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:
Link to example
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:
Link to example
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
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.
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.
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
