Help

If statement that checks if array < 2

Topic Labels: Formulas
1040 9
cancel
Showing results for 
Search instead for 
Did you mean: 
ArnHub
4 - Data Explorer
4 - Data Explorer
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 9

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')

@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.

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.

Sho
11 - Venus
11 - Venus

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. 

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

2023-09-20 08_27_55-CRO proces Univé_ Experimenten - Airtable en nog 5 andere pagina's - Werk - Micr.png

 

This is the list that's referenced to:

2023-09-20 08_28_22-CRO proces Univé_ Product - Airtable en nog 5 andere pagina's - Werk - Microsoft.png

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

2023-09-20 08_36_55-CRO proces Univé_ Experimenten - Airtable en nog 5 andere pagina's - Werk - Micr.png

"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.

Sho
11 - Venus
11 - Venus

 

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?

Sho
11 - Venus
11 - Venus

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

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