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
Solved! Go to 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.
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.
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.
Thanks Derek! How do I update the formula to check for only a specific row?
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?
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().
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.
User | Count |
---|---|
43 | |
28 | |
23 | |
14 | |
14 |