Path: csiph.com!usenet.pasdenom.info!aioe.org!.POSTED!not-for-mail From: Helpful Harry Newsgroups: comp.databases.filemaker Subject: Re: Unstored Get(FoundCount) calculation: inconsistencies when used in parent TO calculations Date: Wed, 11 Sep 2013 16:23:29 +1200 Organization: Aioe.org NNTP Server Lines: 104 Message-ID: <110920131623290660%HelpfulHarry@BusyWorking.com> References: <61467fa5-d258-4c94-be7b-39a62f213fe6@googlegroups.com> <34c111fb-3c30-4987-ad64-5fd422a42db2@googlegroups.com> NNTP-Posting-Host: vHXFHdbwBMG+uolI9ktXAQ.user.speranza.aioe.org Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Complaints-To: abuse@aioe.org User-Agent: Thoth/1.5.4 (Carbon/OS X) X-Notice: Filtered by postfilter v. 0.8.2 Xref: csiph.com comp.databases.filemaker:1360 In article <34c111fb-3c30-4987-ad64-5fd422a42db2@googlegroups.com>, John Hanson 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: > > > > > > > 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.