Hi, I am hoping one of you people much wiser than I has devised an efficient way to disburse a value in a parent record across related children records as a percentage of the related total. For example:
ParentRecordValue (PRV) = 1000
ChildRecord1 Value (CR1_V) = 100
ChildRecord2 Value (CR2_V) = 100
ChildRecord3 Value (CR3_V) = 200
What I need in each child record is basically: Result = PRV * V / Sum ( V )
CR1_Result = 250
CR2_Result = 250
CR3_Result = 500
I would prefer for the data to be updated when I add new or update child records. I can achieve the correct results simply by using Sum ( V ), no problem. Accept it brings Filemaker to its knees with only a few hundred records. Meaning it takes ages to commit a record.
I can create a self join relationship in the child table and place the Sum field in each child record but I don't see how that would be any more efficient because Filemaker still has to perform the calculations for all related records every time Sum ( V ) changes (i.e. adding a new record).
Does anybody have clever method to achieve this?