SELECT() Expression

[tvinci]

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]

Rich:

Yeah the problem is with the double quotes around true.

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]

tvinci:

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

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]

tvinci:

there’s a bug in my app where this expression is returning stops from other routes as well

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]

tvinci:

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

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!!!