Showing results for 
Search instead for 
Did you mean: 

Lookup Table search entries. Use formula Mode

Deputy Chef I
Deputy Chef I

Hello Automation Pros,

I've created a lookup table with column timestamp (assigning now()).

However I would like to retrieve all entries with a specific date, let's say today(DD-MM/YYYY) and not the entire timestamp.

I've try using lookup("Employee Failed Audit Lookup Table - DEV","Timestamp":)

but I don't seem to know how to truncate timestamp/change format in the column itself.

I don't want to create a separate column just for storing the current date due to the 10 column lookup table restrictions and endup with 2 columns. one for timestamp and another just for date.

If I search directly in the Lookup table just providing the date it works but not when using search entries.

Thanks in advanced


Deputy Chef I
Deputy Chef I

Store the timestamp in a fixed format without the time zones and hrs/min/secs


pull all records, and do a .where( ) function to isolate the date on a variable


Deputy Chef I
Deputy Chef I

I am not sure of your use-case, but for best performance and scalability, store date and other columns that you want to search/lookup your data in a separate column and all the metadata in a JSON or XML object under a data column.

Eg: If you currenctly have say 8 columns and you only want to search using 3 columns, then create a lookup table with 3 columns for each of the searchable datapoints and add the remaining columns to a JSON/XML object and store it in the 4th column. This will mitigate the risk of future updates and will assist with easy lookup logic.

Deputy Chef I
Deputy Chef I

Pull in the contents of the lookup table to a list and add that to a collection. Then you can execute SQL and and limit results based on your desired date field comparisons.

Deputy Chef I
Deputy Chef I

For the most part, I advise my team to use 9 columns max for a look up table, and use the last column as "Processed" flag, if the lookup table stores list of records to be processed.

As for the data format of look up tables, I don't think you can set the format and everything is stored as plain text. I look up for all entries where Processed is untrue, then use Ruby formula to convert the date column to date or datetime format.

Certainly open to suggestions and recommendations for handling this challenge.