Filter comma separated dates for an API Call

[David_Ohanessia]

Hello Everyone,

Maybe somebody form the community will help with something I’m struggling with.

I’d like to make an API call with a Filter formula on a lookup field, which includes comme separated dates, see below :

As you can see, there is a simple calculation field that is : IS_BEFORE(TODAY(),Booked_Dates) that doesn’t seem to be very happy with the multiple item field on Line 1.

However, when I’m filtering lookup fields with conditions in Airtable it seems to work perfectly fine (don’t notice both conditions, only the second one):

And the result :

So my question is : Is there a formula I can use to have the same behavior than Airtable’s lookup filter so I can make the appropriate API call in the FilterByformula query ? Or is there any workaround that one of you might suggest ?

The idea here is to look for an available timeframe in a booking system…

Thank you for your help.

Best,
D

[Justin_Barrett]

The core problem is that you’re pulling dates through using a lookup field. Lookup fields often return different data structures depending on the number of linked records and the type of data being returned. For a detailed breakdown, I built a table to keep track of the differences:

Airtable

Airtable Field Reference - Airtable

Explore the "Airtable Field Reference" view on Airtable.

Long story short, the lookup field is returning a single datetime when there’s only one linked record. Once there are two linked records, then it most often starts to return an array. While Airtable does have a few array functions, they’re fairly limited, and none of them allow for processing the individual items inside the array.

David_Ohanessian:

Is there a formula I can use to have the same behavior than Airtable’s lookup filter so I can make the appropriate API call in the FilterByformula query ?

Unfortunately not.

David_Ohanessian:

Or is there any workaround that one of you might suggest ?

What table is being referenced by that {Booked_Dates} lookup field? Add a field there that uses the IS_BEFORE(TODAY(), {Date field here} formula, which will output either a 1 or 0. Then turn your existing {Calculation} field shown above into a rollup to pull in and process those formula field values.

The problem then becomes the aggregation formula to use in that rollup field. Your description above doesn’t make it clear if you want that formula to output a 1 if both dates are after today, or if either date is after today.

• If you want a 1 if any date has met the test in the other table use OR(values) function
• If you want a 1 if all dates meet the test, you’ll have to use IF(values, AND(values)). Only using AND(values) oddly enough will still output a 1 when there are no links, so that slightly longer version is necessary to handle that)

[David_Ohanessia]

Thank you @Justin_Barrett !! This is so helpful.

However, when I’ve posted my request, I thought there might be a straightforward answer to my issue. So in order to make things clear here is what i’m trying to do :

I need to parse time frame availabilities (stored in lookup cell in let’s say “Table A”) from a date posted in another table (“Table B”). If there is no overlap in a certain row, then a match is created in “Table C” which is the source of the aggregated lookup values in “Table A”

I was thinking that it was possible to do by making an API call in Table A filtered by the Date/Time parameter in Table B using a FilterbyFormula Eg : List all records for which record’s Date/time in Table B is not overlapping with a value in the lookup cell in Table A. Then, from those filtered records in the API call, pick an available record randomly (using something like Zapier Utilities), and create a new record in “Table C” to make a match. This record will then have the Date/Time from Table B, sent back to “Table A” through Linked Records + Lookup Value and won’t be available anymore if a new request come through, and so on…I don’t know if I’m clear enough though :slightly_smiling_face:

But you said this was not possible because of the structure of the lookup cell by itself if I understood correctly.

So I understand that there might be a way to achieve this by doing calculations in the Tables themselves before making the API call. Therefore I can filter the result based on those calculations results and the one you provided above are a good start…But I don’t know how to proceed, probably because I’m lost here :- :sleepy:

Thank you for your input and your help.
Best,
D

[Justin_Barrett]

David_Ohanessian:

I don’t know how to proceed, probably because I’m lost here

I’ve read over your message a few times, and I’m admittedly still lost myself. Let’s step back from the technicalities of the setup for a moment. Could you explain in a big-picture sense what you’re trying to achieve and the workflow that you envision? It sounds like a reservation system of some kind. Whatever the case, just describe the features you’d like this system to have, and then we can look at how to pull it off with Airtable (and any other tools if necessary).

[David_Ohanessia]

Hi @Justin_Barrett !!

Thank you for your response and sorry for the complexity of my message. I’m translating my thoughts in English while typing so maybe there is a reason why you’re also lost :slightly_smiling_face:

Yes, I’m trying to achieve a reservation system with multiple slots management. In between our messages I found a solution with the help of Zapier.

However, here is what I’m trying to achieve :

1. Clients can book Tuition courses and on selected timeframes from teachers (see below)
2. Some teachers are available on specific timeframes
3. AND can teach multiple kind of courses (topics), whatever the timeframe is, as long as they are available,
4. When a Client book a course, I need to find the right available teacher for the timeframe, book him to my client, and then make the other topics that the Teacher can propose unavailable for this specific timeframe until the former course has been done.

And you can imagine this with multiple teachers that propose multiple courses and topics. The issue I’m facing is how to handle the Timeframe slots and availabilities.

Here is what I did using Zapier :

1 - Client books tuition course with an Airtable Form (trigger)
2 - Zapier calls a “Booked_Tuitions” Table to look for Teachers already booked on the same Timeframe
3 - Then Zapier looks for a Teacher that pertains to both the requested tuition and timeframe availability filtered by the responses of the above API call
4 - We create a new record in the “Booked Tuitions” Table that confirms the booking slot to the Client.

This is working well as long as the “Booked Tuitions” table is up-to-date.

If you feel there is a better way of doing this feel free to tell me, but as long as I found a workaround by myself it’s fine so far.

Take care
D