Format rule not applied properly

[tvinci]

Hi everyone,

I want to apply a highlight format rule only when the value of GPS Check-In is NOT blank, but no matter how many times I play with the If Condition, all of the client names remain highlighted. I tried fixing this problem with a virtual column to no avail. Any ideas? Thanks

[tvinci]

I just realized the reason for AppSheet’s confusion. I do populate the GPS Check-In column but afterwards I have an option so that the user can create a new row for the same location, so effectively, from the back-end perspective, the GPS Check-In column is always blank.

[Derek3]

Hi Tiffany,

It sounds like you’re on the right path already. One other thing that jumps out from the “If this condition is true” formula is the Questions[GPS Check-in] part.

That formula will check if any row of the entire “GPS Check-in” column is populated.

If you want to check a specific row of the “GPS Check-in” column, you would need to update your formula.

[tvinci]

Thanks Derek! How do I update the formula to check for only a specific row?

[Derek3]

Using a SELECT() function is common way.

A SELECT() function allows you to select a column from a table (e.g. Questions[GPS Check-In]) and then apply some filter criteria to determine which rows to return.

The filter criteria to apply in the SELECT() statement depends on how your app is setup and what rows you want to return.

Do you have a reference setup between the tables? In other words, does either the “v1.6.0” table or the “Questions” tables have a column with data type “Ref” that references back to the other table?

[tvinci]

Thanks @Derek.

I used the SELECT() statement and received this error message:

[Derek3]

Hi Tiffany,

The SELECT() statement will always return a list. You can convert the list to a single value by wrapping the SELECT() statement in an ANY() statement.

For example:
ANY(SELECT(…, …))

This will return one random value from the SELECT() statement. This pattern is generally used when you are expecting only one value from the SELECT().

[tvinci]

I used this expression and the text still will not highlight. Thank you.

[tvinci]

This is what the backend looks like. The last entries were input by the app.

[Derek3 · Accepted solution]

The SELECT statement goes through each row of the “questions” table and checks if it meets the conditions you specify. The problem appears to be in the conditions.

Part 1:
The syntax: table_name[column_name] will return the entire column as a list. So when you write questions[Unique ID], AppSheet will return the entire “Unique ID” column for every check on every row.

You probably want to check the value of “Unique ID” for that specific row. You can do that by writing
[_THISROW].[Unique ID]

Part 2:
The conditions in your SELECT statement are getting into more advanced use of formulas. Typically, the conditions of a SELECT statement will check each row against some static value. For example [_THISROW].[Color] = “Red”. However, in your example, the conditions are checking each row against a value in another table.

It can be done, but it is a challenging place to start if you’re still getting familiar with SELECT statements. If you’d like to give it a shot, then you’ll need to apply the same concepts to the masterDatabase[ID] part of your conditions. This is currently returning the entire “ID” column. Try taking the same concepts and using them to select a specific value from the “ID” column of the “masterDatabase” table.