Filtering Detail Records for Summary Calcs

[SharonGordon]

Hi all! I could use some advice on performing a find...

Namely, I have two related tables: a detail table that contains detail records, and a summary table that contains a calculated sum() based on an amount in the detail table.

(For other design considerations, I need to have the calculated sum() in a separate table.)

I would like to filter the detail records based on one of the attributes in the detail table so that the sum() in the summary table reflects a total amount only on the found set of detail records.

So, I created a script - here's a rough description of it:

Go to the summary layout

Find all records

Enter find mode

Set field detail.amount to the filter value

(The detail table is related to the summary table)

Perform find

After running this, the summary result reflects the total amount of all of the detail records, not the found set I was aiming for.

Any ideas would be really appreciated!

Thanks,

Sharon

[comment]

That's how it works - relationships ignore the found set. It must be so, because there can be many found sets: each user and each window can have a different found set of the same table, and a relationship wouldn't "know" which one is preferable.

It's hard to tell with so few details, but your best option is probably to filter the relationship (or a second relationship) by the same criteria used for the find.

[SharonGordon]

Hmm, could you say a little more about how to filter a relationship?

I know I was skimpy on details...I can post more if it looks necessary.

Thanks!

Sharon

[SharonGordon]

Is this what you were thinking?

Add a global field to my summary table and join it to the field in my detail table that I want to filter on.

Then when I enter the filter value into the global field in the summary table, that should filter the detail records?

Thanks,

Sharon

[comment]

There are several ways to filter a relationship. The attached example uses a global field in the parent table to set a threshold for the child's value. Examine the relationship's definition. You can add more criteria there to further narrow the filter.

You can also use a calculation field instead of a global - this is preferable when there's no user input of the filter criteria.

Another way is to set up a calculation field in the Child table, along the lines of:

Case ( ; ParentID )

Then you base the second relationship on this field.

FilterChildren.fp7.zip

[SharonGordon]

Many thanks! I really appreciate the help!

Sharon