lookupRecords filtering based on a date's year

Hello everyone,
I have a data table named “Dates_gen” with a column named “Date_jour”, typed as date (format : “DD/MM/YYYY”)
In another table I need to know if there are already records for a specific year.

If I try this formula: Dates_gen.lookupRecords(Date_jour=« 2025-04-16 ») it displays Dates_gen[[2]] which means the filter returned one record from my data table, which is fine.

But I cannot find how to filter based on a specific year.
This formula for example: Dates_gen.lookupRecords(Date_jour.year=2025)
gives me a “expression cannot contain assignment” error.

Anyone can tell me how I should proceed to figure out if there are records for the year 2025 in my data table?

Thanks a lot
G.

Hey @Guillaume_L !

I would recommend adding a formula column to the Dates_gen table that pulls the year value for the value in the Date column. You can do this using the formula $Date_jour.year.

If all you need to know is that a record exists for a given year, you can use a lookupOne formula which is much more efficient than lookupRecords. Once it finds a single record that matches the given criteria, it stops. The formula in the screenshot below uses lookupOne to find a record where the year matches. Then, uses bool() to return either true or false. If it finds a single match, it returns true. If no match is found, it returns false.

Let me know if you have any follow up questions!

Thanks,
Natalie

Hello Natalie,
Thanks for your reply. I was kind of hoping to avoid adding another column but I guess I have no choice and I’ll go with that :slight_smile: