Show status based on user answers to most recent inspection

[Chris_Bean1]

Morning folks

I am trying to show the status of an item listed in one tab based on entries recorded by users in another tab. I have a list of skips used for transport in a master data tab, and skip inspections completed by users in another tab. The skip inspections are yes/no questions, and a series of 'TRUE' for those questions mean the skip is good to go. 

I would like the status of the skip in the master data tab to be determined by the most recent skip inspection. If there is a 'FALSE' in any of the user answers, it should show 'defected', if it's all 'TRUE' then 'ok'. 

I have this in the App Formula of the column editor:

if(max((Skip inspections[date]).[skip number]),

(Skip inspections[Door hinges intact?]=true,

Skip inspections[Bottom bar moving easily and closing door correctly?]=true,

Skip inspections[Guide rails free of damage?]=true,

Skip inspections[Are the welding joints intact, showing no cracks?],

Skip inspections[Is the lifting bar secure, showing no cracks?]=true,

Skip inspections[Are the walls in good repair?]=true,

Skip inspections[Are all rear door twist locks present and securing to the door correctly?]=true,

Skip inspections[Is the sheet centre pole intact and straight?]=true,

Skip inspections[Sheeting system intact, no holes?],=true,

Skip inspections[Are the straps that secure the sheet present and correct?]=true,

Skip inspections[Is the ratchet securing device present and working well?]=true),

"ok", "defected")

As ever, any help appreciated

Cheers

Chris

[Chris_Bean1]

In the app editor I get the following error with the formula I put in:

Cannot compare List with Yes/No in (Skip inspections[Door hinges intact?] = "true")

[JPAlpano]

This is because Skip inspections[Door hinges intact?] will return you all the rows  of the column [Door hinges intact?].  Hence, it is telling you that a List cannot be true/false or yes/no.

You need to specify which specific row in Skip inspections table you want to compare ="true".
You may need to use SELECT() expression or LOOKUP() to do this.

ex:  SELECT(Skip inspections[Door hinges intact?], [Column Name in S.I. table]="123") = true

[Chris_Bean1]

Hmm, I thought that was what the max part of the formula was doing, ie find me the latest check against thus skip number (max against the date) then if all OK, show OK, if not show defected...

I'll try again, thank you

Chris

Get Outlook for Android<>