Record by record query?

[Fileshaker]

Hello,

 

I am trying to query through a table record by record to add up a certain field of the records that qualify under the if statement:

 

If ( qualification1 and qualification2 and qualification3; Sum ( add up certain field of all the records that made it through the if statement ) ; 0 )

 

However, the if statement goes through the first record only and, if it qualified, adds up all of the table's entries, regardless of whether they qualified individually.

 

Is there a foreach statement or something similar I can use?

 

Thanks.

[Robert Schaub]

You need to omit records that do not qualify .

 

Meaning in a script

 

Go to record request [first]

Loop

If qualification1 and qualification2 and qualification3

Go to record request [next][exit after last]

Else

Omit Record

Go to record request [next][exit after last]

End Loop

 

 

A calculation field =

Sum(Certain Field) unstored

 

With the found set of qualifing the calculation will add up to what you want.

[comment]

How about just performing a find?

 

Make 'qualification1 and qualification2 and qualification3' your find criteria. Add a summary field that sums 'certain field'. Summary fields operate on the found set. Once you have found your qualifying records, the summary field will show you the sum of 'certain field' in all qualifying records.

[Fileshaker]

Thanks for both of your replies. Neither a script, nor a find would work, because I am trying to display all records with their respective summary fields at the same time (using a portal right now).

 

More specifically, I am trying to keep a running sum of a field, should the underlying record have a qualifying content in three other fields. Should one of these three fields be different....:

 

A B C D

1 1 2 10

1 1 3 13

1 2 2 5

2 1 3 9

2 2 1 14

2 1 3 10

 

Trying to summarize data in column D for each unique data in column A, if data in column B = 1, and for each unique data in column C:

 

A C Summary

1 2 10

1 3 13

2 3 19

 

I have the sorting mechanism for columns A & C, but Summary is non-exclusive, so I get:

 

A C Summary

1 2 61

1 3 61

2 3 61 (Sum of all of column D, since the 1 record meets the criteria. E.g. it simply sums everything if the first record qualifies)

 

Is there anything more specific I can do?

[comment]

I cannot follow your explanation in detail, but it seems like you want a report with subsummaries. At least that's how I understand 'to summarize data in column D for each unique data in column A'. I don't understand the added condition 'if data in column B = 1'. If records where data in column B 1 are to be excluded from the summary, then exclude them from the found set.

 

Now, to summarize data in column D for each unique data in column A, you need a summary field, sTotalOfD, defined as 'Total of D'. This can be a running total, if you so define it.

 

Next you need to add a sub-summary part to the layout, defined as 'when sorted by A'. Also add a trailing grand-summary. Put sTotalOfD in both parts.

 

Sort your records by A and enter Preview mode. You should have a sub-total of D for each group of identical A's, and a grand total of D at the end.

 

 

This technique is good for reports. It is fast, and easy to setup, but if you need "live data", then this won't work for you.

 

The other way is summarizing thru relationships, for example: a self-join 'thisTable::A=thisTable2::A' and a calc field = Sum ( thisTable2: ). Unlike summary fields, a relationship ignores the found set, so in your case you'd want another field, either global or calc = 1 and add a second criteria to the relationship, namely thisTable::constant1=thisTable2::B.

[The Digital Man]

I think what you are asking for is going to require doing it through relationships.

[Fileshaker]

Thanks for your responses. I think that I will go the self-join / relational route to maintain live data.

[Fileshaker]

How can I change Chopper's script suggestion to complete the following task:

 

Only 1 criterion: "Month Name" in table A must match "Month Name" in table B, where table B is a list of each "Month Name"

 

But, and this is my problem, I'd like to loop through table A, omitting records, leaving me with the total of something happening in a certain month, saving that total then to the related "Month Name" in table B, then doing the same trick for the next "Month Name" in table B (summing up a field of that Month Name in table A and saving it to table B).

 

It really is a for each loop, which I am desperately missing in Filemaker...