Accepted Solutions
Welcome to the community, @Marc_Loskill! :grinning_face_with_big_eyes: Thanks for sharing the video, which really helps to clarify your desired goal. As you might have noticed, Airtable records can’t directly access other record data in the same table, so there’s no way to say, “Look at the previous/next record for comparison.” True, you could add a link field and link each record to the one before it, but a) that’s a pain, and b) even that has limitations.
My first thought was that slight variation of your second formula might work. Instead of checking to see if the date is in the same week as NOW(), add one week to NOW() and compare against that:
IF(IS_SAME({Webinar date}, DATEADD(NOW(), 1 "week"), "week"), "YES", "no")
If your webinars are mid-week, though, that’s going to start giving you “no” at the beginning of the week leading up to the next webinar, which isn’t what you want.
A more accurate method would be to use DATETIME_DIFF() to look at how many days there are until the next webinar date, and only mark those with “YES” where that difference is 14 days or less (I noticed that most of your dates are 14 days apart, though a couple varied from this pattern). Try this:
IF(
AND(
{Webinar date} > NOW(),
DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14
),
"YES",
"no"
)
You could also simplify this a bit: drop the outer IF() function and the text output, which will put a 1 in the record of the next webinar, and a 0 in all the others. That would still allow you to filter for that next webinar (assuming that you’re using the output to drive a filter).
AND({Webinar date} > NOW(), DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14)
If your schedule ever varies away from the 14-day pattern again, you could change 14 to a slightly higher number and it should still work.
Welcome to the community, @Marc_Loskill! :grinning_face_with_big_eyes: Thanks for sharing the video, which really helps to clarify your desired goal. As you might have noticed, Airtable records can’t directly access other record data in the same table, so there’s no way to say, “Look at the previous/next record for comparison.” True, you could add a link field and link each record to the one before it, but a) that’s a pain, and b) even that has limitations.
My first thought was that slight variation of your second formula might work. Instead of checking to see if the date is in the same week as NOW(), add one week to NOW() and compare against that:
IF(IS_SAME({Webinar date}, DATEADD(NOW(), 1 "week"), "week"), "YES", "no")
If your webinars are mid-week, though, that’s going to start giving you “no” at the beginning of the week leading up to the next webinar, which isn’t what you want.
A more accurate method would be to use DATETIME_DIFF() to look at how many days there are until the next webinar date, and only mark those with “YES” where that difference is 14 days or less (I noticed that most of your dates are 14 days apart, though a couple varied from this pattern). Try this:
IF(
AND(
{Webinar date} > NOW(),
DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14
),
"YES",
"no"
)
You could also simplify this a bit: drop the outer IF() function and the text output, which will put a 1 in the record of the next webinar, and a 0 in all the others. That would still allow you to filter for that next webinar (assuming that you’re using the output to drive a filter).
AND({Webinar date} > NOW(), DATETIME_DIFF({Webinar date}, NOW(), "days") <= 14)
If your schedule ever varies away from the 14-day pattern again, you could change 14 to a slightly higher number and it should still work.
@Marc_Loskill Not likely. Airtable has many features that are similar to spreadsheets, but underneath it all its design is much closer to a database, where records don’t necessarily have a set order until they are retrieved and sorted for specific purposes. Airtable’s default grid view creates an impression of order, with records appearing to be “before” or “after” other records, but even when a view has been set to sort the records a specific way, the order has no inherent meaning at the data level, and records have no default connection to other records. It’s a key point to know that distinguishes databases from spreadsheets, and it occasionally leads to some debate over whether or not Airtable should provide some default record-to-record connection/relationship. While the developers do try to occasionally add features that make it more friendly to folks coming from a world of spreadsheets, my personal feeling is that this particular behavior isn’t likely to change.
While typing that up, I thought about another way to address this issue. It’s not necessarily better, but may help you to understand some of Airtable’s inner workings more deeply. This solution involves linking to another table, and the most logical way that I could think of to incorporate that would be to build a table for all of the webinars that you plan on hosting/producing. Even if it’s only one webinar for now, there’s some benefit to having general data about the webinar in its own space, plus it opens the door for this other solution.
I made a [Webinars] table for this purpose and made a single record in that table. I then made a link field in the other table—which I’m calling [Webinar Sessions] for now—and set that to link to records in [Webinars]. I won’t get into the nitty gritty of link fields, but if you’re not familiar with them, I recommend checking out this overview:
In that field, I linked each webinar session record to the lone webinar record from the [Webinars] table. With that done, it looks like this:
Over in the [Webinars] table, that lone record looks like this, with incoming links from all session records in the first field.
In this table I can add a rollup field. Rollup fields are designed to process data from linked records. This is done by targeting a single field from the table that’s the source of the linked records, and then telling Airtable what you want to do with the data collected from that field.
I’ll start setting up the rollup field like this, telling it to give me the contents of the {Webinar date} field:
One of the options that you see there is to restrict the collected data to records that match a certain criteria. Switch that on, then set it as follows:
That isolates those records for webinars in the future.
Now we need to add an aggregation formula. This tells Airtable how to process the data collected from those fields—in this case, the collection of webinar dates that are later than today. We can find the earliest of those dates with the MIN() function, which returns the lowest option in a collection (in this case, the lowest/earliest date). The data collected is made available in the formula via the “values” keyword, so the aggregation formula becomes this:
MIN(values)
The aggregation formula field defaults to simple formulas like this, so just start typing “min” and you’ll see the list of suggested options pare down. When you see “MIN(values)” appear, you can either click it with the mouse to insert it, or use the cursor up/down keys to select it and then hit Enter.
With that set and the rollup field saved, we have this:
Now no matter how close or far apart you schedule your webinar sessions, you can always see the one coming up next in that rollup field.
BTW, there’s also a way to take this info and get the same “Yes”/“No” output in the [Webinar Sessions] table. If you want to go that far, let me know.
