Efficient way to disburse parent value across related records?

Discussion created by onefish on Apr 14, 2018
Latest reply on Apr 15, 2018 by philmodjunk

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?