Workflow to calculate amount from multiple entries

[camille]

Hello

I am trying to create a workflow that will sum the total amount of multiple entries for each account - see the attached image. Any advice as to how to go about this?

Thank you in advance!

Camille

[eymen-elkum]

Hello Camille,

Check this: https://docs.espocrm.com/administrat...titysumrelated

[Maximus]

Hello,
There are a couple of ways:
1. Use a Report and then create a Report Panel for Account https://docs.espocrm.com/user-guide/...#report-panels (good solution if you need just to monitor the sum amount).
2. Use the entity\sumRelated() formula https://docs.espocrm.com/administrat...titysumrelated - in order to store this value in a field (good solution if you have plans to use this value in the future calculation). The best option here is to combine this formula with Workflow to make this value dynamically changeable. For example, let's take a look at the relation between Account and Opportunities and imagine that you want to calculate the sum amount value of all related opportunities. To make dynamic logic you will need create a Workflow for Opportunity with the following logic:
1. Target entity - Opportunity
2. Trigger type - after record updated (saved)
3. Condition - if the 'amount' field was changed
4. Action -> Update related record (Account) -> Use your formula to update. E.g:

totalOpportunityAmount = entity\sumRelated('opportunities', 'amount');

[camille]

Hi Maximus

Thank you for your response, option 2 is most helpful for me as I'd like to work with the calculated value.

I've created a workflow and it is running, but it's not calculating the correct value. With the example I've attached, the 'liARR' value should be calculated as $920, but it's being displayed as 0. Do you have any further tips?

[Maximus]

Due to the workflow's condition (IF Amount is changed), the workflow won't be triggered upon other changes. So in order to trigger the workflow, you need to affect the amount value.

I've noticed that in your formula you specified the filter 'Won'. In this case, it has the sense to improve your workflow by changing the condition scope in the target Workflow. E.g:
1. Remove your 'IF Amount is Changed condition' from the 'ALL' scope and place it to the 'ANY' scope.
2. Add one more condition to the 'ANY' scope -> 'IF status is equal to Won'

So in this case your workflow will be triggered not only when you will change the Amount value, but also when you will change the status to 'Won'.

[camille]

Maximus thank you, that helps a lot.

The issue I'm coming across now is if I move one of the Status' back to something other than Won (ie. prospecting), the Workflow isn't recalculating the value liARR.

In my case, the Stage has to be Won and the Amount cannot be empty (so I've placed these conditions in the 'ALL' scope instead of 'ANY'). Do you have any more tips to ensure the value is recalculated each time there is a change in either the amount or stage?

[Maximus]

> The issue I'm coming across now is if I move one of the Status' back to something other than Won (ie. prospecting), the Workflow isn't recalculating the value liARR.

For this purpose, you need to change this condition: 'IF status is equal to Won' to this: 'IF status is changed' so the workflow will be triggered upon any Status field's change and recalculate the desired field.

> Do you have any more tips to ensure the value is recalculated each time there is a change in either the amount or stage?

You have to know that ALL condition scope means that all the conditions should be matched to fire the workflow. ANY means that anyone of the matched condition is enough to start Workflow. So which one to chose always depends on a logic you want to build.

So, in this case, I believe it makes sense to:
1. Leave in the ALL scope the condition: 'IF the amount is not empty'.
2. Remove from the ALL scope the condition: 'IF status is equal to Won'
3. Leave in the ANY scope those 2 conditions: 'IF the amount is changed' and 'IF the status is changed'

[camille]

Thank you Maximus

One last question, I only want the amount to be calculated for Opportunities with the Stage = Won. The above solution is working for me besides this last point.

I've tried adding it to the formula (liARR = entity\sumRelated('opportunities', 'amount', 'won') but when I add in the 'won', it calculates the amount as 0 each time.

[Maximus]

Please tell what EspoCRM and Advanced Pack version do you use?
Provide please a screenshot of your Workflow's settings. I will take a look into.

[camille]

Maximus We just updated both our EspoCRM and Advanced Pack to the latest versions.

See the screenshots attached, the workflow is now running but is adding all opportunities instead of just opportunities with a stage = won. So it's resulting in liARR = 1420 when it should be = 920.

Thank you.

[Maximus]

Hello Camile.
Sorry for the delayed reply. I've tried to reproduce it on EspoCRM v.6.0.6. with Advanced Pack v.2.6.1. It works as it should w/o any issues.

[kumaresan]

Can I Sum the records, for a period let's say for the last 6 months only?

Let say,
totalRevenue = entity\sumRelated('opportunities', 'amount', 'won'); only from last 6 months

[Maximus]

Hi kumaresan,
Unfortunately there is no such Formula to get it done. I believe the best option is to solve this with Hook https://docs.espocrm.com/development/hooks/.