Calculate SUM value in Opportunities

[khairfactory]

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?

[Vadym]

Hi khairfactory,

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.

1. Make a Currency type field via Entity Manager (e.g., "sum"), add it to desired layouts.
2. Next, create a simple List type Report for the Contact entity with the Name column.
3. Create a Workflow rule with the Contact target entity and Scheduled trigger.
4. Select report created before, specify scheduling frequency (e.g., */5 * * * * (every 5 minutes)).
5. Add Execute Formula Script action with the following formula:

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

[Vadym]

khairfactory,

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

The first way:

1. Make a simple List type Report for the Task entity with the Name column.
2. Create a Workflow rule with the Task target entity and Scheduled trigger.
3. Select report created before, specify scheduling frequency (e.g., 30 1 * * * (at 01:30 once a day)).
4. Add Execute Formula Script action with the following formula:

$createdAt = datetime\format(createdAt, 'Europe/Amsterdam' ,'YYYY-MM-DD');

ifThen (
datetime\today() == datetime\addDays($createdAt, 7) && status == 'Completed',
deleted = true
);

---

The second way:

1. Make a List type Report for the Task entity with the Name column and the following Filters:

• Field -> Status -> Any Of -> Completed
• Field -> Created At -> Older Than X Days -> 6

2. Create a Workflow rule with the Task target entity and Scheduled trigger.
3. Select report created before, specify scheduling frequency (e.g., 30 1 * * * (at 01:30 once a day)).
4. Add Execute Formula Script action with the simple formula:

deleted = true;

More information about Workflows, Reports, and Formula:
https://docs.espocrm.com/administrat...ows/#workflows
https://docs.espocrm.com/user-guide/reports/#reports
https://docs.espocrm.com/administrat...culated-fields

[Mark G.]

Is there no way of doing basic "Master-Detail" summation without the reports extension?
Given "sumRelated" and other formulas available in Espo, it's unfortunate and quite lacking that additional pieces are required to make something this basic, actually work.

[Vadym]

Hi Mark G.

In Entity Manager, it's possible to define script (formula) for a specific entity type. This script will be executed every time before the record is saved.
To edit formula for a specific entity type, follow Administration > Entity Manager > dropdown menu on the right on the row of the entity type > 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.

https://docs.espocrm.com/administration/formula/#formula-calculated-fields

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

[stayfi]

Vadym ,

Any workarond if i don't have workflow, to, calculate" sum"
I tried the formula without help

[Vadym]

stayfi

The method described above is for users who do not have an Advanced Pack extension. (post #5)
Just create a Currency type field (e.g., "sum") via Entity Manager, add it to the Detail view and paste the Formula (Administration -> Entity Manager -> Contact -> Formula):

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

[Mark G.]

Thanks, Vadym!

It is unfortunate that there is not some kind of "update" command in the Espo scripting language, that would update the calculation and re-display it in real time ... aside from when saving the record.

e.g.

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

[telecastg]

It is unfortunate that there is not some kind of "update" command in the Espo scripting language, that would update the calculation and re-display it in real time ... aside from when saving the record.

This updated commercial extension will allow you to add a "Recalculate Formula" button to any entity in Detail view without having to write any code in Espo 7. https://payhip.com/b/28Dhw

[stayfi]

Vadym -
Mark G.

Hey Vadym tried this: sum = entity\sumRelated('opportunities', 'amount', 'won'); without success,

i want to achieve this, please test it on your end, and tell me if the Sum works,

you have a one Contact Has Many Cleaning relationship set, every time a contact visit a new cleaning entity should be created with the two fields (amount and payedAmount). i think best way is to add 3 fields in contact like below:

- totalCleaningAmount
- totalPaidAmount
- totalBalance

When a new cleaning entity is created for the given contact, you can add formula on contact to calculated total amounts of related cleaning, see formula below:

totalCleaningAmount = entity\sumRelated('cleanings', 'amount');
totalPaidAmount = entity\sumRelated('cleanings', 'paidAmount');
totalBalance = totalPaidAmount - totalCleaningAmount;

[Vadym]

Hi stayfi

Tell me, please, how did you use this Formula? Provide a screenshot.
Did you create a Currency type "sum" field and add it to the layout?

[stayfi]

Vadym

this is the formula: totalCleaningAmount = entity\sumRelated('cleanings', 'amount');

also totalCleaningAmount is set to read only

[Mark G.]

I'm having the same issue ... I can make calculated fields work within a single/child entity record ... e.g. item quantity x item price = line item total ... but not summations (integers or currency) updating to the parent entity record.

[stayfi]

Vadym Maybe vadym will try it on espo 7 and report

[Vadym]

Hi!

entity\sumRelated(LINK, FIELD, [FILTER]) function is supported from 5.3.0 version to the last EspoCRM release.

For it to work, there must be a One-to-Many or Many-to-Many relationship between entities.
I hope the screenshots bring a little clarity.

[stayfi]

Vadym

vadym. i want the results to be shown in the opportunities, in my case in contacts, can you try with this example?

You have a one Contact Has Many Cleanings relationship set, every time a contact visit, a new cleaning entity should be created with field (amount)

I want - total amount to be shown in the contact page