FMP 10 Adv: I have 20'000 records with some 15'000 parents - what's the
best way to count the number of parents of my present found set in the
child table?

The count function returns 1 for every single record, the sum will be
the number of child records, i.e. the number of records in my found set.

--
http://clk.ch

On 2011-07-03 01:32:10 -0700, clk (AT) tele2 (DOT) ch (Christoph Kaufmann) said:

I'd use Go To Related Records, select the Match Found Set option, then
use Get(FoundCount) to set the value into a variable, after the GTRR.
Then return to the original child record set.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

In article <1k3trsb.1t7bmm5s0xis2N%clk (AT) tele2 (DOT) ch>, clk (AT) tele2 (DOT) ch (Christoph
Kaufmann) wrote:

Create a Relationship in the child table to group the related records.
e.g.
rel_SameParent
match record in ChildTable with records in ChildTable
when ParentKey = ChildTable::ParentKey

The you can create a Calculation Field which works out each record being
1/xth of the total number of same parent records.
e.g.
OneXth Calculation, Number Result, Unstored
= 1 / Count(rel_SameParent::ParentKey)

If you were to then create a Summary Field and Summary Report totalling
these values, you would get a total of 1 for each different ParentKey
sub-summary. Which also means to get the number of unique parent records
you can simply summarise them all
e.g.
s_NumParentRecords Summary
Total of OneXth

by putting the field in a Leading or Trailing Grand Summary Part of the
Layout (you can also retrieve the value for use in other calculations if
needed).



Another way would be to create some fields in the Parent Table.

Create a Calculation Field to "flag" if there are any related Child Table
records.
e.g.
HasChildRecords Calculation, Number Result, Unstored
= If(Count(rel-ChildTable::KeyField) > 0; 1; 0)

Then you can have either:

- a Summary Field in the Parent Table to total these values

or - create a Relationship from the Child Table back to ALL
the Parent Table records and use a Calculation Field in
the Child Table to Sum via this Relationship.


Helpful Harry )

On 3/07/11 6:02 PM, Christoph Kaufmann wrote:
Create a rel from child to parent using Childarent_id to Parent:arent_id

create a value list based on the rel, then something like this: Count
the value list items;


set $_child_parents =


ValueCount ( ValueListItems ( Get ( FileName ) ; "child_parents" ))

or more elegantly:

Let([
F = Get ( FileName ) ;
VL = "child_parents" ;
VLI = ValueListItems ( F ; VL )
];
ValueCount ( VLI )
)

Lynn Allen <lynn (AT) NOT-semiotics (DOT) com> wrote:

Thanks - I thought as much. I take it there's no way to calculate the
number then.
--
http://clk.ch

Your Name <yourname (AT) yourisp (DOT) com> wrote:

Create a Relationship in the child table to group the related records.

Sounds great.

The result wouldn't depend on the found set in the child table, I stick
to the first version.

--
http://clk.ch

On 7/3/2011 9:04 PM, Christoph Kaufmann wrote:

I would probably do it the same a Lynn.

But another way would be to use the List() function to get all
ParentID's from the child table. Then use a valuecount() of
UniqueValues() to determine the number of unique parent IDs.

You'll find the UniqueValues() function at
http://www.briandunning.com/cf/596

So your calc would end up looking something like this:
ValueCount( UniqueValues( List( Children::ParentID )))

The only problem with this method is that it might be slow across 20,000
records, whereas it might be just fine across 1000 records.

Howard Schlossberg <howard (AT) nospam (DOT) fmprosolutions.com> wrote:

Great. It's like Helpful Harry's approach, enriched with the list
function that doesn't exist in v5.5 :-)
--
http://clk.ch

On 2011-07-03 21:04:55 -0700, clk (AT) tele2 (DOT) ch (Christoph Kaufmann) said:

Yes, Howard has given you a calculated example. Finding in FM is very
fast, calculating totals (or counting) across relationships can become
very slow. If your set of parents is always going to be relatively
small, use the calculated method.

I recommended the GTRR/Get(foundcount) method because for large record
sets, it's likely to perform faster.
--
Lynn Allen
--
www.semiotics.com
Member FBA
FM 10 Certified Developer

Helpful Harry wrote:

I'm afraid that this method, too, works only if I have every record of
the table in the found set, because the totalling depends on the found
set, the count of the child records with the same parent, however, does
not.
--
http://clk.ch