Skip to main content

Formula that checks the most recent of several dates?

  • April 28, 2020
  • 5 replies
  • 87 views
S

Hi!

I’m new to Airtable and conditional formulas in general. I have a an airtable where I want to return a “Not Recently Scheduled” if a customer did not schedule an appointment with my company within the past 2 months, and “Recently scheduled” if they did. Previously scheduled appointments are recorded in another airtable, and I have added a lookup to this new table that pulls the dates of these previous appointments (if any) in a column called “Previous sessions”.

Does anyone know how to accomplish this? I have checked out a couple of instructions but I struggle to make it work. I haven’t found a guide where the formula only checks the most recent of several recorded dates, and I also haven’t figured out if I can check the last 2 months from the date in which a person checks the airtable, without having this date stated anywhere.

Let me know if it’s unclear what I try to accomplish and I’ll try to describe it in another way. Thank you!

    5 replies

    JonathanBowen
    Forum|alt.badge.img+18

    Hi @Sofia_Linse - when you say “in another airtable” I assume you mean in another table, rather than another base?

    Instead of having a lookup field, which will show all linked appointment dates you can use a rollup field to show the most recent. I might have these tables:

    As you can see, my “people” table includes a rollup for the date fields from the appointments table:

    I’ve then got a formula field which shows whether the most recent appointment date is within 60 days or more than 60 days ago:

    IF(DATETIME_DIFF(TODAY(), {Last Appt Date}, 'days') < 60, 'Recently scheduled', 'Not recently scheduled')

    JB

    S
    • Sofia_Linse
    • Author
    • New Participant
    • April 28, 2020

    Hi @Sofia_Linse - when you say “in another airtable” I assume you mean in another table, rather than another base?

    Instead of having a lookup field, which will show all linked appointment dates you can use a rollup field to show the most recent. I might have these tables:

    As you can see, my “people” table includes a rollup for the date fields from the appointments table:

    I’ve then got a formula field which shows whether the most recent appointment date is within 60 days or more than 60 days ago:

    IF(DATETIME_DIFF(TODAY(), {Last Appt Date}, 'days') < 60, 'Recently scheduled', 'Not recently scheduled')

    JB


    Hi @JonathanBowen, thank you so much for helping me out! Yes - I’m referring to another table - still learning the vocabulary.

    The formula for returning Recently scheduled/Not recently scheduled works great on records with one appointment, but I don’t get the Rollup to work. A difference I noticed from your screen shot is that my “Feedback session” field does not have that “calendar” icon next to it, see below.

    The “Feedback session” field in the “Customer” table includes all dates a customer scheduled times with us, as seen here…

    … and is linked to the table where we add the records as primary fields. Here, the the dates are indeed configured as dates.

    Do you have any idea of why it’s not working? Please let me know if I can clarify anything!

    JonathanBowen
    Forum|alt.badge.img+18

    Hi @JonathanBowen, thank you so much for helping me out! Yes - I’m referring to another table - still learning the vocabulary.

    The formula for returning Recently scheduled/Not recently scheduled works great on records with one appointment, but I don’t get the Rollup to work. A difference I noticed from your screen shot is that my “Feedback session” field does not have that “calendar” icon next to it, see below.

    The “Feedback session” field in the “Customer” table includes all dates a customer scheduled times with us, as seen here…

    … and is linked to the table where we add the records as primary fields. Here, the the dates are indeed configured as dates.

    Do you have any idea of why it’s not working? Please let me know if I can clarify anything!


    Ahh…OK, this is because your date field isn’t a date field, but a linked record (and presumably a date in the linked table).

    You might have to do a bit of table restructuring to get this to work. Are you able to share more of your base structure (specifically, the tables, how they link together and the fields in each) so that I can better advise?

    JB

    S
    • Sofia_Linse
    • Author
    • New Participant
    • April 29, 2020

    Hm, that’s a good question. It’s an old Airtable that dates way before I joined. Since I’m new to Airtable, it’s challenging to distinguish the default functionality from what we have created on our end. I’ll try to explain the structure to you, but no worries if you don’t get it! What you’ve already provided me with is really helpful.

    As of now, we only add new records to a base called “Research sessions.” The primary field in that base is the date of each session. The table also contains a field called “Company” which displays the company participating in each session. This field is linked to a table called “Companies”.

    The “Companies” table has the company name as primary field. It also include a field called “Research sessions” which links to the records of dates, as described in the previous paragraph.

    The table I’m working in now is new, and I want to have the ability to paste a list of company names in one of the columns, and then have Airtable identifying these company names in other tables and check their previous research participation. As of now, the formula you suggested works on companies that only have one date recorded in “Research sessions”, but not for those with multiple dates.

    Let me know if any of this makes sense, and again, no worries if it doesn’t. Then I’ll find some other way :slightly_smiling_face:

    S
    • Sofia_Linse
    • Author
    • New Participant
    • April 29, 2020

    Hm, that’s a good question. It’s an old Airtable that dates way before I joined. Since I’m new to Airtable, it’s challenging to distinguish the default functionality from what we have created on our end. I’ll try to explain the structure to you, but no worries if you don’t get it! What you’ve already provided me with is really helpful.

    As of now, we only add new records to a base called “Research sessions.” The primary field in that base is the date of each session. The table also contains a field called “Company” which displays the company participating in each session. This field is linked to a table called “Companies”.

    The “Companies” table has the company name as primary field. It also include a field called “Research sessions” which links to the records of dates, as described in the previous paragraph.

    The table I’m working in now is new, and I want to have the ability to paste a list of company names in one of the columns, and then have Airtable identifying these company names in other tables and check their previous research participation. As of now, the formula you suggested works on companies that only have one date recorded in “Research sessions”, but not for those with multiple dates.

    Let me know if any of this makes sense, and again, no worries if it doesn’t. Then I’ll find some other way :slightly_smiling_face:


    Hi @JonathanBowen, I just learned that we’ll have a demo meeting with a CSM from Airtable soon so I’ll be able to ask these questions directly. It will be easier when I can share my screen. But thank you so much for your help!

      Terms & ConditionsAccessibility statement