Calculate the most recent date of an activity
Hello, I would like to know if it is possible to have a formula field that allows me to display the date of the most recent activity of a contact (the date of the most recent appointment and not the date of creation of the activity).
Currently I use the following formula: last(Activities.'Rendez-vous')
This formula works well if the activity that I create is the most recent one. But let's say that I have an activity already created on 02/02/2023 and that I want to create an activity that I had forgotten to add and which took place on 01/15/2023 (so before that of February ). With the formula last() the software will note in the field the date of 15/01/2023 while there will be a more recent activity (even if it was created before).
I don't know how to make the software only consider the date of the most recent activity and not the date an activity was created.
Thanks for your help.
May I recommend the order by function. Which would sort your records by a field before picking the last one.
So if ‘Rendez-vous’ is the date field:
let x := last(Activities order by 'Rendez-vous') x.'Rendez-vous'
or you can use the sort() and last() functions:
let x := sort(Activities.'Rendez-vous') last(x)
Is it possible to add a condition to the formula?
The formula below works fine if I don't have a condition. However, I realize that I need to retrieve the most recent date from the "Activités" table provided that the select field named "Type de rendez-vous" is different from 5. I have done quite a bit of attempts that did not work.
Thanks for your help.
date(let x := last('Activités' order by 'Date du rendez-vous'); x.'Date du rendez-vous')
Sébastien Guillet said:
However, I realize that I need to retrieve the most recent date from the "Activités" table provided that the select field named "Type de rendez-vous" is different from 5.
Since 'Activities' is a reference field, you need to use the square bracket [ ] method of filtering. It would look something like:
date(let x := last('Activités'['Type de rendez-vous' != 5] order by 'Date du rendez-vous'); x.'Date du rendez-vous')