Selective sum (SUMIF) & selective update
It would be great to hear from some seasoned FMP users if I found a FMP way to solve this issue or if I found a solution that is far too complex. Here is the requirement:
1) I need to summarize amounts in a budget table, basically to show how much the budget shows a surplus or shortage of money.
2) I want to save that surplus to a special budget line called "savings". This record is indicated with a field "SavingsYN". There is only 1 savings line at a given time - all other records have "N" in budget:: SavingsYN.
First I tried with a calculated field in the table (budget:: SumAmount = sum(Amount)), only to find out that this only summarizes records displayed in the layout. If I do a find, the summary is not for the entire table. But I left this field in the table.
Then I added a global calculated field to the table that contains the value I am looking for (budget::SavingsN = "N"). So this field is a constant for all rows in the table, including the savings budget record.
Then I created a relationship from the table to itself with budget:: SavingsN = budget2:: SavingsYN. So now budget2 contains all records that are not the savings account.
In my layout I still have budget as the base table for my layout. But if I now refer to sum(budget2:: SumAmount), I do get the correct summary - no matter on which record I am or which records are being displayed.
I did the same thing for budget:: SavingsY (global calculated field = "Y"). And I also created a 2nd relationship from budget to itself: budget:: SavingsY = budget3:: SavingsYN. So budget3 now contains the Savings record for every record in budget.
Then on the onModeExit trigger (browse-only) I did a Set Field(budget3:: Amount; sum(budget2:: Amount)).
It works, but it feels kind of tricked.
Are there more elegant FMP ways to achieve this?