Calculate SUM value in Opportunities

[leonidasfs]

Hello there,

New to EspoCRM and i have a quick question:

I want to add a new field inside the Contacts page where it will calculate the SUM of all the Won Opportunities a client have. Any idea how to proceed?

PS: Also any idea how to delete completed tasks automatically after X days?

[Maximus]

Hello.

> I want to add a new field inside the Contacts page where it will calculate the SUM of all the Won Opportunities a client has. Any idea how to proceed?

1. Open Administration -> Entity Manager -> Contact -> Fields -> add a new field of the 'currency' type to store the sum value (lets call it 'totalWonAmount')
2. Open Administration -> Entity Manager -> Contact -> Formula -> use this function ->

totalWonAmount = entity\sumRelated('opportunities', 'amount', 'won');

Read more about it here https://docs.espocrm.com/administrat...titysumrelated.

Note, this formula triggers to recalculate the totalWonAmount each time you make some changes in the target Contact or fire a 'Recalculate Formula' (The admin can run Recalculate Formula action for specific records from the list view: select records (or all search results) > click Actions dropdown > click Recalculate Formula).

In order to make some more dynamically logic (e.g. if an Opportunity record status changed to 'Closed Won', then it automatically triggers recalculation for the totalWonAmount of the related Contact) you need to:
1. Write a custom Hook (programming skills required) https://docs.espocrm.com/development/hooks/
2. Use Workflow from the Advanced Pack https://docs.espocrm.com/administration/workflows/

> Also any idea how to delete completed tasks automatically after X days?

You need to create a Scheduled Workflow that is based on the report. Here is useful Information:
- https://docs.espocrm.com/administrat...ows/#scheduled
- https://docs.espocrm.com/administrat...-target-record
- https://docs.espocrm.com/user-guide/reports/

[Mark G.]

I'm looking into the same calculation need ... being able to total a field from multiple sub-entity records, then update that value into the parent entity.

I see the required formula to do this is (example): totalWonAmount = entity\sumRelated('opportunities', 'amount', 'won');

However, it is my understanding that with Espo version 7 onward, you now have to use "Select Builder" to create any desired query function ... in your case, to ONLY include the Status="Won" records in the total.

Has anyone been able to get this "Select Builder" function to work?
If so, do you have an actual example (i.e. the code from a WORKING Espo instance) to share?

Thanks.

[rabii]

Hi Mark G.

totalWonAmount = entity\sumRelated('opportunities', 'amount', 'won');

won is a predefined primary filter for opportunity in espocrm see

https://github.com/espocrm/espocrm/blob/master/application/Espo/Modules/Crm/Classes/Select/Opportunity/PrimaryFilters/Won.php

you can create your own filters for any given entity and use the filter in formula.

[stayfi]

Hi, Maximus
i can't run the formula, is the condition a must in it?

[Mark G.]

This is why some WORKING/LIVE examples, showing actual formula syntax used along with actual pre-defined filters (in Select Builder), would be very helpful. Something that can simply be copy-and-pasted, then adapted.

Espo is feature-rich with all the different functions available, but it seems that getting them to work can sometimes be hit-or-miss. Many of us are not programmers, but can understand some code enough to be able to adapt it for our own needs.

stayfi ... to answer your question, according to the Espo documentation, the condition is optional...

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

[stayfi]

Mark G. Have you found a solution?

[Mark G.]

Apparently, starting with Espo version 7, "Select Builder" should be used to create pre-defined filters ...

https://docs.espocrm.com/development/select-builder/

"Select Manager" is being deprecated ...

https://docs.espocrm.com/development/select-manager/

[Mark G.]

Mark G. Have you found a solution?

No. I'm new to Espo, so still finding my way around ... and experimenting!
However, once I find a solution I will publish on this board.
When users have a few real-life working examples to copy-and-paste and adapt, the mystery will be solved.
We don't all need to be spending time trying to re-invent the wheel ... or just getting it to turn.
Collective effort and sharing!

[stayfi]

Waiting for it, thanks