I've been racking my brain trying to figure out how to best go about calculating a total based on a n-series of records called by other records. The other challenge is describing this in such a fashion that it’s understandable.
The situation is this: a parent record (Job) may have x-related child records (Assembly), those child records (assemblies) may have x-related records, and so on. Each Assembly consists of parts, and each part has a quantity for the Assembly to be created.
So, each Job will list a quantity for each Assembly. Each Assembly that calls a sub-Assembly will have a quantity for the sub-assembly being called, and so on. So if the Job(A) requires 4 of an Assembly(1), and that Assembly calls a sub-Assembly(1.1) with a quantity of 2, the simple math is 4 * 2. If that sub-Assembly(1.1) calls another sub-Assembly(1.1.1) with a quantity of 3, then it is 4 * 2 * 3.
Where it gets tricky is that each Assembly has Parts, each with their own quantity, and those quantities can change. Another wrinkle is that Assemblies can be used on other jobs and rarely in the same way, so a sub-Assembly on one Job may be the Assembly on another. If an Assembly Part quantity value is changed, I need to know everywhere that is used on any (active) Job and update the total quantity for ordering purposes. Also, parts can be added, removed, or modified on an Assembly, and those changes need to be tracked back to each Job that uses them so the Quantities can be updated. A change on an Assembly would always be a change for any active Job that it might use it.
I’m hoping that someone out there can point me in the direction of a method or technique that will help me solve this problem.