Hi all!
I have two fields, both filled with features. I want to see which features are repeated in both fields.
For example, I want a column that would tell me that ‘Feature2’ is in both columns of row 1 (record1), Feature3 and Feature4 are both in the columns of record 2 and 'Feature1 and Feature3 are also both there in record 3.
Maybe a formula like: If the text in column A is similar to the text in column B, then insert it in column C?
I hope this is clear! Thanks in advance for your help!
Best answer by Jeremy_Oglesby
tx
OK – well, to do this with a formula, you could make an explicit comparison for each of the 12 features. Something like this:
IF(
AND(
FIND("Feature1", {expected features}&""),
FIND("Feature1", {Available Features}&"")
),
"-- Feature1 "
) &
IF(
AND(
FIND("Feature2", {expected features}&""),
FIND("Feature2", {Available Features}&"")
),
"-- Feature2 "
) &
IF(
AND(
FIND("Feature3", {expected features}&""),
FIND("Feature3", {Available Features}&"")
),
"-- Feature3 "
) &
IF(
...
)
Just continue repeating that pattern.
A couple questions for you, @Anne_Sophie_Asselin:
A couple questions for you, @Anne_Sophie_Asselin:
tx
tx
OK – well, to do this with a formula, you could make an explicit comparison for each of the 12 features. Something like this:
IF(
AND(
FIND("Feature1", {expected features}&""),
FIND("Feature1", {Available Features}&"")
),
"-- Feature1 "
) &
IF(
AND(
FIND("Feature2", {expected features}&""),
FIND("Feature2", {Available Features}&"")
),
"-- Feature2 "
) &
IF(
AND(
FIND("Feature3", {expected features}&""),
FIND("Feature3", {Available Features}&"")
),
"-- Feature3 "
) &
IF(
...
)
Just continue repeating that pattern.
+35This is one of those times when we really need more robust Array functionality built into Airtable! (The array functions are extremely limited at this point.) @Jeremy_Oglesby, do you have any insights on whether or not more enhanced Array functionality is on the horizon?
But luckily for @Anne_Sophie_Asselin, with such a small & non-changing list of features, you could just create a lengthy formula to deal with this. Looks like Jeremy created a great one for you! :slightly_smiling_face:
This is one of those times when we really need more robust Array functionality built into Airtable! (The array functions are extremely limited at this point.) @Jeremy_Oglesby, do you have any insights on whether or not more enhanced Array functionality is on the horizon?
But luckily for @Anne_Sophie_Asselin, with such a small & non-changing list of features, you could just create a lengthy formula to deal with this. Looks like Jeremy created a great one for you! :slightly_smiling_face:
No, I’ve seen/heard nothing about new formulas. I wonder if Scripting block was meant to be their “catch-all” solution for that kind of stuff – which would mean that, eventually, advanced array stuff will only be available to Pro workspaces.
OK – well, to do this with a formula, you could make an explicit comparison for each of the 12 features. Something like this:
IF(
AND(
FIND("Feature1", {expected features}&""),
FIND("Feature1", {Available Features}&"")
),
"-- Feature1 "
) &
IF(
AND(
FIND("Feature2", {expected features}&""),
FIND("Feature2", {Available Features}&"")
),
"-- Feature2 "
) &
IF(
AND(
FIND("Feature3", {expected features}&""),
FIND("Feature3", {Available Features}&"")
),
"-- Feature3 "
) &
IF(
...
)
Just continue repeating that pattern.
OH YESS!! Thanks @Jeremy_Oglesby it worked perfectly!
Just to make sure I understand well, Could you please explain why you added the &"" at the end of the lines starting with FIND?
Thank you so much for your help!
OH YESS!! Thanks @Jeremy_Oglesby it worked perfectly!
Just to make sure I understand well, Could you please explain why you added the &"" at the end of the lines starting with FIND?
Thank you so much for your help!
Sure –
The data for the Linked Record fields ({expected features} and {Available Features}) are held as arrays behind the scenes. When you pull the value of a Linked Record field into a formula, it comes in as an array of values.
The FIND() function only works on strings. You ask it to find a sub-string inside a larger string. It won’t work if you ask it to find a sub-string inside an array.
So, to force the Linked Record array into being a string, I concatenate the Linked Record field with an empty string – &"". This coerces the array into behaving like a string, so that the FIND() function works properly.
+35No, I’ve seen/heard nothing about new formulas. I wonder if Scripting block was meant to be their “catch-all” solution for that kind of stuff – which would mean that, eventually, advanced array stuff will only be available to Pro workspaces.
Ugh, that would be so terrible for the Airtable platform if that was the direction they were planning on going in. It would really hurt people like myself who aren’t JavaScript experts, and it would also mean that they’ve stopped investing in their own low-code/no-code backend.
Ugh, that would be so terrible for the Airtable platform if that was the direction they were planning on going in. It would really hurt people like myself who aren’t JavaScript experts, and it would also mean that they’ve stopped investing in their own low-code/no-code backend.
Ya – here’s hoping they continue to flesh out plain old formula fields still!!
+35Ya – here’s hoping they continue to flesh out plain old formula fields still!!
Ya – here’s hoping they continue to flesh out plain old formula fields still!!
I agree!! I also wish that they reached out to the community more & interacted with us more. Maybe even threw an annual Airtable developer’s conference or something. It’s almost like we’re entirely on our own over here.
Sure –
The data for the Linked Record fields ({expected features} and {Available Features}) are held as arrays behind the scenes. When you pull the value of a Linked Record field into a formula, it comes in as an array of values.
The FIND() function only works on strings. You ask it to find a sub-string inside a larger string. It won’t work if you ask it to find a sub-string inside an array.
So, to force the Linked Record array into being a string, I concatenate the Linked Record field with an empty string – &"". This coerces the array into behaving like a string, so that the FIND() function works properly.
OMG! It all makes perfect sense! That was VERY well explained! Thanks.
Where did you learn all this? Do you have secret ressources I could look in haha?
I know airtable has great documentation already… maybe I missed the best ones? OR you are just a Javascript expert? :stuck_out_tongue:
OMG! It all makes perfect sense! That was VERY well explained! Thanks.
Where did you learn all this? Do you have secret ressources I could look in haha?
I know airtable has great documentation already… maybe I missed the best ones? OR you are just a Javascript expert? :stuck_out_tongue:
I’m definitely not an expert, but I work in the software industry, so I do have some level of familiarity with how software and web technologies work :slightly_smiling_face:
And when it comes to Airtable specifically, lots of trial and error, testing, trying new things, etc. I’m a tinkerer, and sometimes I discover helpful things in my tinkering.
+35OMG! It all makes perfect sense! That was VERY well explained! Thanks.
Where did you learn all this? Do you have secret ressources I could look in haha?
I know airtable has great documentation already… maybe I missed the best ones? OR you are just a Javascript expert? :stuck_out_tongue:
Lol, I think that’s actually one of the big issues with Airtable — their documentation is very limited (at best). I think that the experts here are bringing over their own knowledge from other programming languages in the past & applying that same logic to Airtable. And, as @Jeremy_Oglesby said, a TON of trial & error! :stuck_out_tongue_winking_eye:
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
