Solved: Summarize Yes/No Columns from Inspection

jtelson · 06-04-2020 04:46 AM

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?

Steve

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:

View solution in original post

Steve

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:

jtelson

Thank you so much. This solution works great.

Just curious, with virtual columns with this many IFS expressions, how does this affect performance?

Steve

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.

jtelson

Excellent, thanks again.