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

Groups > comp.databases.filemaker > #1360

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

Show all headers | View raw

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.

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