Dec 19, 2020 12:53 PM
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?
Dec 19, 2020 03:35 PM
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
You could use a view with two conditions: “is before” and “is after”.
You could use a formula field that uses a combination of
You could us a formula that does comparisons of the
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.
Dec 20, 2020 02:09 AM
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?
Dec 20, 2020 11:09 AM
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.
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.
Jan 02, 2021 02:27 PM
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.