Seems like a job for FileMaker's summary or calculating fields in your 12-month table. These would sum or calculate the related transactions.
Or, if you are concerned about too much overhead due to these types of fields, you could employ script triggers. Upon committing the record, you could trigger a script to set the related field according to the values in the changed entry.
In script triggers, is it possible to get the old value and new value while editing an existing transaction?, so that I can subtract the old value from actuals and add the new value. Possible to give a simple example.?
1 of 1 people found this helpful
Yes you can get both the original and the changed values using script triggers. You could capture the original value and set a variable in a script by using a script trigger like "On Object Enter". Then, for the same field use a second script trigger "On Object Modify" to capture the changed value and set a variable. Your script could then calculate the change to the "actual" field.
However, it seems like a lot of work when you could just use a calculation in the Budget table that could be refreshed easily. I realize that you are the only person knowing the real objective, environment, schema, and constraints to make that judgement.
There are some good videos and resources regarding script triggers on FileMaker's and other sites (even YouTube) that could explain it better. I've used script triggers for some lightweight audit trails and they work as advertised.
thanks for the info. I am used to SQL server procedures and triggers. FM is very different but very simple in handling updates, calculation etc. Once again thank you very much. I will try this and let you know if I need any help.