Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Record by record query?


Recommended Posts



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?



Link to comment
Share on other sites

You need to omit records that do not qualify .


Meaning in a script


Go to record request [first]


If qualification1 and qualification2 and qualification3

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


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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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....:



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?

Link to comment
Share on other sites

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::D ). 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.

Link to comment
Share on other sites

  • 1 month later...

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... confused.gif

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...

Important Information

Terms of Use