Lookup Table search entries. Use formula Mode

[bcastro]

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

[bpay]

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

then

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

[prerak-rustagi]

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.

[david-macintosh]

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.

[lee-jaemin]

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.