Sum fields related on date, formula

[Athensmusic]

Hi there,
we have an entity named "A", type Event which we use the default event's field "dateStart" and another field named "b" type Currency.
We have another entity named "C" which is in relationship with "A" added Link type children to parent.
In entity "C" we have a field named "year" type Varhar and a field named "d" type Currency.
We do "add c" and give the name of each month.
The sum of all events "b" Currency fields, related on dateStart (month and year) to be displayed in the corresponding field "d".
What we need seems so but we tried a couple of hours to figure out the correct formula but without any success. Tried different relationship types without success too.
Any help?
Thanks!

[Maximus]

Hi,
In your case you have to use this formula:

entity\sumRelated(LINK, FIELD, [FILTER]);

But for achieving your goal you need to create an appropriate filter for your "a" entity. This is manual of how to create a new filter https://github.com/espocrm/documenta...select-manager.

For example what I did for Opportunity:

protected function filterClosedDate(&$result)
    {
        $result['whereClause'][] = array(
            'closedDate=' => '11/20/2019'
        );
    } 

After I was able to write the formula this way:

entity\sumRelated('opportunities', 'amount', '20/11/2019');

[Athensmusic]

Thanks for replaying,
we cannot get the sum of all "b" fields, even not trying to parse date & year.
"e" is the name of the Link on the child field and "f" of the parent.
what we're tryied:
On entity "C", formula

d=entity\sumRelated('A', 'b');
or
d=entity\sumRelated('e', 'b');
or
d=entity\sumRelated('f', 'b');
nothing of those give us the result.
What we misunderstand?

[Maximus]

Not sure that this supported for parent type relationship. You can use One-to-Many relationship instead.
BTW, it is much easier to make Filter by using the Report feature:
1. https://github.com/espocrm/documenta...report-filters
2. https://github.com/espocrm/documenta...mula.md#filter.

[Athensmusic]

Thanks
Unfortunately, no way to make it work under any relationship, tried all.
How can sum all 'x' fields in the same entity into another field named 'z'?
which is this simple formula please?
z=?

[Maximus]

> Unfortunately, no way to make it work under any relationship, tried all.
I will give you a tip to understand what a proper 'LINK' you need to use in the formula. For example, I used the next formula to sum all amounts to an Account entity

entity\sumRelated('opportunities', 'amount', '20/11/2019');

The name of 'LINK' that I used is 'opportunities'. Please, open Administration -> Entity Manager -> Account -> Relationship. The name of a relationship is 'LINK' (see screenshot).