Calculation field for different entity inside one form for model driven apps

[NurNina]

Hi, I have two entities, Sales Header and Sales Lines. Inside the form of Sales Header for my model driven apps, the Sales Line is the subgrid view. 

My Sales Line has a lookup field to Sales Header (which is the Sales Id) and also a field for Line Amount. 

I want to create a new field in Sales Header to calculate the total amount from Line Amount in Sales Line. Is there any ways to do that?

[GarethPrisk]

I believe you're describing a rollup field. Here is the MSFT docs on that field type.

On your Sales Header entity

• Add a new field called Sales Order Total
  • Data type: Currency
  • Select/add rollup definition
• In the editor for the rollup field
  • Select the Sales Order Line relationship
  • Select the Line Amount field
  • (Optionally: Determine if any child records should not be included in the rollup)
• Add the new field to Sales Order form

NOTE:

• Rollup fields will calculate automatically at set intervals
• They can be manually updated as well, but clicking into the field on the form and selecting refresh
• They can technically be triggered to update, say when a child record is updated, but this requires custom coding/development
• Their main advantage is that they are system-determined (users cannot overwrite), but the data does become stale unless refreshed - it may not be the best choice for rapidly changing child records, but it will be correct when refreshed

[v-xida-msft · Accepted solution]

Hi @NurNina ，

Does the "Sales Header" Entity have a One-to-Many relationship to the "Sales Lines" Entity?

Based on the needs that you mentioned, I think the "Roll Up" functionality for a Whole Number field in your 'Sales Header' Entity could achieve your needs.

The Roll Up field functionality is used to calculate an aggregate value from the records related to a record, or a value computed over a hierarchy.

Please check the following article for more details:

https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/types-of-field...

You could consider add a new "Whole Number" type field in your 'Sales Header' Entity, then configure the "Roll Up" functionality for this field as below:

click the "Rollup" option under the "Add" option, then click "Save" button within the opening dialog. After that, the Roll up configuration panel would be opened as below:

then edit your Model-Driven App form, then enable the 'Total Amount' field into the corresponding Entity form, then run our Model-Driven app, check if the issue is solved.

Best regards,

[NurNina]

@GarethPrisk does it has to be Currency ? Can I choose some other data type like decimal maybe?

[NurNina]

@v-xida-msft im not sure whats the issue here but my Total Amount is 0.00 when clearly there are values in my Line Amount. 

[GarethPrisk]

Please post a screenshot of your roll-up field settings and I'll let you know.

[GarethPrisk]

What data type is line amount? Typically the child entity field and parent entity roll-up field will be identical, but some types can be mixed. The roll-up is typically a decimal, whole number, or currency since it usually has a count, sum, max, etc.

[NurNina]

@GarethPrisk  does it takes time for the calculation to run?

[GarethPrisk]

The actual running of the roll-up itself, for a single record, is very quick. There is a recurring system job which will bulk roll-up all records.

You can also manually recalculate at any time. https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields#online-rec...

The recurring job can also be changed.
https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields#modify-rol...

[v-xida-msft]

Hi @NurNina ,

In default, the Roll Up field within CDS Entity would not be refreshed immediately when a new record from the Related Entity has been bind to the Primary Entity.

As an fixed solution, you could consider click "Calculate" icon within your Model-Driven app form, then the "Recalculate" button would show up as below:

then the Roll Up filed value would be re-calculated.

In addition, you could also consider go to the "System Job" setting in your current Environment instance, then find the specific Roll up field calculation System Job, and modify its recurrence:

Currently, within System Job in CRM, you could only configure schedule recurrence in Hourly or in Daily. You could configure the System job execute in 1 hourly, and specify the start time.

Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields#modify-rol...

If you want the Roll up field to be re-calculated immediately when a new record from the Related Entity has been bind to the Primary Entity, I afraid that there is no direct way to achieve your needs. As an alternative solution, I think the CDS SDK could achieve your needs. Please check and see if the following blog would help in your scenario:

https://www.plusconsulting.com/forced-calculation-of-a-rollup-field-using-custom-workflow-activities...

Best regards,