Sum of different fields

[Marinos Kangas]

Hello,

I am new to FileMaker and still trying to find my way around the scripting language.  Even though the scripting language is quite rich, I don't feel as comfortable and flexible as I do in lets say VB.

To my query now;  I have designed a solution for my company which processes invoices.  These invoice forms input numerical amounts tied on budget codes for each department and in turn are stored in a different table.  As you can see on the image attached, I cannot find a calculation function that will sum all the amounts for each BudgetCode into AmountAllocated.  AmountAssigned is the yearly department budget predefined.

I will be grateful for any suggestions or ideas.

Thanks

Edited December 7, 2016 by Marinos Kangas

[Wim Decorte]

Care all of these 'budget' Table Occurances based on the same base table?

Same question for the 'budget code' TOs.

 

[Marinos Kangas]

Yes Wim, those are all connected to the main Invoice page and the only common link is the Invoice number.  As for the Budget Code tables, at first I thought of not linking them and calculating them via script but I cannot figure out a command that will give me the flexibility of an SQL query.

[Wim Decorte]

I don't think I understand your answer in relation to my question...  If 'budget' and 'budget codes' are just one table each then it should be fairly easy to set the 'amount allocated' to the right value by using any of your existing relationships.  sum(personnelBudget::amount) for instance will do if you execute the Sum() from the context of the 'PersonnelBudgetCode' TO.  Given that all of the relationships between budget code and budget are duplicates you can pick any one of them as long as start from the TO used in the relationship.

Remember that you can do a SQL query in FM with the ExecuteSQL() function if you find that easier.

[Agnes Riley]

I don't think you need a new table occurrence for a different budget type (budget code) you just need a type/code field.

Also you should use an ID (primary and foreign keys) to connect table occurrences.

It always helps if people explain in layman's terms what they are trying to achieve so we can recommend a good approach.


Sent from my iPhone using Tapatalk

[Marinos Kangas]

Well...silly me, I confused myself for the wrong reasons.  Wim, the Sum calculation worked like a charm in the AmountAllocated field due to the relationship I have created.

The ExecuteSQL looks like a godsend command I will investigate.  I am sure it will come handy when the going gets tough

Thank you both for your insight.  Sometimes the solution is right in front of your nose, you just can't see it.