topic Re: Lookup Table search entries. Use formula Mode in Workato Pros Discussion Board

Lookup Table search entries. Use formula Mode

bcastro — Sat, 30 Apr 2022 06:40:11 GMT

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

Re: Lookup Table search entries. Use formula Mode

bpay — Sat, 30 Apr 2022 06:56:33 GMT

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

Re: Lookup Table search entries. Use formula Mode

prerak-rustagi — Sat, 30 Apr 2022 16:08:19 GMT

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.

Re: Lookup Table search entries. Use formula Mode

david-macintosh — Sat, 30 Apr 2022 23:20:57 GMT

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.

Re: Lookup Table search entries. Use formula Mode

lee-jaemin — Mon, 02 May 2022 08:03:02 GMT

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.

Re: Lookup Table search entries. Use formula Mode

susrith-kalluri — Mon, 02 May 2022 19:35:09 GMT

Try using this formula

lookup("Employee Failed Audit Lookup Table - DEV","Timestamp":(datapill.strftime("%m/%d/%Y")))[Column_name]

Syntax:lookup("TABLE_NAME", "REFERENCE_COLUMN": datapill)["LOOKUP_COLUMN"]

Re: Lookup Table search entries. Use formula Mode

bcastro — Mon, 02 May 2022 21:32:52 GMT

thank you all for your responses!! . I actually opened a ticket service with Workato regarding the lookup function. I am passing datapill.strftime("%m/%d/%Y") but it is not filtering the values and instead returning all entries. For now as suggested I am storing the timestamp without timezone and pulling all records and then storing them on a collection and doing SQL.