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

Groups > comp.databases.filemaker > #1355 > unrolled thread

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

Back to article view | Back to comp.databases.filemaker

Contents

  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

#1355 — Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations

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?

[toc] | [next] | [standalone]

#1356

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)

[toc] | [prev] | [next] | [standalone]

#1357

In addition to Harry's suggestion, you can also put a summary field in the child table, set to count( IDField).  Pointing to that from the parent table will give you only the number of related records.

Also, your comparison calc will evaluate Sum(childTO::Cal_FoundCount) two times if there are related records.  Some alternatives:

Let( Ct = count(childTO::PrimaryID),
   case( Ct = 0, 0, Ct )
)

...or...

Max( 0, count(childTO::PrimaryID) )




On Monday, September 9, 2013 12:51:02 PM UTC-7, John Hanson wrote:

> 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.
> 
> ParentTO::Cal_childCount = 
> 
> If(Sum(childTO::Cal_FoundCount) = 0; 0; Sum(childTO::Cal_FoundCount))
> 

[toc] | [prev] | [next] | [standalone]

#1358

On Monday, September 9, 2013 5:13:31 PM UTC-7, fmproso...@gmail.com wrote:
> 
> Also, your comparison calc will evaluate Sum(childTO::Cal_FoundCount) two times if there are related records.  Some alternatives:
> 
<snip>
>
> On Monday, September 9, 2013 12:51:02 PM UTC-7, John Hanson wrote:
> 
> > ParentTO::Cal_childCount = 
> > If(Sum(childTO::Cal_FoundCount) = 0; 0; Sum(childTO::Cal_FoundCount))

Good Lord. You and Harry hit that nail on the head. I just looked at that calculation again and now I feel like a complete idiot. There's absolutely no need to ever add up the found count except in the report summary field itself.

I use ChildTO::cal_FoundCount on parent layouts to show the number of child records per each parent record and it works just fine. I never use it in layouts based on ChildTO - it's useless there, for many reasons that Harry described.

Here's the blog post from Digital Fusion explaining the idea behind it:
http://www.teamdf.com/weetbicks/a-lightning-fast-alternative-to-the-count-function/17/

As near as I can tell, when you GTRR in FileMaker, it's the equivalent of performing a find in the childTO, where childTO::parentKey == parentTO::uniqueKey. 

Since Get(FoundCount) evaluated from the childTO will return the number of results for that search, as near as I can tell, it always returns the correct number of related records when displayed from the parent record. This is regardless of any searches you might do on the childTO independently of the relationship. I have not tried using it with filtered portals however.

In this case, I needed to store the result of ChildTO::cal_FoundCount in a calc field on the parent table in order to summarize the related records for an arbitrary report. I think I absent-mindedly copied the logic I was using to sum up some quantity fields and substituted the cal_FoundCount field instead (leading to the doubled number as you both pointed out).

For example, here's how I used the FoundCount in a total qty calculation, which avoids doing the double-sum you pointed out and also avoids running a Sum() if the found set is empty:

ParentTO::cal_TotalQty =
If( childTO::cal_FoundCount = 0; 0; Sum( childTO::n_Qty ) )

Here's what I think my logic should have been for the running record total (and this seems to be producing the desired results now):

ChildTO::cal_FoundCount =
Get(FoundCount)

ParentTO::cal_ChildCount =
ChildTO::cal_FoundCount

ParentTO::sum_Childen = 
= Total of cal_ChildCount (running with restart), when sorted by cal_YYYYMM

[toc] | [prev] | [next] | [standalone]

#1360

In article <34c111fb-3c30-4987-ad64-5fd422a42db2@googlegroups.com>,
John Hanson <crayz9000@gmail.com> wrote:

> On Monday, September 9, 2013 5:13:31 PM UTC-7, fmproso...@gmail.com wrote:
> > 
> > Also, your comparison calc will evaluate Sum(childTO::Cal_FoundCount) two
> > times if there are related records.  Some alternatives:
> > 
> <snip>
> >
> > On Monday, September 9, 2013 12:51:02 PM UTC-7, John Hanson wrote:
> > 
> > > ParentTO::Cal_childCount = 
> > > If(Sum(childTO::Cal_FoundCount) = 0; 0; Sum(childTO::Cal_FoundCount))
> 
> Good Lord. You and Harry hit that nail on the head. I just looked at that
> calculation again and now I feel like a complete idiot. 

Overlooking something simple that later seems obvious happens to the
best of us.  :o)




