0

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.

5 replies

null
    • Fred
    • 1 yr ago
    • Reported - view

    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)
      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred Hello, the first method works perfectly. Thank you so much.

    • Créateur de bien-être
    • Sebastien_Guillet
    • 1 yr ago
    • Reported - view

    Fred

    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')
    • Fred
    • 1 yr ago
    • Reported - view
    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')
      • Créateur de bien-être
      • Sebastien_Guillet
      • 1 yr ago
      • Reported - view

      Fred Perfect ! It works. THANKS

Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 5Replies
  • 110Views
  • 2 Following