Help

Discover what data silos are costing your org in our commissioned Forrester study. Learn more

Need help with IF statement including text array

Topic Labels: Formulas
Solved
Jump to Solution
1305 14
cancel
Showing results for 
Search instead for 
Did you mean: 

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?

image

I hope this is clear! Thanks in advance for your help!

1 Solution

Accepted Solutions

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.

See Solution in Thread

14 Replies 14

A couple questions for you, @Anne_Sophie_Asselin:

  1. How many features are there?
  2. Do the features change often? (new additions, removals, name changes)
  3. Are you on a Pro plan? (permanent access to blocks, and specifically the Scripting block)

Hi @Jeremy_Oglesby

  1. There are 12 features.
  2. The features won’t change.
  3. I am on the free trial right now which includes all the Pro features. I still have 8 days left so might as well take advantages of it! (but since it’s temporary, a formula might be better than the use of blocks?..)

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.

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.

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.

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!!

@Jeremy_Oglesby

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.

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.

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:

Well I’m glad I found such great help! :slightly_smiling_face: