sumRelated reportFilter Problem

[serqet]

hello guys,

I add formula from entities but it doesn't give report total? can you help me?

Formula ; test1 = entity\sumRelated('Volvox', 'reportFilter5fa541ade1a44984b');

First I created the report.
Then I created a report filter and selected the report I created.
And I used the ID of this filter.

[serqet]

Log:
Espo.ERROR: Formula failed: [] []

[serqet]

Maximus help pls

[Maximus]

Hi serqet,
I believe you used the wrong link in your formula. The link name should started from the lower case letter like 'volvox'. See manual here please https://docs.espocrm.com/administrat...titysumrelated.
Also, make sure that 'volvox' is the relationship name.

[emillod]

Maximus i'm wondering if "reportFilter5fa541ade1a44984b" is correct?

[Maximus]

Well, it looks normal except the link name. For sure, if my suggestion will not help, then checking the filter will be the next step.

[serqet]

Hi Maximus ,

first of all thank you for the answer.
Volvox is my entity name. I guess I shouldn't use the entity name.
Actually, I want to draw the sum I marked in the report in the picture exactly with the help of a formula and record it in a field. I could not succeed.

[Maximus]

I've noticed that your report's target Entity is Opportunity, so I assume that you have a relationship between the Volvox and Opportunity entity of One-to-Many type. In this case, the proper formula should be something like:

test1 = entity\sumRelated('opportunities', 'reportFilter5fa541ade1a44984b');

Just to make sure that the Opportunity entity link called 'opportunity', open Administration -> Entity Manager -> Volvox (Relationship) -> Relation between the Volvox and Opportunity -> 'Name' parameter of the Opportunity entity.

Please take a look one more time at this https://docs.espocrm.com/administrat...titysumrelated.

[serqet]

Maximus
I took the screenshot from the demo page to be an easy example
I want to do this for the entity I created ...

by the way, there is no relationship in my entity.

[serqet]

Maximus this is my view... help pls.

[Maximus]

Hi,
sorry for the delayed reply.

> by the way, there is no relationship in my entity.

You need to create a Relationship between the Volvox and Opportunity entity of One-to-Many type. After this, you need to add Opportunities relation subpanel to the Volvox entity and link a Volvox entity record with one or many Opportunity record/s.
W/o this relationship your formula won't work.

[serqet]

Hi Maximus

Unfortunately, there is no relationship that can be established between opportunities and my own entity.
Actually, I need the answer to this: How can I get the total number of lines I have circled in the image into a field?
I can pull this with sql query but I don't know how to use it in espo, maybe I can do it using hooks but I will need an example file for it.

Thank you.

[Maximus]

Hi,
Now I see You don't need to Sum the total amount but you want to Count the number of records Well, to count the number of the related records you need to use this formula instead: entity\countRelated(LINK, [FILTER]) https://docs.espocrm.com/administrat...tycountrelated.

But you said that you don't have the relationship and you are interested in counting the total number of the records. For this purpose, there is no such formula function. You need to create a hook https://docs.espocrm.com/development/hooks/.

[serqet]

Hi Maximus ,

Glad you understand my problem
Yes I can use Hooks but I need help. Can you give me an example showing how to run the following query?

SELECT COUNT (*)
FROM `table_name`
WHERE name = "xxx" and test = "yyy" and MONTH (zzz) = MONTH (CURRENT_DATE ())
AND YEAR (aaa) = YEAR (CURRENT_DATE ())

[Maximus]

I didn't test it but probably you will be able to achieve your purpose with this https://docs.espocrm.com/administrat...a/#recordcount.

[serqet]

I tried this but failed to retrieve the date field in the asset as the current month...