topic Search lookup table entries using a "before or on" date in Workato Pros Discussion Board

Search lookup table entries using a "before or on" date

mgrezak — Thu, 27 Jul 2023 22:19:59 GMT

Hi Community,

I would like to retrieve any entries in a lookup table that have a date that is BEFORE OR ON today's date.

It seems that using the search action I can only give a value such as TODAY. How could I use a <= or Before or On search parameter on a column in my lookup table?

I feel like such a feature in lookup table would be very useful if it does not exist today.

Thank you,

Re: Search lookup table entries using a "before or on" date

steven-marissen — Fri, 28 Jul 2023 08:20:49 GMT

I don't think what you're looking for is currently possible directly on a lookup table action (not via formula approach to lookup tables).

Plenty of other options though:

'Get all entries' from the lookup table
1. use the 'where' formula on that list to keep only what you need
2. Load the entries into a collection and use SQL to keep what you need

Re: Search lookup table entries using a "before or on" date

rachelnatik — Fri, 28 Jul 2023 13:14:18 GMT

Hmm, I feel like you should be able to lookup a row based on the date. But if you can't - just query the table and then filter to the one you need.

Re: Search lookup table entries using a "before or on" date

marlon-muela — Tue, 01 Aug 2023 05:08:16 GMT

Hi,

This is Marlon from the Support team.

The input field for Search Entries in a lookup table uses an exact value. For example, you wanted to search for a row created today. You cannot use a formula or search for rows created before or less than a date.
What I can suggest is to get all entries and then use an SQL collection to create a query.