Items requiring attention at end of form

[SKETCHwade]

Hi,

I have a form that has a condition at the end that provides a result based on the answers above. If everything passes inspection the result is pass, if something failed the result is failed.

IF(OR(
[Are trucks, cars & trailers parked to reduce impact on road users and pedestrians] = No,
…
[Containers correctly marked] = No
)=Yes,“Requires Attention”,“Satisfactory”)

I am trying to create an “items requiring attention” column that does the same as the above column, but in this situation lists the columns that failed.

I have looked at the switch and ifs functions but they both provide a result if it gets a match.
I cant think of a way to get it to work.

[Suvrutt_Gurjar]

One possible option is to use format rules for the failing and passing columns wih different colors. This will visibly show the status in detail and summary views after the form is saved.

If you wish the list of columns, then I believe one option is to build a somewhat long LIST() type column with base type as “Text” with individual column based IFS()statements

LIST ( IFS( [Are trucks, cars & trailers parked to reduce impact on road users and pedestrians] = “No”, " trucks, cars & trailers IMPROPERLY parked"), IFS([Containers correctly marked] = “No”, “Containers INCORRECTLY marked”)…IFS([Inspection Status]=“Satisfactory”, “None of the items requires attention”) )

You could end the above list with the existing last column that you have mentioned. If the current last column is called say [inepsection Status], then last IFS() in the above list column could be IFS([Inspection Status]=“Satisfactory”, “None of the items requires attention”) , This can work as a default value fot the “items requiring attention” column.

Also the forms have provision to assign numbers to the columns. So to shorten the “items requiring attention” list, one could use the question number of the form in IFS() statements. such as
IFS( [Are trucks, cars & trailers parked to reduce impact on road users and pedestrians] = “No”, " Question 3")

There may be some other better options possible, that community members may suggest.

Edit: To remove blank elements from the above composed list you could subtract a blank element list at the end. Something like below highlighted.

LIST ( IFS( [Are trucks, cars & trailers parked to reduce impact on road users and pedestrians] = “No”, " trucks, cars & trailers IMPROPERLY parked"), IFS([Containers correctly marked] = “No”, “Containers INCORRECTLY marked”)… IFS([Inspection Status]=“Satisfactory”, “None of the items requires attention” ) ) -LIST("")

[Steve]

Items requiring attention column App formula expression:

(
  LIST(
    IFS(condition1 = No, "message1"),
    IFS(condition2 = No, "message2"),
    ...
  )
  - LIST("")
)

[SKETCHwade · Accepted solution]

Thanks @Steve,
It got me on the right track.

I ended up doing this on a long text column, its concatenating the name of the column if it is a no and makes a new line so they arent connected to each other:

CONCATENATE(
IF([Are trucks, cars & trailers parked to reduce impact on road users and pedestrians] = No, “Are trucks, cars & trailers parked to reduce impact on road users and pedestrians
“,””),
…
IF([Gas bottles stored correctly - vertically/chained/secure] = No, “Gas bottles stored incorrectly
“,””)
)

My concern here is that I am doing this IF statement for 50 columns. it’s not particularly complex but it must take a toll on performance no?

[Steve]

SKETCHwade:

My concern here is that I am doing this IF statement for 50 columns . it’s not particularly complex but it must take a toll on performance no?

If it’s a virtual column, there will be an increasing effect on sync time as the number of rows in the table grows. An alternative would be to use a normal (non-virtual) column instead, so the expression is only evaluated in a form or when the row is updated by an action. This eliminates sync-time effects, but requires space in your spreadsheet file.