Record count by condition

[shalmaxb]

Hi,
is it possible to count the records meeting a certain condition (checked bool field) in one entity to show in a number field of another entity? I have contacts, that can apply for a course/workshop and in the workshop entity I want to count those records of the applied contacts. In the workshop entity there is shown a list of applied contacts by a realtionship.

So far I am able to count only all contacts, but not specific ones.

[yuri]

Hi,

You can utilize formula function record\count, or entity\countRelated.

https://docs.espocrm.com/administrat...a/#recordcount

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

Set your custom field with a function result.

[shalmaxb]

I solved it, but only after lots of trial and error. In general the formula entity\countRelated(LINK, [FILTER]) is, what it needs, but in the documentation it is not clear. LINK means the name of the relationship, not the entity. And furtheron I had to put in an equation (what I did not read from the documentation, but found by experimenting) to form the condition. In the end my formula looked like this:

COUNT=entity\countRelated('RELATIONSHIP_NAME', 'CONDITIONAL_FIELD' = 'VALUE_OF_CONDITIONAL_FIELD')

This way the counting field in the other entity counts the number of records, that meet the condition. I hope it is understandable.

[yuri]

1. Bellow there is an explanation what does LINK mean. https://docs.espocrm.com/administrat...tion-arguments,

2. There's also an example showing that string values should be quoted: https://docs.espocrm.com/administrat...tycountrelated

3. Your formula is not correct. I recommend to follow the documentation rather than an experiment. An experiment results may be misleading. countRelated function doesn't support key->value filters.

I recommend also using a search to find use cases. Example: https://forum.espocrm.com/forum/gene...ity-sumrelated