Multiple selection field, date recognition

[Max_Ha]

Dear wizards of Airtable,

We run a program for kids this summer. Within a timespan of 4 weeks they can choose which day they’d like to join us. On their last day, they get a “good bye gift”. Unfortunatly this is easily forgotten. Since we run our registration and administration thru airtable I was hoping for you guys might now a solution for this. I’m looking for a formula that kinda goes like this:

IF: only on the last [sign up date] equals [today] status should change to, “good bye gift”

For example:
Johny is with us on monday, 4th, 5th and 6th of July. Only on July the 6th his status should change (not the other 2 days).

The “sign up date” field is a multiple-select field. I need a formula (and probably a little detour) that understands which date is the latest date in the sequence.

Thanks in advance!

[Justin_Barrett]

Am I correct in reading that the program lasts exactly three days? Could you provide more detail as to the setup of that multiple-select {Sign-up Date} field? That’s going to be a key item in the formula, but without knowing its design, we can’t provide a solution for you. If you could share a screenshot of that field’s setup dialog, that would be extremely helpful.

[Max_Ha]

Hi Justin,

Thanks for reaching out.

No it’s random… The program is 4 weeks in total. They can choose any day AND as many days as they like. Sometimes they sign up for a day, others only on mondays and some might pick a few days randomly…

Here’s a screenshot of the field (it’s in Dutch sorry)

[Justin_Barrett]

Max_Ha:

No it’s random… The program is 4 weeks in total. They can choose any day AND as many days as they like.

That’s the biggest hurdle, and unfortunately the most challenging one when it comes to Airtable’s available features. Because users can technically pick any days in any order, and Airtable keeps the entries in the order chosen (even when chosen via a form), it’s going to be very difficult to isolate the latest chronological date.

When querying a multiple-select field in a formula, Airtable returns a string of comma-separated items. The most that could be done with that is to isolate the last chosen item, but that wouldn’t necessarily be the last date chronologically if the user picked their dates out of order. If Airtable had more robust array processing capabilities, this might be a lot easier.

It might be possible to write a script in the Scripting block that parses the dates to determine the latest one, putting that date into a new field, but I’ve only done a little work in the Scripting block, so I’m not 100% certain of that method.

[Max_Ha]

I understand… And what if, instead of a ‘multiple selection’-field we use a ‘link to another record’-field… In that case every optional date would be a new record in a different tab? I suppose the system might recognise it as an actual date. Would that help?

[Justin_Barrett · Accepted solution]

That would definitely work, but be aware that when querying links in a formula, Airtable converts all the links in the order they were made into a single string, with the string representations of those primary field items separated by commas.

You would need to use a rollup field, and use the MAX() function there to find the latest date: