Trying to create a table column for summing values in Dynamics CRM

(0) Share

Report

Posted on by DevOs

118

Hello,

I am trying to create a table column for Work Orders that sums a field called "install times" found in dynamics CRM:

Each item in the CRM has an install time, and each work order has a list of all items used for it. I simply wish to have a few field that sums the *total* value of all the "install time" in the work order.

So I have gone to dataverse>Work Order>Form>New table column>Data Type>Formula. But no matter what I do, I cannot click the "save" button because when I mouse over the many formula(s) I've tried, they all give errors.

SUM(
 FILTER(
 [WO Product],
 _workorderproductid_value = [_workorderid]
 ),
 installtimetobase
)

Sum(relatedentities('msdyn_workorderproduct', 'msdyn_installationtime'), msdyn_workorderid = ThisItem.msdyn_workorderid)

aggregate(
 lookup(
 "workorderproduct",
 "workorder",
 "_workorderid_value",
 [_workorderid],
 "installtimetobase"
 ),
 sum
)

_suminstalltimes_value@OData.Community.Display.V1.FormattedValue: (
 select sum(installtimetobase/60) 
 from workorderproduct 
 where _workorderid_value eq [_workorderid]
)

----------------------------------------------------------

So I have abandoned that and am now trying to just create a business rule to do it, but I'm a bit confused as to how to accomplish that?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (0)

DevOs 118 on at

Like (0)

Report

Is this correct?

Was this reply helpful? Yes No
Anton_Dali_UKR 75 on at

Like (0)

Report

You need to crete rollup field.
In case you want to aggregate calculated value in "parent" record which has 1:N relationship this is exactly what you need. Rollup field settings provide an ability to look into the each related record and summarize - I believe it is yor case - all values from the defined field.
The only thing you must be aware of is that this type of field executes operation automatically by schedule. How to impact on this timing is another topic.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Hi @DVOP,

You won't be able to acheive this with business rules and up-to-now nor with Power FX columns.

You can try rollup columns as @Anton_Dali_UKR suggested but it only gets updated every 12h which might be problematic since it can show stale values to users. There are severals ways to achieve this like with plugins, JavaScript but the easiest way IMO is with Power Automate using the Dataverse connector. Have a cloud flow that triggers when a new item is created/deleted or when the install times is update on the item. Then you can calculate the total install time and update the parent (ie work order).
Hope this helps!

Was this reply helpful? Yes No
DevOs 118 on at

Like (0)

Report
Hello.

Thank you for replying, I did not notice this until now as this has been stressing me so much. I'm not sure if the timing matters as I just need the time values once per work order and maybe like once a week.

But if I were to go the power automate way, how would I do that exactly? When a new item is added to work order entity then...do what? How do I say I want the "install time" field from all products on a work order to be added?

I'm trying to follow these instructions but they don't match up with the current version of power automate it seems:

Go to Power Automate and create a new flow. Choose the "Common Data Service - When a record is created, updated or deleted" trigger and select the "Work Order" entity. Set the trigger to "When a record is created, updated or deleted". Click on "New Step" and select the "Common Data Service - List records" action. Set the "Entity name" to "Product". In the "Filter Query" field, enter the following query: _productid_value eq '{workorderid}'. Click on "New Step" and select the "Compose" action. In the "Inputs" field of the "Compose" action, enter the following expression to calculate the total install time for all products associated with the Work Order: sum(body('List_records')?['value']?['installtimes']). Click on "New Step" and select the "Common Data Service - Update a record" action. Set the "Entity name" to "Work Order". Set the "Record Identifier" to the Work Order ID by clicking on the "Show advanced options" button and selecting "Work Order ID" from the dropdown. In the "Fields" section, set the "Total Install Time" field to the output of the "Compose" action.

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Hi @DVOP,

Yes the Dataverse flow connection and actions have been updated in the last years. Here's an overview of the flow logic:

Flow trigger is When a row is added, modified or deleted , filter on the Work Order table. Change Type of Added, Modified or Deleted since those will impact the install times. Set the Scope to Organization

Get the Work Order details with Get Row By ID action to get the product ID (parent). This is what you want to do the sum by. Pass the ID from the previous step (ie the work order that triggered this flow)

Get all Work Orders related to that product ID with the List Rows action. The filtering syntax might be tricky but ensure you filter by the product ID from step #2.

Initialize a new variable, loop through the rows from step #3 and add the times to the variable

Finally, you need to save the results with the Update a row action

All the links provided have examples of how to use Power Automate actions. If it's the first time, it may take a bit of time to get familiar and take a few trials to get things right. Once you're familiar with flows though, you will like it 🙂
Hope this helps!

Was this reply helpful? Yes No
DevOs 118 on at

Like (0)

Report

Hi Eric,
Thank you for trying to help. I am a bit lost already though as to the whole row ID business, because it'll never be static, it'll be dynamic for each work order generated. Microsoft's instructions that you linked do not explain how to make that dynamic?

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Yes you can dynamically get that from the previous step. If you click in the "Row ID" box an itellisense will popup and and pick the unique indentifier from the "When a row is ...." step. Hope this helps!

Was this reply helpful? Yes No
DevOs 118 on at

Like (0)

Report

I don't have that

Was this reply helpful? Yes No
EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Yes you almost there! Just pick the column that says "Unique identifier for entity instances"

Was this reply helpful? Yes No
DevOs 118 on at

Like (0)

Report

Is this correct?
And for step #4, when you say "Initialize a new variable, loop through the rows from step #3 and add the times to the variable". What times are you referring too?

Edit: Shouldn't step 2 table name be "get work order products"? Not "Work orders" again?

Was this reply helpful? Yes No