I have a very basic business rule objective. I want to total Child records in a portal, and make sure they equal to an amount in the Parent (master) record, before committing the Parent and Child records.
This is the simple Table schema:
ChildAmountSum (Sum ( Child::ChildAmount )), unstored since reference a field in another table)
Relationship (equal, with ability to create records on Child side checked)
Master::MasterId ------< Child::MasterId
I also want the total of the Childs records to be currently shown on the layout as I enter or change the Child records.
I use the aggregate SUM function in the Parent (master) record, which updates beautifully as I enter the Child records.
I have an OnRecordCommit script trigger on the layout that compares ChildAmountSum to ChildAmount. What I am finding is even though the screen shows the amounts being equal, the script fails. Even the Data Viewer is not being properly updated.
And when I click outside the portal and commit, the script trigger test fails.
If I set the GoalAmount to 200, the script trigger test passes, the record is committed, and the data viewer now shows 1000.
I can understand that since the records are not committed, the aggregate SUM function may not return a result that reflects what is being shown on the screen. Also, ChildAmountSum field is not stored (I do not have a choice). I am considering using temporary tables (or even repetition fields since I can store the total) to gather and commit the data for testing before adding them to the transaction table, or maybe just committing the records first, flagging the parent record as untested, testing the totals then removing the flag if totals equal.
I am using FMA v14, and I am still determining if FM is the right product development tool for some of the financial business system we are looking to convert from Microsoft Access and Excel.
Any comments appreciated.