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

[mgrezak]

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, 

[steven-marissen · Accepted solution]

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:

1. '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

[steven-marissen · Accepted solution]

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:

1. '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

[rachelnatik]

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.

[marlon-muela · Accepted solution]

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.