Date difference between the values of the linked field

[tiolan]

In the Client table I have the Meetings field, linked to Meeting table. A client can have multiple meetings.

I need to know, if a client has any two meetings closer to each other, then 1 month. If the difference between any two dates is less then a month – formula must return “often”.

Help me please! I can’t figure out…

[Andy_Lin1]

This is tricky to accomplish in Airtable, since unlike in Excel, there’s no concept of row numbers or any inherent sequential order. That being said, there are a few ways you can go about it:

1. Use a few helper fields to get the job done (adapt the field names I’ve used below to your tables).
   a. In the Meeting table, create three new fields:

   • “Previous Meeting”, an linked record field that links to the record of the previous meeting with this client
   • “Previous Meeting Date”, a lookup field that looks up the date field (e.g. “Meeting Date”) of the “Previous Meeting” record.
   • “Days Since Previous Meeting”, a formula field with DATETIME_DIFF({Meeting Date},{Previous Meeting Date},"days")

   b. In the Client table, create two new fields:

   • “Minimum Days Between Meetings”, a rollup field based on “Meetings”, “Days Since Previous Meeting”, and the formula MIN(values)
   • “Often?”, a formula field with IF({Minimum Days Between Meetings},IF({Minimum Days Between Meetings}<30,"Often")) (The first IF function is to handle cases where a client only has one meeting in the table.)

2. You could use an automation that updates the “Often?” field (by using a script instead of a formula) whenever the Meetings field is updated for a client. (This is a bit more involved and requires you to be on a Pro plan or higher.)

3. You could run the script mentioned in #2 manually. (This can be done on any plan, provided you’re not using any other extension, since scripts are considered a type of extension.)

4. You could do some advanced Airtable-fu, which I’ve never attempted before, but would be relatively automatic and doesn’t require any scripting or automation support: Multi-record calculations (c.f. 'running balance,' 'row number,' and the like)

If you’re not familiar with Javascript or advanced Excel trickery, I’d go with the first option. Airtable’s formula functions for arrays is… lacking, so helper fields are the way to go.

[tiolan]

Thanks for the ideas! They are very helpful:)

But how can we automatically link the actual meeting to the record of the previous meeting? Or get the date of last meeting before actual?

(I’m not familiar with Javascript and on a Pro plan)