Here is a portion of my inspection form:
The header for each section eg Fire Suppression system?
determines wether or not the section is required/shown.
I am trying to write an expression for the Result that will do the following:
For Fire Suppression system
Date within 6 months?
Pressure good?
Visual condition good?
For External Fire Extinguisher
Date within 6 months?
Pressure good?
Visual condition good?
For Internal Fire Extinguisher
Date within 6 months?
Pressure good?
Visual condition good?
Result = Pass
If a section is not shown because it is not required then its result is not relevant to the final Result
I hope this makes sense. Thank you
Solved! Go to Solution.
Hi @SKETCHwade,
You have extended the same logic over other subcategories. That is good however i believe it needs tweaking as below, if I have understood your requirement correctly.
Please try following expression. It assumes that if each of the subcategories, namely [Fire Suppresion System?], [External Fire Extinguisher?] and [Internal Fire Extinguisher?]and their questions result in to respective subcategory being pass, then overall result is โPassโ . If any one of those subcategories fail , the overall result is โFailโ
IF(AND(
IFS(
[Fire Suppresion System?] = FALSE, โPassโ,
AND([Fire Suppresion System?] = TRUE,
[Fire Suppresion Date]>Today()-182,
[Fire Suppresion Pressure] = โGoodโ,
[Fire Suppresion Visual Condition] = โGoodโ),
โPassโ,
AND([Fire Suppresion System?] = TRUE,
OR([Fire Suppresion Date] <=Today()-182,
[Fire Suppresion Pressure] = โHighโ,
[Fire Suppresion Pressure] = โLowโ,
[Fire Suppresion Visual Condition] =โReplaceโ)
),โFailโ)=โPassโ,
IFS(
[External Fire Extinguisher?] = FALSE, โPassโ,
AND([External Fire Extinguisher?] = TRUE,
[External Fire Extinguisher Date]>Today()-182,
[External Fire Extinguisher Pressure] = โGoodโ,
[External Fire Extinguisher Visual Condition] = โGoodโ),
โPassโ,
AND([External Fire Extinguisher?] = TRUE,
OR([External Fire Extinguisher Date] <=Today()-182,
[External Fire Extinguisher Pressure] = โHighโ,
[External Fire Extinguisher Pressure] = โLowโ,
[External Fire Extinguisher Visual Condition] =โReplaceโ)
),โFailโ)=โPassโ,
IFS(
[Internal Fire Extinguisher?] = FALSE, โPassโ,
AND([Internal Fire Extinguisher?] = TRUE,
[Internal Fire Extinguisher Date]>Today()-182,
[Internal Fire Extinguisher Pressure] = โGoodโ,
[Internal Fire Extinguisher Visual Condition] = โGoodโ),
โPassโ,
AND([Internal Fire Extinguisher?] = TRUE,
OR([Internal Fire Extinguisher Date] <=Today()-182,
[Internal Fire Extinguisher Pressure] = โHighโ,
[Internal Fire Extinguisher Pressure] = โLowโ,
[Internal Fire Extinguisher Visual Condition] =โReplaceโ)
),โFailโ
)=โPassโ),โPassโ,โFailโ)
Not to me.
Sorry @Steve
Is there any section in particular that does not make sense?
Or should I try to rewrite this?
This may help further explain my intentions:
IF([Fire Suppression System?] = Yes,
IF([Fire Suppression Date] > Today()-182,Pass,Fail)
IF([Fire Suppression Pressure] = Good,Pass,Fail)
IF([Fire Suppression Visual Condition] = Good,Pass,Fail)
,Fail)
You can see here the overarching Question is does the [Fire Suppression System?] exist?
If it does not then ignore the section about the fire suppression system.
If it does exist then do its three following columns pass the criteria?
The Result column should display pass if the Fire Suppression System? column is no (It does not exist so should not affect the result for the rest of the form)
Or if the columns meet the criteria. Otherwise the Result column should display Fail.
Note the code I used above for this example does not work the way I want it to. It displays fail if you select No on the initial column and sometimes displays pass if you make some columns fail the criteria.
I believe you may try following
IFS(
[Fire Suppression System?] = FALSE, โPassโ,
AND([Fire Suppression System?] = TRUE, [Fire Suppression Date]>Today()-182, [Fire Suppression Pressure] = โGoodโ, [Fire Suppression Visual Condition] = โGoodโ), โPassโ,
AND([Fire Suppression System?] = TRUE, OR([Fire Suppression Date]<=Today()-182, [Fire Suppression Pressure] = โHighโ, [Fire Suppression Pressure] = โLowโ, [Fire Suppression Visual Condition] =โReplaceโ) ), โFailโ
)
Edit: : I also believe that for all these check conditions, you may wish to have some proper initial values for all these inspection fields in consistent manner for either โFailโ logic or "Pass"logic.
So [Fire Suppression Pressure], [Fire Suppression Visual Condition] etc. may all have initial values that satisfy โPassโ logic or values that satisfy โFailโ logic. This will ensure that the expressions do not fail because of โundefinedโ values for these fileds.
Thank you @Suvrutt_Gurjar
This helped me get a lot closer I think.
Here is what I have:
IFS(
[Fire Suppresion System?] = FALSE, โPassโ,
AND([Fire Suppresion System?] = TRUE, [Fire Suppresion Date]>Today()-182, [Fire Suppresion Pressure] = โGoodโ, [Fire Suppresion Visual Condition] = โGoodโ), โPassโ, AND([Fire Suppresion System?] = TRUE, OR([Fire Suppresion Date] <=Today()-182, [Fire Suppresion Pressure] = โHighโ, [Fire Suppresion Pressure] = โLowโ, [Fire Suppresion Visual Condition] =โReplaceโ) ),โFailโ,
[External Fire Extinguisher?] = FALSE, โPassโ,
AND([External Fire Extinguisher?] = TRUE, [External Fire Extinguisher Date]>Today()-182, [External Fire Extinguisher Pressure] = โGoodโ, [External Fire Extinguisher Visual Condition] = โGoodโ), โPassโ, AND([External Fire Extinguisher?] = TRUE, OR([External Fire Extinguisher Date] <=Today()-182, [External Fire Extinguisher Pressure] = โHighโ, [External Fire Extinguisher Pressure] = โLowโ, [External Fire Extinguisher Visual Condition] =โReplaceโ) ),โFailโ,
[Internal Fire Extinguisher?] = FALSE, โPassโ,
AND([Internal Fire Extinguisher?] = TRUE, [Internal Fire Extinguisher Date]>Today()-182, [Internal Fire Extinguisher Pressure] = โGoodโ, [Internal Fire Extinguisher Visual Condition] = โGoodโ), โPassโ, AND([Internal Fire Extinguisher?] = TRUE, OR([Internal Fire Extinguisher Date] <=Today()-182, [Internal Fire Extinguisher Pressure] = โHighโ, [Internal Fire Extinguisher Pressure] = โLowโ, [Internal Fire Extinguisher Visual Condition] =โReplaceโ) ),โFailโ
)
It looks pretty long and frightening but Its really just an extended version of what you presented.
My issue is that due to the way IFS work getting a pass by selecting no on the column [Fire Suppression System?] means the result is essentially locked as pass even though the internal fire extinguisher may have failed for example.
Any ideas?
Hi @SKETCHwade,
You have extended the same logic over other subcategories. That is good however i believe it needs tweaking as below, if I have understood your requirement correctly.
Please try following expression. It assumes that if each of the subcategories, namely [Fire Suppresion System?], [External Fire Extinguisher?] and [Internal Fire Extinguisher?]and their questions result in to respective subcategory being pass, then overall result is โPassโ . If any one of those subcategories fail , the overall result is โFailโ
IF(AND(
IFS(
[Fire Suppresion System?] = FALSE, โPassโ,
AND([Fire Suppresion System?] = TRUE,
[Fire Suppresion Date]>Today()-182,
[Fire Suppresion Pressure] = โGoodโ,
[Fire Suppresion Visual Condition] = โGoodโ),
โPassโ,
AND([Fire Suppresion System?] = TRUE,
OR([Fire Suppresion Date] <=Today()-182,
[Fire Suppresion Pressure] = โHighโ,
[Fire Suppresion Pressure] = โLowโ,
[Fire Suppresion Visual Condition] =โReplaceโ)
),โFailโ)=โPassโ,
IFS(
[External Fire Extinguisher?] = FALSE, โPassโ,
AND([External Fire Extinguisher?] = TRUE,
[External Fire Extinguisher Date]>Today()-182,
[External Fire Extinguisher Pressure] = โGoodโ,
[External Fire Extinguisher Visual Condition] = โGoodโ),
โPassโ,
AND([External Fire Extinguisher?] = TRUE,
OR([External Fire Extinguisher Date] <=Today()-182,
[External Fire Extinguisher Pressure] = โHighโ,
[External Fire Extinguisher Pressure] = โLowโ,
[External Fire Extinguisher Visual Condition] =โReplaceโ)
),โFailโ)=โPassโ,
IFS(
[Internal Fire Extinguisher?] = FALSE, โPassโ,
AND([Internal Fire Extinguisher?] = TRUE,
[Internal Fire Extinguisher Date]>Today()-182,
[Internal Fire Extinguisher Pressure] = โGoodโ,
[Internal Fire Extinguisher Visual Condition] = โGoodโ),
โPassโ,
AND([Internal Fire Extinguisher?] = TRUE,
OR([Internal Fire Extinguisher Date] <=Today()-182,
[Internal Fire Extinguisher Pressure] = โHighโ,
[Internal Fire Extinguisher Pressure] = โLowโ,
[Internal Fire Extinguisher Visual Condition] =โReplaceโ)
),โFailโ
)=โPassโ),โPassโ,โFailโ)
Thank you @Suvrutt_Gurjar
That is perfect!!
Your explanation for my intention was also spot on:
Please try following expression. It assumes that if each of the subcategories, namely [Fire Suppresion System?], [External Fire Extinguisher?] and [Internal Fire Extinguisher?]and their questions result in to respective subcategory being pass, then overall result is โPassโ . If any one of those subcategories fail , the overall result is โFailโ
I really appreciate it @Suvrutt_Gurjar !
Thank you for the update @SKETCHwade. You are welcome and good to know it helps.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |