Getting the Total price of a Work Order based on the Work Order Details Table

[CLiendo]

I’m try to obtain the Total price of a Work order in the Work Orders Table based on the price of each Row in the Work Orders Details Table for the matching work order.

I have virtual column type = List in the Work Orders Table with the following formula = REF_ROWS(“WO Items”, “Work Order ID”) and I have a Total Price column in the Work Orders Table with the following formula = SUM (SELECT (WO Items[Total], [Work Order ID] = [_THISROW].[Work Order ID]))

I have two work orders with each with unique key in the table WO1 and WO2, and the issue I have is that the SUM is returning the total price for both work orders details instead of one total price for work order W01 and another for work order W02.

Here are the data tables

Here are the column definitions

[Steve]

Total Price is a normal column. Normal column App formula expressions are only recalculated when the row is edited in a form or modified by an action. Try opening each row in a form and saving it without making any changes, see if that prompts the correct values to appear.

[CLiendo]

I opened and saved each detail item for work order W01 and the opened and saved each detail item for work order W02.

I when back and opened work order W01 in edit node and the total that was calculated included the items associated with work order W01 and W02, instead of only calculating the WO1 work order details. I then tried opening in edit mode work order W02 and the total that was calculated included all WO1 and WO2 work order details.

I found the Order Capture sample app and tried it and it works fine. I checked the formula in the Order Capture app and I using the same formula, except for column names and still does not work.

[CLiendo · Accepted solution]

Steve,

I just figured it out the formula should be SUM (SELECT (WO Items[Total], [Work Order ID] = [WO ID])), it works fine now.

Thank you for your help. Happy Labor Day.

[Steve]

Well done!

[LLD]

Hi Carlos,

This solution helped me as I had a similar requirement for my app. Thanks!

However the total value in the “parent table” does not automatically update when we add rows to the “child” table… I have to manually “update”/ “edit” the parent record, so that it picks up any newly added child records.

Did you face this issue. If so, do you have a solution?