How do i return records that match 2 criteria?

jpj747 · ‎Dec 19, 2020

Hi

I’m using an integration via Integromat to pull in some records for a report function.

I have lots of records in my base for different names (Name) and have formatted a field called Period which is the date created in the format “YYYY/MM”.

So I may have:

Dave / 2020/11 / Some Data
Dave / 2020/12 / Some Data

I need to return all records for Dave in a given period.

I can use FIND(“Dave”, Name) to return all records for Dave which works great.

What I really want is WHERE period = “2020/12”

How can I create a formula to search the base by Name and Period?

Thanks

kuovonne · ‎Dec 19, 2020

Welcome to the Airtable community!

Do you want period to be a specific hard coded month? Or do you always want it to be the current month? If you want to use the current month, do date calculations based on TODAY().

You could use a view with two conditions: “is before” and “is after”.

You could use a formula field that uses a combination of AND(), IS_BEFORE(), IS_AFTER(), and/or DATETIME_DIFF().

You could us a formula that does comparisons of the MONTH() and YEAR() of the date.

You could use a formula that does a comparison with the YYYY/MM string.

All of these functions are described in the Date and time section of the Formula field reference.

jpj747 · ‎Dec 20, 2020

Hi

Thanks so much for getting back to me.

Are you suggesting that I can’t do a search on more than one criteria and instead I have to change the view to only contain the month I want?

kuovonne · ‎Dec 20, 2020

Not quite. I was trying to point out that there are many ways of going about finding the records you want. You also didn’t say how you determine which month is the month you want.

You can limit the records in a view by filtering based on AND or OR conditions, but not both. If you want to find all the records for Dave in the month of December 2020, you could have a filter using AND conditions.

Name contains “Dave”
Date is on or after Dec 1, 2020
Date is on or before Dec 31, 2020

This method uses only the filtering for the view, but you mentioned wanting a formula, so it wasn’t clear if you wanted to filter the view with or without a formula.

By the way, you say that you’re working on an Integromat integration. You haven’t said how you are searching for the records, but it seems like you are trying to limit records to a view. If you are trying to control the search from the Integromat side, please post more details.

jpj747 · ‎Jan 02, 2021

Now that I’ve had time to become more familiar with Airtable, I have come to realise that the search conditions are achieved within the view filters and formulas, which is what you were suggesting! Now that I appreciate this, it makes a lot more sense :slightly_smiling_face: I’m all good on this question now.