Count Opportunities with filter X (how to use on Formula Fields)

[rodrigocoelho]

Hi,

I would like to create a Formula Field that counts all opportunities with filter X (already created it).
The only formula I see is count RELATED. Is it possible to know(count) a number of registers even not being related?

I would like to know if there is a entity\sumRelated without being Related to the entity.

Thanks

[yuri]

Hi Rodrigo,

It's possible to achieve w/o development. Create Report Filter.

Use:

entity\sumRelated('opportunities', 'amountConverted', 'reportFilter5c41a0a396f66725d');

where 5c41a0a396f66725d is ID of Report Filter record you can obtain from a url.

[rodrigocoelho]

Hi Rodrigo,

It's possible to achieve w/o development. Create Report Filter.

Use:

entity\sumRelated('opportunities', 'amountConverted', 'reportFilter5c41a0a396f66725d');

where 5c41a0a396f66725d is ID of Report Filter record you can obtain from a url.

Hi Yuri, first I would like to thank you for the great product that is espocrm.

Let me try to explain what I'm trying to do.

I'm trying to create a field that shows the last opportunity of X Type created. This would be shown when creating a new opportunity to help me to decide the opportunity code.
I can't use auto increment number, because it can't difference different opportunity types. If at last I could change its next number value via workflow, I could accomplish what I need. But it is not possible.

So, I created a number field, and I'm trying to use the entity formula (before save) with count related function for showing the total number of opportunities with type x already created.

The problem is that the count related only consider registers related to that being created. So, it would not work.

Any idea?

Thanks again

[yuri]

> The problem is that the count related only consider registers related to that being created. So, it would not work.

Not sure if I get you. entity\countRelated('opportunities', 'reportFilterID') should calculate a count of opportunities that fall into report. Report should filter record with the specific type.

You will need to use multiple ifThenElse to call different report filters depending on a type.

[yuri]

Also I plan to introduce new function in the future.

recordCount('Lead', 'emailAddress', 'test@test.com')

With the ability to specify more than one key-value pairs.

[rodrigocoelho]

> The problem is that the count related only consider registers related to that being created. So, it would not work.

Not sure if I get you. entity\countRelated('opportunities', 'reportFilterID') should calculate a count of opportunities that fall into report. Report should filter record with the specific type.

You will need to use multiple ifThenElse to call different report filters depending on a type.

CountRELATED will count only registers that are RELATED and based on Filter, not??? I wish to calculate ALL registers, based on Filter, not only the ones RELATED.

[yuri]

Makes sense.