How to use the GREATEST function in a complex expression with a date field/

[raoulteeuwen]

Hello. New EspoCRM-user here. I do have some SQL knowledge.

A little background: we store donations in our system. People can donate 'oneoff', or frequently (monthly, annually)

I was asked to make a report where i list people that, in the previous month, have stopped their frequent donation.

So i am planning to establish the last donation of a person that is listed as a 'frequent' donor, and test whether that last donation is more than x days in the past.

I am looking at 'complex expressions' ( https://docs.espocrm.com/user-guide/...x-expressions/ ), and found a GREATEST function. So i thought i could do:

GREATEST(donatiedatum)<'2024-01-01'

(the field tested translates to 'donation date')
(on https://docs.espocrm.com/administrat...mula/datetime/ i found the date format should be like '2024-01-01')

But whether i use the above, or the inverse (GREATEST(donatiedatum)>'2024-01-01'): i do not get any results.

I was unable to find any/relevant examples of the use of this function. Anybody any tips on how to use this function / what i'm doing wrong?

[item]

Hi,
i think https://docs.espocrm.com/user-guide/reports/#columns
MAX(donatiedatum) will do what you search.

And you can "Filter" after some "date" field

[rabii]

As item Mentioned you don't need a function, you can just add the donation date field in the filter section and there is a function in it old than x Days. see attached screenshot

[raoulteeuwen]

Thank you both, will try as soon as i find time, and report back if not successful. Thanks again!

[raoulteeuwen]

Took some time due to different things happening, but: i haven't been able to do what i want to do. The data model is that we have persons in one table, and donations in another. The donations table contains what someone financially contributes, frequency (whether it is one off or a recurring contribution), a date of the contribution and the (reference to the) person that contributed.

I want to establish the 1st (and in another query: the last) donation a person did, per type (so 1st one-off, 1st monthly).

As 'Entity type' for the report i use the Donations table. Using MAX (as i understand it) returns the latest contribution in the table. But i need it per user and per frequency. So i can see what was the last recurring donation per person, and if they have seemed to stop doing such a recurring contribution for instance.

So i need some way of doing a group by or something.

Someone proposed to create a workflow (via functionality of the Advanced Pack) that automatically (not sure what the trigger should be) calculates the 1st donation of a person, and store/save it on the person record. Is that in Espo the best way to do what i need?

[rabii]

> As 'Entity type' for the report i use the Donations table. Using MAX (as i understand it) returns the latest contribution in the table. But i need it per user and per frequency. So i can see what was the last recurring donation per person, and if they have seemed to stop doing such a recurring contribution for instance.

Using Max it means the maximum but not necessarily the last contribution. Based on what you mentioned here you can easily create a report entity Type (Donation) set order by contribution date (For 1st you need to set the order ascending) and group by the contributor and just add frequency as a column and even the contribution date. This should be an easy report to build if you have all relationship set properly.

For last donation you can use the same report but just change the order by (donation) to descending.

[raoulteeuwen]

Thank you: will (again) look / read /delve into how to group (have a hard time finding when that is in the UI or in what field to force that or whether is only available under certain conditions or entity types or...) etc re 'you can easily create a report entity Type (Donation) set order by contribution date (For 1st you need to set the order ascending) and group by the contributor and just add frequency as a column and even the contribution date'