Skip to main content

If statement that checks if array < 2

  • September 19, 2023
  • 9 replies
  • 88 views
A
Forum|alt.badge.img+2
Hi, I'm trying to make a formula that combines different records to a readable title.
I'd like to add the Product field to the formula, but only if it has not more than one value and only if the value is not 'Algemeen'. The last part I can fix, but I need some help with the check for the amount of values.
 
The current formula combines 5 fields, with only a check for the Product field. Any help for the extra check is appreciated.

 

{Team} & "-" & {ID} & " | " & IF({Product} != 'Algemeen', {Product} & " | ", "") & {Section} & " | " & {Name}

 

 

9 replies

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 19, 2023

In short, you can apply math to binary expressions this way:
(at least two must be empty, and Product is not Agleemen)

IF( ((Product='')-(Product='Agleemen')+(Name='')+(Section='')) >1, 'true', 'false')

  
but if you want it just because you have trouble with '|' dividers ,  there is another solution for that, here in community , which is more elegant and readable. sorry, I haven't time to find it now.

A
Forum|alt.badge.img+2
  • ArnHub
  • Author
  • New Participant
  • September 19, 2023

In short, you can apply math to binary expressions this way:
(at least two must be empty, and Product is not Agleemen)

IF( ((Product='')-(Product='Agleemen')+(Name='')+(Section='')) >1, 'true', 'false')

  
but if you want it just because you have trouble with '|' dividers ,  there is another solution for that, here in community , which is more elegant and readable. sorry, I haven't time to find it now.


Thanks Alexey for looking into this. However I think we misunderstood each other. I was not trying to look for a statement to check if Product was empty, I only want to use Product if it has only one value, because it's an array. Users can add multiple products to a record and I don't want to print them all in the field.

So I need two rules:
IF (product has 1 value in array) AND (product value is not 'Algemeen')

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • September 19, 2023

@ArnHub Can you include screen shots with specific examples?
It can also be tricky to determine when a cell value in Airtable is actually an array. Very few fields product true arrays in Airtable. Sometimes lookups and rollups produce arrays; sometimes they don't. Linked record fields in formulas might look like arrays, but they are text strings as far as formulas are concerned. So it is important to know the field types for the fields involved.

Alexey_Gusev
Forum|alt.badge.img+25
  • Alexey_Gusev
  • Brainy
  • September 19, 2023

Thanks Alexey for looking into this. However I think we misunderstood each other. I was not trying to look for a statement to check if Product was empty, I only want to use Product if it has only one value, because it's an array. Users can add multiple products to a record and I don't want to print them all in the field.

So I need two rules:
IF (product has 1 value in array) AND (product value is not 'Algemeen')


You can turn array to comma-separated text by adding the empty string, and then count commas by substitute. But that method is not very reliable, depending on field type.

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • September 20, 2023

If you want to count the number of linked records, add a rollup field and count with "COUNTALL(values)" formula.

You can refer to it in the Formula field. 

A
Forum|alt.badge.img+2
  • ArnHub
  • Author
  • New Participant
  • September 20, 2023

@ArnHub Can you include screen shots with specific examples?
It can also be tricky to determine when a cell value in Airtable is actually an array. Very few fields product true arrays in Airtable. Sometimes lookups and rollups produce arrays; sometimes they don't. Linked record fields in formulas might look like arrays, but they are text strings as far as formulas are concerned. So it is important to know the field types for the fields involved.


The Product field is a reference field to a list, where you can link to multiple records (that's why I assumed an array):

 

This is the list that's referenced to:

So if I reference to 3 Products, they are all added to the title field by the formula:

"SV-999 | Bedrijfsauto, Bromfiets, Fiets | Funnel | Name of test"

But I'd like to have those 3 Products not included in the title when there's either:

  • More than 1
  • Less than 1
  • If the value is "Algemeen"

Not sure what to do and what a rollup field is that @Sho mentions.

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • September 20, 2023

 

Read about the Rollup field here.
Rollup Field - Overview | Airtable Support+

This will give the number of linked products. Assume that the Rollup field is named "Count".
In this case, the formula for the field in the title would look like this

 

IF( AND( {Count}=1, NOT("Algemeen",FIND({Products})) ), {Products} )

 

 

A
Forum|alt.badge.img+2
  • ArnHub
  • Author
  • New Participant
  • September 20, 2023

 

Read about the Rollup field here.
Rollup Field - Overview | Airtable Support+

This will give the number of linked products. Assume that the Rollup field is named "Count".
In this case, the formula for the field in the title would look like this

 

IF( AND( {Count}=1, NOT("Algemeen",FIND({Products})) ), {Products} )

 

 


Thank you for the effort @Sho. This sounds like a possible solution, however I find it hard to understand how to configure the rollup field. It's not possible without a rollup-field?

S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • September 20, 2023

Possible, but not if the Products name contains ", ".

IF( AND( NOT(FIND(", ",{Products})), NOT(FIND("Algemeen",{Products})) ), {Products} )

 

Terms & ConditionsAccessibility statement