Need Search results given a date and a string

[Paul_Brown]

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?

[TheTimeSavingCo]

Hi Paul, off the top of my head, you could do this in two ways:

Method 1: Use an automation and helper table

1. Create a “Helper” table and create a record there called “Search Helper”
2. Create a link between the data table and the “Helper” table
3. Create fields in the “Helper” table called “Location”, “Start Date”, “End Date”
4. 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:

Link to example

[Nathaniel_Grano]

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

All of this is to say that I agree with @Adam_TheTimeSavingCo’s approaches

[Paul_Brown]

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.

[TheTimeSavingCo]

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