Change in parent table to calculate in child

OK, I have a quote builder for my small construction company that I am working on. I have a project table that I can put markup, per hour rate and tax percentage in. Then I go to estimate items and add each item with things like item, quantity and hours needed for each item. If I set the per hour rate to say $200 as in the example all my calculations work, but when I decide to change my hourly rate in the projects view it will not update to my total cost of previously added items in estimates form. It only applies to new ones.
I am thinking I need some way to trigger a recalculation on columns for previous items but I can not seem to find out how.

Thanks,
Matt


Which table here is the parent and which is the child? Where is the calculation running that depends on the hourly rate that is input in the “Project Form” shown, and what is that calculation expression?

1 Like

I am fairly new at this, only about 6 months.

I may be calling them the wrong thing but I assume the Project is the parent and the Estimate is the child since the total cost in the Project table is calculated by all the items in the Estimate table for that Project, with that being said the calculation for the Project Form total is being made in the Estimate Form Sub Total column then all Sub Totals for that project are added together to the Project Form Total which is a virtual column. (not shown in picture it is down the page a little).

Here is expression for Estimate Sub Total: ([Material Cost](1+[Project].[Tax])+([Labor Hours]([Project].[Hourly Rate]))+([SubCon Cost]))*(1+[Project].[OH&P]).

Thanks,
Matt

Yes, it sounds like you are correct about Project=parent, Estimate=child.

Note the difference here between a Real column, and a Virtual column; Real will only re-calculate when/if anything else in the record is edited, Virtual will re-calculate upon every sync. Not that I recommend the use of a Virtual column here, as that will hurt your sync times, I just wanted to point out the difference.

So, yes, you do need to somehow trigger the re-calculation in all related records when the parent’s [Hourly Rate] is modified. One way to do that would be to run an Action as the Form Save Behavior for the “Project Form” View. This action would be of type “data: execute an action on a set of records”, which would point to another Action on the Estimate table, and would run it across all related records.

2 Likes

Matt, I’m trying to do something similar to what you’re trying to do.

If I’m right, you will need to create an action that changes the value accounting the child form values.

I have managed to change values to a stable value, such as “+1” or any other value that you’d like to. Not yet to a variable, such as you’re trying to do.

If I manage to successfully do it, I’ll post it in order to try to help you.

Regards;

Fábio