Formulas: logical function AND/OR not working as expected

[Ludom]

Hi!
I’m quite new to Baserow but have 15+ years of experience with dabases and an extensive experience with other no-code database solutions.

I’m trying to do something fairly basic in a formula field: use a series of and/or conditions to determine the value of a status field.
The problem is, AND and OR functions are limited to 2 arguments, which is quite unexpected and forces to add up many AND/OR operators. This renders formulas quite heavy and very hard to read.

Example with a very simple set of AND conditions:

• instead of:

AND(
  totext(field('Status'))="Validated",
  field('Name')!="",
  field('Description')!="",
  totext(field('Language'))!="",
  field('Rank')>0
)

• we need to write:

AND(
  totext(field('Status'))="Validated",
    AND(field('Name')!="",
      AND(field('Description')!="",
        AND(totext(field('Language'))!="",
        field('Rank')>0
))))

Is there a plan to support more than 2 arguments to AND and OR functions in the future and avoid impossible formulas with dozens of operators?

Best,
Ludo

[nigel]

100% agree that the AND/OR formulas and probably some other functions should work as you have proposed. CONCAT already works this way with a variable number of arguments so it’s very possible for the language to technically support this. Will open a feature request to track this.

[nigel]

Separate thought here, we could also add && (AND) and || (OR) operators to the language which in addition to a better and function. So you could write true && false && false = false etc

[Martin]

I’ve got the same problem.
But somehow the formula with && doesn’t work neither …

[davide]

Hey @Martin,

Currently, our AND and OR formulas don’t support more than two arguments. As Ludom mentioned in the first comment, we need to write them in a nested way:

AND(
  totext(field('Status'))="Validated",
  AND(field('Name')!="",
    AND(field('Description')!="",
      AND(totext(field('Language'))!="",
      field('Rank')>0
  )))
)

I’ve logged this issue here: Add `&&` and `||` operators in formula language (#2135) · Issues · Baserow / baserow · GitLab.
While I can’t promise a specific timeline for addressing this, I’ll discuss it with the team.
Thanks for your feedback.

[Ludom]

Hello @davide ,
Have you been able to discuss that with the product team? Is it sth that could be considered in one of the roadmap milestones?
Best,
Ludo

[davide]

Hello @Ludom,

I’ve created a MR with the changes here Add && and || operators to the formula language.

The changes need to be reviewed and tested, but there’s a high chance we can include them in the next release.

[Ludom]

Hi Davide,
Thank you very much, this looks great!
Just one thing: does that mean that we’ll be able to combine these in a formula (e.g. bool1 && bool2 && bool3 && bool4…)?

Best,
Ludo

[davide]

Yes, this will be a valid formula. You’ll need to properly use parentheses when combining the operators, but it’ll be possible to create, let’s say, a long list of && or || without parentheses.

Example of a working formula:

[Mary-Lou]

@Ludom Am I missing something or do AND/OR still allow only 2 arguments?

[davide]

The AND/OR functions? yes, they didn’t change. But now you can use the operators to create more readable combinations as described above.

[Ludom]

Hi @davide !
Since the last BaseRow release, I’ve tried using the && syntax but got the following type of error message every time:

Error with formula: argument number 2 given to operator && was of type number but the only usable type for this argument is boolean, argument number 2 given to operator > was of type number but there are no possible types usable here.

Here’s an example of formula that failed:
field('BooleanField1') && field('NumberField2')>0

I’ve tried this with a many different field types but got the same result. For instance:
field('BooleanField1') && field('SingleLineTextField2')!=""
gives an error message saying the 2nd argument is of type text - whereas it clearly is a boolean expression (I checked its validity by using a formula with only 1 term and it did return a boolean value).

Using parentheses like this:
field('BooleanField1') && (field('SingleLineTextField2')!="")
doesn’t change anything.

Could you help me here?

[davide]

Hi @Ludom,

it’s indeed necessary to use parentheses correctly to create a valid formula. A formula like this works on baserow.io: if(true && (field('nr') != 0), "hello", "bye")

[Ludom]

Hi @davide !
As mentioned above, I tried using parentheses and that did not work either.
From what I understand, the problem is that this syntax only works together with the if() function which is not the expected behavior.

As a matter of fact, the only way to make my expression work is to put it like that:
IF(field('BooleanField1') && (field('SingleLineTextField2')!=""), true, false)

Which means more complexity for no reason. It also means that these expressions won’t be usable with other functions unless combined with IF([BooleanExpression], true, false) - that is highly unusual and will surely be confusing for most users.

Best,
Ludo

[davide]

@Ludom can you share a bit more about your fields and your formula?

As you can see in the pictures below, a formula like field('BooleanField1') && (field('SingleLineTextField2')!="") should correctly create a boolean formula field without the need of an if

.

[Ludom]

Thank you @davide !
I’m surprised: the exact same formula I had tried (which generated the error message reported above) worked well today (I merely copy-pasted it again). Maybe there was an update in the latest BaseRow version?

Best,
Ludo

[Mary-Lou]

Are there any plans to allow more than 3 arguments?

[Mary-Lou]

I need three and-arguments in an if formula and can’t get it to work with the nested and formulas. Can anyone help me?

[davide]

You may only need to add some parentheses. Can you provide an example of what isn’t working so I can check?

[Mary-Lou]

if(
	and(
		not(isblank(field('Prefix'))), 
			and(field('Messaging Language') = "German", field('Gender') = "m")), 

concat("Herr",totext(field('Prefix'))), 

if(
	and(
		not(isblank(field('Prefix'))), 
			and(field('Messaging Language') = "German", field('Gender') = "f")), 

concat("Frau",totext(field('Prefix'))), 

"CHECK GENDER AND LANGUAGE"))

This is only the very first part of a complex formula we have used for years sucessfully in Google Sheets / MS Excel.

I tried what feels like a 100 different versions.