Display multiple column values from a single row that have the same value

[MCICS2000]

I am wrapping up an inspection app and a customer records app. For the inspection form (annual inspection table), each inspection item answer is referenced to the inspection status table. Since there are about 100 inspection items, it was much easier and faster to use the ref to the inspection status table rather than type in each option 100 times in an enum field. 

That gets me to the issue I am trying to solve. We want to show each inspection item for an individual inspection that has the answer of 'service recommended'. In the table, when the inspection is completed, each row shows an inspection, and each inspection item has one of four answers, with service recommended being one of them. I have tried various list functions without any success in showing the summary desired. 

Any thoughts would be greatly appreciated. 

[AleksiAlkio]

Would it be lighter solution to show the "answer' with a format rule?

[MCICS2000]

I should have mentioned that the inspection will be turned into a PDF, which will have a summary page, which is why we want the service recommended answer summary. So a format rule wouldn't help with that. 

[AleksiAlkio]

Your table/column structure is a little bit mystery. Is the Inspection the parent and Inspection Items a child?

[MCICS2000]

The inspection (with the inspection items) would be the child and the inspection int answer (four possible answers for each of the 100 or so inspection items in the inspection) would be the parent. I built it this way because it was the easiest way to add the same dropdown answer for each of the inspection items. That said, if there is a better way to quickly add enum items that would also help with my original issue, I’m open to changes. 

[AleksiAlkio]

Let me try to summarize what you have.. and correct if needed.
#1 - You have an "Inspection" table and it contains 100 columns (Inspections items)
#2 - Every "Inspection Items" columns are Ref types with four different options (you probably have some other column types as well, but that doesn't matter)
#3 -  You are reading these four different options from the "Inspection status" table
#4 - You wrote: We want to show each inspection item for an individual inspection that has the answer of "service recommended". Do you mean that you would like to have a list of "column names" where the status is "service recommended"? So the list should show something like.. "Fire door", "Smoke detector", etc.?

[MCICS2000]

Yes, that is all correct. 

[AleksiAlkio · Accepted solution]

With your structure, the formula would be very long. Something like IFS([Item_1]="Service recommended","Item_1, ")&IFS([Item_2]="Service recommended","Item_2, ")&etc..

[MCICS2000]

Ok, that would make sense as to why the expressions I was trying were not working. Sounds like it is not worth going this route. 

[AleksiAlkio]

If you would have one inspection Item in each row, the formula would be much simpler. That's doable as well, but it depends on if it's a good approach in your case.