Surprising Result in Switch(

Panorama X
1

I have an integer field, WeekDay, which is either empty or it has integers 0 to 5, corresponding to the days of the week, Sunday through Friday. The switch( function gives a result that surprised me when the field is empty:

switch(WeekDay,0,"Sunday",1,"Monday", ...,"Saturday") ==> Sunday!!

So switch is evaluating the value of an empty integer field as zero, and not returning the default value. A workaround is:

switch(WeekDay,{},"Saturday",0,"Sunday",1,"Monday",...,"Saturday") ==> Saturday.

I had expected switch( to use the default value when the key was empty.

2

So switch is matching an empty field, which you meant to represent Saturday, with your listed value of 0 for Sunday. See if explicitly testing for an empty field first will give the desired results:
?(emptyfield(WeekDay),"Saturday",switch(your initial parameters))

3

Empty numeric fields always evaluate as the number zero (and did so in previous versions of Panorama as well). This happens before the switch( function even gets a chance to look at the data – in other words, the switch( function has no idea that the field is empty.

@JohnB is on the right track, but there is no emptyfield( function. You can use the sizeof( function to detect an empty value.

?(sizeof("WeekDay")=0,"Saturday",switch(WeekDay, … ))

Did you try this? I would not expect this workaround to work correctly. An empty numeric value is not an empty string, Panorama will calculate it as zero. At least that is what it should do. Ok, I did some tests, this formula sort of works. It will correctly display an empty field as Saturday, but it will also display 0 as Saturday, when you want Sunday. This is because it is evaluating the {} as 0.

So the correct solution is using ?( and sizeof( as I explained above.

4

I misread Help. What I’d intended was the emptycell( function. Would that work in place of sizeof( here?

5

Thank you both for the solutions.

6

Yes, in fact internally emptycell( is actually based on the sizeof( function. I forgot about it though, but now that you’ve reminded me I think it is clearer, as shown below. But either will work exactly the same.

?(emptycell("Weekday"),"Saturday",switch(WeekDay, … ))
7

On a related issue, if you have a data button for the integer field, and the integer button sets a value of zero, then the button will be checked when the associated field is empty!

8

An integer field always contains an integer value. If the cell is empty, that is actually a value of zero, but with an extra flag that tells Panorama not to display the zero in the data sheet. But any other code that wants to know the value will see zero, including formulas and data buttons.