Switch fails when result is 0

[sango]

Your Setup:

{“version”:“4.4.9”,“edition”:“enterprise edition”}

Describe the Problem/Error/Question:

I tried the switch formula to get from a select field to a numerical value for further processing.

But, it fails when i want to map a value to 0. Why?

Reproduce

Make a column with a selection. I.E. “good”, “neutral”, “bad”

I want to map “good”->1, “neutral” ->0, “bad” → -1

so i do this

switch({behaviour}, "good", 1, "neutral", 0, "bad",-1, "failed")

For good and bad, it works, for neutral, i get failed.

This seems to be wrong

[rdb]

SeaTable 4.4 is roughly a year old. Please update to SeaTable 5.2 and retry.

[abaer]

@rdb which is what we are planning in the next days

[sango]

We retested it on
{“version”:“5.2.7”,“edition”:“enterprise edition”}

Sadly same Result

[abaer]

Workaround with explicit ifs() works as expected

ifs(
isempty("Einfachauswahl"),0,
{Einfachauswahl}="Good", 1,
{Einfachauswahl}="Neutral", 0,
{Einfachauswahl}="Bad", -1,
True(),0
)

switch seems to have a problem with the return value 0, both on 4.4.9 and current 5.2.

Please verify if i’ve missed something

[sango]

That would work. Thank you.

Yet, i would be happy to use switch.

[rdb]

I cannot reproduce the issue.

This is the result when using switch() with strings:

This is the result when using switch() with integers:

You could argue that the cell should display “0”, but an empty cell and the value zero are basically the same. They behave the same when using them in formulas.

This said, I see another problem in your formula: The result type in a formula column is either string or number (or date or date-time, …). You are mixing number in string in your formula.

[sango]

Tested it as part of a calculation, and it seems to work with the paranthesis.

switch({Einfachauswahl}, “Good”, “1”, “Neutral”, “0”, “Bad”, “-1”, “failed”) * 100 ← works

switch({Einfachauswahl}, “Good”,1, “Neutral”, 0, “Bad”, -1, “failed”) * 100 ← does not work

[rdb]

From my point of view, this is a cosmetic issue. But it is an issue because it is inconsistent with the way other formulas work. I created a ticket.

[abaer]

Thanks for that! To build up knowledge for everyone, I jogged up my memory and link posts from the past that had the same general topic (i.e. consistent data type as a formula result):

How to create a formula if the source column contains as result #N/A

I have a formula which contains positive and negative numbers, as well as " #N/A " as result. I want to create a additional formular as an ifs, which says ifs({Column A}>= 1, “OK”, {Column A}<= -1, “Not good”, {Column A}= “#N/A”, “Not available”) However “#N/A” is not recognized, how to express this result in a formula?

Why is this formula not working

Not wanting to have the last world or be right (because both solutions work), I’ll just add the knowledge I acquired in long, frustrating sessions to prevent others from the same fate in more complex scenarios: Both methods make sure that the column only contains values of one type (String). How I understand it, the format detection takes the data in the first row it encounters. I just made a quick test with a formula that creates number, date, boolean and string values. ifs( {Name}="A",True()…

That’s where I learned the hard way that consistent output data type is a good idea

[abaer]

@rdb , sorry I have to strongly contradict you there regarding the cosmetic issue. That’s just for speeding up the fix by providing my findings.

Originally, I wanted to point out that an empty output instead of the integer 0 will cause confusion if that result is used in another formula. I would be tempted to use “isempty” to check for the seemingly empty cell, and you be very confused if it returned “false” because there was was a hidden 0 value in the cell.

However, that does not seem to be the case. Please verify this, but as it turns out, switch() seems to broken because it falls into the “default” output when one switch branch returns zero.

Here’s my test:

Intended behaviour:

• All consistent numerical outputs
• “Neutral” returns zero
• Empty cell returns -5
• Untreated values return -6
• Good = 1, Bad = -1

Reference formula with ifs

ifs(
isempty("Einfachauswahl"),-5,
{Einfachauswahl}="Good", 1,
{Einfachauswahl}="Neutral", 0,
{Einfachauswahl}="Bad", -1,
True(),-6
)

The same with switch(), making sure that the output in consistently the integer type:

switch(
if(isempty("Einfachauswahl"),"dummyentryforempty",{Einfachauswahl}),
"Good", 1,
"Neutral", 0,
"Bad", -1,
"dummyentryforempty",-5,
-6
)

My conclusion: The switch result “0” crashes into the “default” option => Bug?

The whole analysis was somewhat muddled by the fact that @sango used a “string” default value, which resulted in the value not being displayed because the detected column type was “number”.

Which is easily tested with this formula

switch({Einfachauswahl},"Good", 1,
"Neutral", 0,
"Bad", -1,
"Default") & " forced into String"

[sango]

True. But i had a reason why i wanted to use this inconsistency. As i do not want to have a number value which represents an error state. That would run the risk of creating errors where there is none.
In my original formula, the default case would be “not analysed” or similar. Representing the absence of a selected value.

Sure, it may not be a good practice, yet as it seemed possible from the documentation, i thought that would be a legit usecase.

[abaer]

You could still go with strings all the way as shown by @rdb above.

Then, when you really should need integers to do calculation, you could use value() in another formula to parse the strings “-1”, “1” and “0” into integers.

Which has the added charme to work around the current switch() bug (if it really is one). That’s why i think we are still not off-topic