SELECT() Expression

tvinci · 06-19-2020 06:59 AM

Hello AppSheet Community

I wrote the below expression to retrieve name values from the locations table whose Route is Broward and are designated as Mandatory AM:

sort(select(locations[name],and([Route?]="Broward",[Mandatory AM?]="TRUE")))

So when I double-check it it makes sense to me, but there’s a bug in my app where this expression is returning stops from other routes as well. How do I correct this expression to be more specific?

Thank you.
-Tiffany

Rich_E

What type of a column is [Route?]? Make sure AppSheet didn’t autoset that column to be a yes/no column which it will do when you have a question mark in the column name. Also remove the quotes around true since you are checking true or false and not for the text “true”. It may or may not matter, but just in case.

tvinci

[Route?] is an Enum type. Should I change that?
I also tried using FILTER() instead of SELECT() but nothing changed.

Rich_E

Enum should be fine. I you temporarily add rich.ellis@appsheet.com to view your app definition, I can take a look and see what could be causing it.

tvinci

Okay I did it. The column is Products in the Clipboard table and the large expression is under Auto Compute -> Suggested Values.

Thank you.

Rich_E

Yeah the problem is with the double quotes around true.
change the select() to this:

sort(select(locations[name],and([Route?]=“Broward”,[Mandatory AM?]=true)))

tvinci

I did this and it didn’t make a difference for some reason. Is the problem the uppercase letters?

Steve

Since when?

Also, [Mandatory AM?]=true is redundant; [Mandatory AM?] is enough if the column type is Yes/No.

tvinci

Yep the type is YES/NO. I checked.

Rich_E

Here is a test of row 4 with TRUE:

Row 4 with “TRUE” quotes:

@Steve not sure I have always found it to cause me issues at times, so I never use quotes with y/n columns

tvinci

Isn’t this picture just the same results no matter the quotation marks? Maybe I’m misreading it.

Rich_E

No the results are in the left column. The first one returns 2 items, the second one has zero.

Other columns are blank when the quotes are used and not blank when no quotes are used. I checked in the table and it appears to be producing the correct results.

Steve

Do the “other routes” have blank Route? column values?

tvinci

Yes! Although I thought if the Route? column value was blank then the expression would evaluate to false and exclude that row.

Steve

Exactly the opposite.

Replace [Route?]="Broward" with "Broward"=[Route?].

tvinci

Wait so I was accidentally setting the value of Route? to equal Broward?

Steve

No, you weren’t setting anything; = is a comparison.

[Route?]="Broward" asks, is the Route? column vale the same as the literal text, Broward?

"Broward"=[Route?] asks, is the literal text, Broward the same as the Route? column vale?

The problem is that the = comparison has a quirk: if the value on the left side is blank, the result is TRUE regardless of the value on the right, so you’re best served to put a value you know will not be blank on the left.

tvinci

Okay I’m also going to eliminate the blank spaces in the backend and let you know if it works Thank you so much

tvinci

Hello AppSheet,

I updated my code and the backend and the problem persists. I’m going to email AppSheet Support about it but does anyone have any ideas on how to fix it? Thank you.

Lucinda_Mason

That’s a key quirk. I need to go check all my expressions with =

Wonder how many = I’ll find in my app documentation.

661!!!