Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #1356

Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations

Show all headers | View raw

In article <c43b5785-4f03-4837-b2e2-1fa665cbdeb6@googlegroups.com>,
John Hanson <crayz9000@gmail.com> wrote:

> Figured this might be as good a place as any to post this question.
> 
> I've been using the unstored Get(FoundCount) trick to get record totals
> rather than relying on the Count() function, for obvious performance reasons.
> This has worked very well on everything I've done so far.
> 
> I needed to include this total in a report layout, so I created a "container"
> unstored calculation in the parent TO...
> 
> ParentTO::Cal_childCount = 
> If(Sum(childTO::Cal_FoundCount) = 0; 0; Sum(childTO::Cal_FoundCount))
> 
> where
> childTO::Cal_FoundCount =
> Get(FoundCount)
> 
> ... and things suddenly went pear-shaped.
> 
> If I place the childTO::Cal_FoundCount field directly on my report, which is
> based on ParentTO, it displays the expected number of child records for each
> parent record. If there are 7 children for that parent, it displays 7.
> 
> If I place my container calculation ParentTO::Cal_childCount on the report,
> as far as I can tell, the result is squared -- for the previous example, it
> displays 49.
> 
> I'm completely baffled at this point. Has anyone else encountered similar troubles with related record calculations?

It's not an inconsistency. It's the correct waythe functions work - the
child Cal_FoundCount value comes from just one record, while the parent
Cal_childCount value is a total of all the child records..

  - The Get(FoundCount) function returns the number of records
     in the current Found Set, which in your example is 7 records.  

  - In the child table the Cal_FoundCount calcuation field works
     out this number for EACH record, i.e. each of the seven child
     table records has Cal_FoundCount = 7. Putting this child table 
     field on a parent table report displays only the FIRST related
     record's data (i.e. 7).
 
  - In the parent table the Cal_childCount calculation field sums
     up the values from ALL the related child table records 
     (i.e. 7+7+7+7+7+7+7 = 49).


You can't actually use the Get(FoundCount) to obtain the number of
related records anyway. 

The Get(FoundCount) is the number of records in the table's current
Found Set, which is not the same as the number of related records for a
Relationship. The current Found Set in the Child table can also change,
either manually or by scripts, and so can give different incorrect
results.

For example, if you have a Child table with records:
 
     Orange
     Blue
     Orange
     Orange
     Blue
     Orange

if in the child table you manually or by a script find all the records
with "Orange" in the  Colour field you might get 4 records, so
Get(FoundCount) will be 4. If the parent table is looking for related
records with "Blue" in the Colour field, then a field based on
Get(FoundCount) will still return 4 (or when using the Sum function,
16) rather than 2.

If you perform a Show All records in the child table, then the parent
table Get(FoundCount) will return 6 (or when using the Sum function,
36) rather than 2.



The best way to count related records is to use the Count function with
a related field that ALWAYS has data - usually the child-side Key field
is the best one to use.
i.e. 
         ParentTO::Cal_childCount = 
                = Count(childTO::ChildKeyField)

As far as I'm aware there is no performance issue. 

In fact, unless you've got a massive number of records, an extremely
complicated calculations, and / or an ancient slow computer (or network
connection for hosted databases), then there is unlikely to be any real
performance issues with any FileMaker functions these days.

Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations John Hanson <crayz9000@gmail.com> - 2013-09-09 12:51 -0700
  Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2013-09-10 09:26 +1200
  Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations fmprosolutions@gmail.com - 2013-09-09 17:13 -0700
    Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations John Hanson <crayz9000@gmail.com> - 2013-09-10 08:11 -0700
      Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations Helpful Harry <HelpfulHarry@BusyWorking.com> - 2013-09-11 16:23 +1200
        Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations John Hanson <crayz9000@gmail.com> - 2013-09-11 08:47 -0700
        Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations fmprosolutions@gmail.com - 2013-09-11 10:01 -0700

csiph-web