How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

(0) Share

Report

Posted on by EMorn

This column calls Total standard price (new_Totalstandard price) I would like to sum all this field to field on opportunity form call Total Distributor Price (New_Totaldistributor Price)

Any Help will be really appreciated.

Thanks

All responses (9)

Answers (1)

Suggested answer

EMorn 15 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

[quote user="Lu Hao"]
Hi EMorn,

In the List records, the Filter Query should be _opportunityid_value eq 'Opportunity(Value)'

Note the closing quotation mark.

Please see this image.

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

In addition, when I tested this Flow again, I found that when the opportunity product line was deleted, it failed to run.

Because Flow is triggered after the record is deleted, the value of the opportunity lookup field of the opportunity product row is also deleted, so List records fails and it cannot find other product rows of the opportunity.

We need to change the trigger at the top to when the record is created and updated.

The following is a screenshot of the completed Flow. Please compare carefully.

Image 1:

Image 2:

Image 3:

So, even with Flow, it can't perfectly fulfill your needs. When the opportunity product line is created or updated, it works, when the opportunity product line is deleted, it does not work.

In summary, using the rollup field is still a more appropriate solution.
[/quote]
LuHao Thank you for the answer, This really helps me
Verified answer

LuHao 40,878 on at

Like (1)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi EMorn,

In the List records, the Filter Query should be _opportunityid_value eq 'Opportunity(Value)'

Note the closing quotation mark.

Please see this image.

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

In addition, when I tested this Flow again, I found that when the opportunity product line was deleted, it failed to run.

Because Flow is triggered after the record is deleted, the value of the opportunity lookup field of the opportunity product row is also deleted, so List records fails and it cannot find other product rows of the opportunity.

We need to change the trigger at the top to when the record is created and updated.

The following is a screenshot of the completed Flow. Please compare carefully.

Image 1:

Image 2:

Image 3:

So, even with Flow, it can't perfectly fulfill your needs. When the opportunity product line is created or updated, it works, when the opportunity product line is deleted, it does not work.

In summary, using the rollup field is still a more appropriate solution.
EMorn 15 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi LuHao

Here it is

Many thanks for your help
LuHao 40,878 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi EMorn,

Could you provide a screenshot of the setup for this step?
EMorn 15 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi LuHao

I follow the step and come up with an error like below

Any Idea ?

Many Thanks
LuHao 40,878 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi EMorn,

Please don't forget to click Yes under "Did this answer your question?" to close this thread.

Thanks.
Suggested answer

LuHao 40,878 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi EMorn,

We have to use Power Automate.

Please refer to this blog for the idea of ??creating flow: https://technologyblog.rsmus.com/microsoft-dynamics/dynamics-365-customer-engagement/dynamics-365-ce-multiple-entity-rollup-field-with-power-automate/

I created flow and tested it, it can update "Total Distributor Price" whenever "Total standard price" changes.

Step 1. Go to Solution and open a custom solution, new a flow.

Step 2. Search for Common Data Service, click When a record is created, updated or deleted.

For the remaining steps, please refer to the screenshots of this flow.
EMorn 15 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi Lu Hao,

This is not work for me. As I mentioned I runout of roll ups fields and need to calculate instance without waiting for 12 hours

Many thanks

Emorn
Suggested answer

LuHao 40,878 on at

Like (0)

Report

RE: How can I sum total price from subgrid opportunity product on opportunity form because I have runout of roll up fields ?

Hi EMorn,

We will use Rollup field.

When create the Opportunity field "Total Distributor Price" (New_Totaldistributor Price), select its Field Type to Rollup. (If you have already created this field and did not select Rollup, then yoy have to delete this field and recreate it.)

Click Edit button next to Rollup, then in the Rollup field editor, under RELATED ENTITY, click "+ Add related entity" and select Opportunity Lines (Opportunity).

Then click "+ Add aggregation", select SUM and (Opportunity Line) Total standard price.

Save and close. Add the filed to the Opportunity form.

On the Opportunity form, on the left side of the field "Total Distributor Price", there is a calculator icon, click it, and then click the Recalculate button, you can see the latest summation result.

A Rollup field starts working 12 hours after being created by default, and its summary calculation is performed every 1 hour.

So if you do not manually click the Recalculate button, the rollup field will refresh the latest result every 1 hour.

For detailed information about the Rollup field, please refer to this document: https://docs.microsoft.com/en-us/dynamics365/customerengagement/on-premises/customize/define-rollup-fields