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.
Solved! Go to 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?
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("")
Items requiring attention column App formula expression:
(
LIST(
IFS(condition1 = No, "message1"),
IFS(condition2 = No, "message2"),
...
)
- LIST("")
)
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?
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.
User | Count |
---|---|
39 | |
28 | |
24 | |
23 | |
13 |