Calculated Values, Client Scripts, Business Rules, and Lookup tables

[Kerry10]

I apologize in advance for how broad and complicated this may sound...

I have a table structure that I'm creating . There is a parent, child, and "grandchild" table. Each child references the parent table, and is included as a related list. There are also multiple calculations that have to be made based on values from all levels of the table. Let me give an example:

Parent table:

     field M = sum of all N's (calculated value using GR query)

     field V = calculated via lookup table based on M and sum of all Ns 

Child table:

     field: X - manually entered 

     field N = sum of all Z's (calculated value using GR query)

Grandchild table:

      Field Y - manually entered

     Field: Z - Calculated via lookup table based on parent.X and Y (parent.x I pass down with a client script)

Issues I have run into with this setup:

1 - V is not looking up the value at first. After a save, it looks it up but usually incorrectly. I believe this is because the calculated value script runs after or at the same time as the lookup.

2 - If I update field X, field Z does not update unless I open the grandchild records. This is because X is passed down to the child table using a client script.

I have tried using business rules instead, but I can never get them to update records on other tables. If needed I can provide my attempted business rule code. Any suggestions or best practices would be much appreciated!

[Dominik Simunek · Accepted solution]

In such case I would normally start with business rules as they are server side and I should have full control over the order of execution and also strong support for debugging. I believe it is also beneficial to have all the calculation logic in one place like business rules instead having something being calculated field and something populated via client script.

The tricky part might be to make sure that everything is calculated in the right order as update on Child table triggers update on Grandchild table but this again triggers update on Child table. So it might be easy to get into an endless loop so you must build your conditions really properly so that it does not trigger update in case it is not needed in fact (the value is not changing anymore).

[Kerry10]

Hi Dominik,

Youre right! Using business rules was the best way to do this. I ended up getting rid of all calculated values. I kept the client scripts on the grandchild table so that they update in real time, but then used business rules for everything else.

The trick for business rules vs data lookups is that if you make the Order of the BR >1000, it will run after the data lookup.

If anyone sees this and would like me to go into more detail about how and when I ran the BRs, let me know!