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?
Hi Paul, off the top of my head, you could do this in two ways:
Method 1: Use an automation and helper table
Create a “Helper” table and create a record there called “Search Helper”
Create a link between the data table and the “Helper” table
Create fields in the “Helper” table called “Location”, “Start Date”, “End Date”
Create lookup fields in your data table to display the data from those 3 fields
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:
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:
It wouldn’t have given you the linked Route/Trip object for each date easily
The resulting dates would have been (hard to read) text representations that wouldn’t have worked with the Date filters
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.