Single row to multiple rows

Development and integrations Scripting app
#1

I thought I was getting the hang of this Airtable thing, but I’m stuck on this problem.
I’m trying to separate multiple columns from a single row into multiple rows in another table:

Single audits are conducted using the fields in the ‘Safety Audits’ table. In the example there are 3 questions (Q1-3). If the answer to these is ‘NO’, the user is asked if the need to ‘Report A Fault’ (YES/NO). If yes, then they enter the details (Location, subject, photo, etc.).

I think I need a script to populate table ‘All Faults’? Any assistance would be greatly appreciated as I have exhausted all of my formula knowledge.

I appreciate this seems quite long winded, but the audit questions are part of our compliance records, so each question needs answered (the real audit has about 12 questions). The faults are a bi-product of the audits, and need to be treated separately, hence the separate table. I’ll then have another view within ‘Safety Audits’ to show a summary of all audits completed, without the fault details.

Thanks, Andy

#2

Regarding binary validation, you could simply make a control field containing answers to all your yes/no questions. Its formula would be something like:

{YesNoQuestionField1} & {YesNoQuestionField2} & {YesNoQuestionField3} & ""

Notice the quotation marks at the end, those will turn that field into a string and leave you with something in the vein of 1101/0000/1101, depending on the yes-no answers.

Then create another field (you can join it with the above one during the cleanup phase) and have it contain instructions/act as a trigger for what you want to do, based on the contents of the aforementioned summary.

I’d usually go with a Switch statement there. Something in the vein of:

SWITCH({ControlFieldName},"0000","Why so negative?", "0101", "You scored 50% on the positivity test", "1111", "Why, aren't you eager.", "")