> There's absolutely no need to ever add up the found count except in the 
> report summary field itself.
> 
> I use ChildTO::cal_FoundCount on parent layouts to show the number of child
> records per each parent record and it works just fine. I never use it in
> layouts based on ChildTO - it's useless there, for many reasons that Harry
> described.
> 
> Here's the blog post from Digital Fusion explaining the idea behind it:
>
> http://www.teamdf.com/weetbicks/a-lightning-fast-alternative-to-the-count-func
> tion/17/
> 
> As near as I can tell, when you GTRR in FileMaker, it's the equivalent of
> performing a find in the childTO, where childTO::parentKey ==
> parentTO::uniqueKey. 
> 
> Since Get(FoundCount) evaluated from the childTO will return the number of
> results for that search, as near as I can tell, it always returns the correct
> number of related records when displayed from the parent record. This is
> regardless of any searches you might do on the childTO independently of the
> relationship. I have not tried using it with filtered portals however.
> 
> In this case, I needed to store the result of ChildTO::cal_FoundCount in a
> calc field on the parent table in order to summarize the related records for
> an arbitrary report. I think I absent-mindedly copied the logic I was using
> to sum up some quantity fields and substituted the cal_FoundCount field
> instead (leading to the doubled number as you both pointed out).
> 
> For example, here's how I used the FoundCount in a total qty calculation,
> which avoids doing the double-sum you pointed out and also avoids running a
> Sum() if the found set is empty:
> 
> ParentTO::cal_TotalQty =
> If( childTO::cal_FoundCount = 0; 0; Sum( childTO::n_Qty ) )
> 
> Here's what I think my logic should have been for the running record total
> (and this seems to be producing the desired results now):
> 
> ChildTO::cal_FoundCount =
> Get(FoundCount)
> 
> ParentTO::cal_ChildCount =
> ChildTO::cal_FoundCount
> 
> ParentTO::sum_Childen = 
> = Total of cal_ChildCount (running with restart), when sorted by cal_YYYYMM

If it does work using Get(FoundCount), then there must have been a
change in the way the function works in newer versions of FileMaker Pro
(possibly when the feature to have multiple tables in one document was
introduced), but it will cause erroneous results in older databases
that have been converted. 

I've just tested it in FileMaker 5.5 using the old
Status(CurrentFoundCount) function, and it doesn't work there. The
FoundCount always simply gives a count of all the records in the child
table's current Found Set, regardless of which Relationship link or
table is used.

You may already have done it, but you should probably test your system
with multiple parent, multiple child records, and when performing
various manual Finds in the child table before relying on it as
actually working. 

As a note, when testing this in FileMaker 5.5 I noticed a couple of
things that may trip you up in testing as well: 

    - performing a Find in the child table did NOT update the
       count fields displayed in the parent table until that 
       table's window was manually refreshed.

   - adding child records via a portal in the parent table
      did NOTupdate the count fields in the parent table if the
      child table was in a Found Set. (i.e. if the child table 
      was displaying records with a Find of data "A1", then 
      adding a child record via the parent table's portal did
      not increase the counts, whether or not it was a new 
      "A1" record). A new Find / Find All needed to be done 
      in the child table and the parent window refreshed.

[toc] | [prev] | [next] | [standalone]

#1363

On Tuesday, September 10, 2013 9:23:29 PM UTC-7, Helpful Harry wrote:
> In article <34c111fb-3c30-4987-ad64-5fd422a42db2@googlegroups.com>,
> 
> 
> If it does work using Get(FoundCount), then there must have been a
> 
> change in the way the function works in newer versions of FileMaker Pro
> 
> (possibly when the feature to have multiple tables in one document was
> 
> introduced), but it will cause erroneous results in older databases
> 
> that have been converted. 
> 
> 
> As a note, when testing this in FileMaker 5.5 I noticed a couple of
> 
> things that may trip you up in testing as well: 
> 
> 

I suppose it would have been worth mentioning in my OP that I'm working with FileMaker 12. Before that I worked with FileMaker 9; even between those two versions, there have been some major improvements (most notably the native UUID support in FM12).

Looking back at the feature history, it's quite astonishing how many "normal" database features (as might be expected from any SQL-compatible solution) were missing from FileMaker 7 and earlier.

[toc] | [prev] | [next] | [standalone]

#1365

On Tuesday, September 10, 2013 9:23:29 PM UTC-7, Helpful Harry wrote:
> If it does work using Get(FoundCount), then there must have been a
> 
> change in the way the function works in newer versions of FileMaker Pro
> 
> (possibly when the feature to have multiple tables in one document was
> 
> introduced)


I don't see how it would possible work using Get(FoundCount) through a relationship.  There was some newer functionality, introduced in 8 or 9 I think, that allows a summary field in a related table show in the parent layout with the correct numbers based on the relationship, which is why I'd suggested a summary field rather than using the get(foundcount) calc.

If you ARE going to use a calc in the parent table, then I wouldn't use the Sum() function when what you really want is a count().  Why? Because Sum() has to actually evaluate each value to toal them up; Count() just has to see how many records have a value (any value) in the particular field.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.filemaker

csiph-web