How to count Opportunities of a certain Sale Type per Contact

[Dmitry]

Hello! I need a condition in my Process Flowchart, based on the number of Opportunities with a certain Sale Type a Contact has.
(In particular, "if this Contact has more than one related Opportunity with SaleType "Evaluation" - do not send him onboarding emails").

I thought I could achieve this by creating an "Evaluations Count" field (integer), and incrementing it, when a new Opportunity with "Evaluation" SaleType is created. Yet I couldn't find a way to increment a field - only to set it to some static value.

So, questions:
1) Can I make a workflow that increments a field under certain conditions? If yes, how?
OR
2) If there a better way to achieve my goal (count related Opportunities with a certain Sale Type)?

[Maximus]

Hi,
you can try to create Workflow for Opportunity:
Trigger type -> after record updated
Condition -> if salesType is changed and salesType == "Evaluation"
Action -> Update Related Record -> Contacts

You can use a formula to recount:

evaluationsCount = entity\countRelated('opportunities', FILTER)

for using the entity\countRelated function you need to set the filter (system filter or created by a report). There are a few ways on how to add this filter if it doesn't exist:
1. Via code https://docs.espocrm.com/development...select-manager
2. By utilizing Report https://docs.espocrm.com/administration/formula/#filter

[Dmitry]

Maximus , thank you for explanation! Do I get i right, that the report to be used as filter should be a simple list of all opportunities that have Sale Type - "Evaluation"?

[Maximus]

Yep. After you created such a report you need to open Administration -> Report Filter and create a filter.

[Dmitry]

Hi Maximus , I tried to follow the instruction, but for some reason it didn't work.

This is what I did:
1) Created an INTEGER field in the Contact entity, called 'evaluationsCount'
2) Created a list report, that shows all Opportunities with "Sale Type" EQUALS "Evaluation"
3) Create a report filter based on that report
4) Created a Workflow, as you suggested, with two differences:
- I used "after record created", because this is when I need this to happen;
- We have "Opportunities" renamed to "Deals" in our system, so I was not sure, what should the correct naming be, and made three formulas (see screenshot).

evaluationsCount = entity\countRelated('opportunities', 'reportFilter5f57c38f2fac88ea9');
evaluationsCount = entity\countRelated('Deals', 'reportFilter5f57c38f2fac88ea9');
evaluationsCount = entity\countRelated('Deal', 'reportFilter5f57c38f2fac88ea9');

The Workflow is active (new lines appear in the workflow log after each new evaluation), but the "Evaluations Count" field in the Contact is always "None".

What have I done wrong?

[Maximus]

Remove the rest 2 lines and leave only this one

evaluationsCount = entity\countRelated('opportunities', 'reportFilter5f57c38f2fac88ea9');

You said that you rename Opportunity to Deal. This way you only changed the Opportunity label, but on the source code it has key 'opportunity' and the link name has stayed 'opportunities' as well.

When you set workflow for this action:

evaluationsCount = entity\countRelated('opportunities', 'reportFilter5f57c38f2fac88ea9');
evaluationsCount = entity\countRelated('Deals', 'reportFilter5f57c38f2fac88ea9');
evaluationsCount = entity\countRelated('Deal', 'reportFilter5f57c38f2fac88ea9');

In the 1st line the formula returned to you a proper result, but right after, the logic flow executed the 2nd and 3rd line of the formula that thrown the error and returned to you a 'None' result. Because there is no such links in the system as 'Deal' or 'Deals'. Please fix it and test it again.

[Dmitry]

Maximus It worked, thank you very much!