Total of foundset removing values under $500 on list view

[PatrickDes]

Hi everyone, it has been some time since I have touched my database. I am facing a new problem: I have a list of prices with a foundset total. I would like to get a new total of that foundset omitting  amounts equal and less than $500. Each amount of the list is the result of a calculation.  Do I need to create a new relationship for that or a portal for both foundsets.

Thank you for your time and guidance.

Patrick D.

[keywords]

If you total is provided by a summary field then it should simply be a case of constraining your found set. A summary field totals the current found set.

[PatrickDes]

Hi Keywords, Thank you for answering. I understand your idea but... A script runs a selection between two dates and the result must report all the payments made. So far so good, I have a sum of the foundset. Now I need, on the same page, with the same dates, to sum all records with a value of ≥ $500. The reason is, these payments come from a separate bank account.

My guess is to use two portals with two calculations. One for the total sum and the other with the calculated result. How do I write this calculation?

[comment]

Basically, you have two options:

1. Find all records between the two dates. Open a new window and constrain the found set to records whose value is ≥ 500. Set a global field (or variable) with the total of this subset. Close the window to return to the original found set. Show the overall total using a summary field, and the subtotal using the global field/variable.

2. Define a calculation field like:

If ( Value ≥ 500 ; Value )

and use a second summary field as the total of this field.

 

3 hours ago, PatrickDes said:

The reason is, these payments come from a separate bank account.

You would think there would be another way to distinguish between them. And I would also think you would want to show the total of each account separately - i.e. sort the records by the account (or, if there is no other way, by a calculation field that returns true when the value is ≥ 500) and show the sub-totals by placing the summary field in a sub-summary part.

 

 

[PatrickDes]

Ooh, very good idea. Thank you. I will try the second option, much simpler.

PS: This is how easily I used to resolve it in Excel: A column with a looping calculation to pull the ≥ 500. Very static but working.

[comment]

54 minutes ago, PatrickDes said:

This is how easily I used to resolve it in Excel

In Excel, it could be much simpler using the SUMIF() function. Unfortunately, Filemaker does not have an equivalent function, so you would need to perform the IF and the SUM separately - as suggested in my 2nd option. Or write a custom function to loop over the found records and return the sum of only those that meet the criteria.

However, since you are already running a script anyway, the smart thing would be to let the script do all the required work, instead of adding an extra calculation field and a summary field to the schema. Which is why my 1st option is listed first.

 

[PatrickDes]

Thank you Comment, truly appreciated.

At the end, I chose the second solution. The reason is, the new calculated field and summary are needed in a list view as well. My client loves visual references and feel safer having all the columns in plain sight.

The file is very fast and adding the 2 new fields will not affect speed.

Patrick D.