Nov 15, 2022 06:40 AM
I am relatively new to Airtable and have a specific Search/Filter issue that my colleagues and I have been attempting to solve for a while now. We are a small travel company that provides tours to our clients. Each tour has a unique route that it travels (with stops along that route).
The tour itself can run on multiple days following the same route.
I have set up our tables in a similar fashion to this screenshot:
We need to be able to create a Search that will return which Trip Name is in a City on a specific date or range of dates.
For example: we search Djibouti from 10/11/22 - 13/11/22, it would return BC, BB, BD, after which we could view individual records that BC, BB or BD are within.
Is this within the realms of possibility with Airtable?
Nov 15, 2022 09:11 AM
Hi Paul, off the top of my head, you could do this in two ways:
Method 1: Use an automation and helper table
The idea is to link all your data records to the “Search Helper” record, which would in turn display the “Location”, “Start Date”, and “End Date” value that you’ve keyed in
You’d then create a formula field in the data table that would check whether the record’s location and day’s matched the specified range. The formula would be…pretty long I think, as you’d be checking against each day and date
Your workflow would then be to go to the “Search Helper” table, key in the location and dates into that one record, and have a view filtered on the output of the formula field I mentioned in the previous paragraph
Theoretically this should work heh
Method 2: Write yourself a custom app that will output the data for you, something that looks like this:
Nov 15, 2022 09:37 AM
I was trying to do something very clever by passing back-references from Trips through Routes and into Destinations and then create a column called “Dates in Destination” with a list of every date where there is a trip in the destination, but:
All of this is to say that I agree with @Adam_TheTimeSavingCo’s approaches
Nov 16, 2022 01:00 AM
Thanks for taking the time to help me here @Adam_TheTimeSavingCo & @Nathaniel_Granor - I’ll take a look at your suggestion to build a ‘helper’ table Adam, but I’m actually leaning towards building the custom app as I think it will be more flexible for our situation.
Nov 16, 2022 01:12 AM
Yeah, custom app would be a lot cleaner
You’re probably already on top of this, but here’s a link to the Scripting docs; I got confused when I first started with the Scripting vs the Blocks SDK docs heh so figured I’d link you just in case