Complex report filters using formula

[Dmitry]

Hello. I need to create a list report, that shows all Accounts that have related Opportunities of a certain type (Sale Type == "Evaluation").

I tried to use Complex Expression in the Filters, but I don't see opportunities.saleType in the list of possible parameters, and I can't write the formula freely
(e.g. I can't write string/contains/(opportunity.saleType, "Evaluation") - I only have to select from the lists.

What am I doing wrong?

[yuri]

Hi,

Don't use complex expression here. Add 'Field' filter Opportinities.Sales Type.

[Dmitry]

Hi yuri, thank you for the fast reply! I actually need to list Accounts that have more than 3 Opportunities of that type, how can I do that?

[yuri]

Using having https://github.com/espocrm/documenta...d#having-group

[Dmitry]

I've read the docs before asking. The "having" option allows counting by opportunity.id, not opportunity.saleType. This is what I am asking in the first place.

[Dmitry]

Finally, I've achieved what I wanted by combining Having (total count of opportunities) and Field (NOT "saleType==1" OR "saleType==2).

By the way, for some reason, I had to use NOT with "Sale Type Equal". Using "Sale Type Not equal" didn't work.

Thank you for support!

[yuri]

You can just combine the filter from my anwer #2 and having count(opportunity.id) > 2.