Hello!
I have an inspection app that uses a form to check different parts of a car.
Each inspection item is a different column, e.g. Brakes, windshield, tires, engine, etc. and user selects either โYesโ or โNoโ during the inspection.
Iโm trying to create a virtual column that summarizes ONLY the โNoโ values in this row so a mechanic knows which items need to be addressed and ignores the โYesโ values.
For instance, if the windshield and tires are listed as โNoโ during an inspection:
The virtual column name would be โAlert! Check these itemsโ where the desired output of the column would be โWindshield, Tiresโ.
I have 50 items as part of the inspection, so would need these to be a dynamic formula if possible.
Iโve tried the select and list functions, but keep getting errors. Any pointers in the right direction?
Solved! Go to Solution.
Try something like the following as the App formula expression of your virtual column:
(
LIST(
IFS(NOT([Brakes]), "Brakes"),
IFS(NOT([Windshield]), "Windshield"),
IFS(NOT([Tires]), "Tires"),
...
)
- LIST("")
)
Each IFS() line is of the format, IFS(NOT([column-name]), "display-name")
, where column-name
is the name of the column to check,and display-name
is the text you want displayed if the column has a No (FALSE) value. Add as many IFS() lines as needed, one per column to check.
You can automatically hide the virtual column if the above list is empty (i.e., thereโs nothing to check) by setting the virtual columnโs Show? expression to:
ISNOTBLANK([_THIS])
See also:
Try something like the following as the App formula expression of your virtual column:
(
LIST(
IFS(NOT([Brakes]), "Brakes"),
IFS(NOT([Windshield]), "Windshield"),
IFS(NOT([Tires]), "Tires"),
...
)
- LIST("")
)
Each IFS() line is of the format, IFS(NOT([column-name]), "display-name")
, where column-name
is the name of the column to check,and display-name
is the text you want displayed if the column has a No (FALSE) value. Add as many IFS() lines as needed, one per column to check.
You can automatically hide the virtual column if the above list is empty (i.e., thereโs nothing to check) by setting the virtual columnโs Show? expression to:
ISNOTBLANK([_THIS])
See also:
Thank you so much. This solution works great.
Just curious, with virtual columns with this many IFS expressions, how does this affect performance?
The expression is not particularly heavy; checking the value of a Yes/No value is very light-weight. Itโd be much worse if were looking at other rows, or other tables, even. That said, good for you for being concerned about the possible performance impact of a virtual column. In your case, since the virtual column is looking exclusively at its own row, and will update only with a change elsewhere in its own row, you could use a normal (non-virtual) column here instead. Keep the expression in the columnโs App formula. Doing this will take up more space in your spreadsheet, but is a huge win for performance over time.
Excellent, thanks again.
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |