Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 10, 2020 03:46 AM
I need to filter the output of a table to only include records that contain the word “attractions” in the Categories field.
I know it’s not correct but here’s what I tried: If({Categories} = attractions)
Can someone tell me where I’m going wrong in the code?
Thank you in advance for any help.
Solved! Go to Solution.
Mar 10, 2020 10:13 AM
This is expected because I was assuming things about your data model that were untrue apparently.
First clue - given the new information about the data model, Categories is a linked field (apparently). As such, you cannot filter based on “=”; rather, it likely requires the “FIND” (in field) operator, right?. The added complexity of the linked field could be at issue as well. Lastly, what scripting environment is the API call coming from?
Forum-debugging of API code (at best) is about guidance - we can provide guideposts, but we can’t build it because it would be extremely counter productive without full and complete access to all the resources - ergo, it’s now a project - ergo, it’s now a consulting gig.
Mar 10, 2020 03:59 AM
Hi @Ken_Cooper,
Are you trying to find the word “attraction” in a text in several fields or is it a single word in the field like in a Single Select or Multi Select Fields?
A simple way would be to use the Filter option (one one or more Fields) and choose contains attraction.
BR,
Mo
Mar 10, 2020 04:03 AM
Thank you @Mohamed_Swellam!
I’m trying to find the word “attractions” in a single field.
I have considered using the Filter option, but I’d have to create about 100 views in Airtable to do everything I need for this project.
I’m hoping to do it with API links to avoid having to create so many views.
Thank you again for your help!
Mar 10, 2020 04:21 AM
You’re welcome @Ken_Cooper
I believe the best option you have is Filter since you want to have in a view.
If you need to make a formula like the above one, you have to have 2 results (when true and when false). So for example, what do you want to happen in the field where you will type this formula is “Attractions” is there and what to happen when its not there?
BR,
Mo
Mar 10, 2020 04:28 AM
Thank you again @Mohamed_Swellam!
Please excuse my ignorance on this topic.
If the Categories field contains “attractions” include the record in the output. If the field does not contain “attractions” ignore the record.
When you say “Filter” do you mean creating Filter views in AirTable, or can I use “Filter” in the script?
Mar 10, 2020 04:42 AM
Oh ok ! I misunderstood, thought you are trying to make a view not create a script :slightly_smiling_face:
Yes I meant that the View can be filtered, Im not familiar with the script, sorry.
I believe other community members whom are familiar with the scripting block can be of help, but maybe they will need you to put part of the script or give more context.
Mar 10, 2020 04:52 AM
@Mohamed_Swellam… No problem. Thank you for trying to help. It’s much appreciated.
Because of my ignorance I’m probably now being very clear in describing my need.
This is the API link I have and it all works except for filtering records that contain “attractions”. I created the link below using “AirTable API Encoder”. If I don’t include the filter it works fine. When I try to include the filter AirTable doesn’t accept the link.
Mar 10, 2020 09:01 AM
There’s certainly something that doesn’t look right about this filter parameter. I assume “attractions” is a field containing one – and only one – actual attraction value, right?
One thing that looks suspicious is the lack of quotes around {Categories}. Shouldn’t it be …
&filterByFormula=if('{Categories}'+%3+attractions)
Have you tried hard-coding the filter parameter to debug it?
Example…
&filterByFormula=if('**actual category text**'%3'**actual attraction text**')
Recommendation …
Make sure it works without dynamic substitution and then solve the substitution algorithm requirements.
Mar 10, 2020 09:54 AM
Thank you @Bill.French!
Category is the name of the field and attractions is the actual text in the field that I want the API link to filter by. The Categories field can contain one or several of 103 different category types. Below is a screenshot of the table with the Category field and the “attractions” text in the field highlighted.
Please excuse my ignorance, but I’m not sure what you mean by hard-coding it. In AirTable, I have filtered the table using the Categories field for “attractions” and the filter returns the correct results, as demonstrated in the image below.
I tried the samples you included in your post, but couldn’t get it to work. I’m sure it’s something I’m doing wrong and if you can show me the correct way to make this filter work I’d be very grateful. I’ve tried everything I can think of the the last 2 days and can’t figure it out.
Thank you again for your help!
Mar 10, 2020 10:13 AM
This is expected because I was assuming things about your data model that were untrue apparently.
First clue - given the new information about the data model, Categories is a linked field (apparently). As such, you cannot filter based on “=”; rather, it likely requires the “FIND” (in field) operator, right?. The added complexity of the linked field could be at issue as well. Lastly, what scripting environment is the API call coming from?
Forum-debugging of API code (at best) is about guidance - we can provide guideposts, but we can’t build it because it would be extremely counter productive without full and complete access to all the resources - ergo, it’s now a project - ergo, it’s now a consulting gig.