LinkedIn
Twitter
Context: I am building an app for field technicians to conduct a site survey of places they work. The app is essentially a questionnaire with about 50 Yes/No questions ("Was site clean?" "Was PPE available?" "Was there a fire extinguisher?" etc). I have made each question its own column. A little unwieldly (especially on the backend), but AppSheet can handle it well enough with page breaks and stuff.
We would like the app to trigger actions if the answer to any question is No. Ideally, we want to create a Followup record that is a child of the Inspection row that references the question, the follow up action taken, etc.
Problem: While it is easy to force the user to manually create a child and manually choose the question to follow-up on, I am hoping to automate this a little and have a child row created for every No answer. For this to be usable, I want the automation to populate the Question as a value in the Followup row. The problem is that the Question is a Column Name, not a value in the row.
I have a mapping table that gives each question a QuestionID, but I don't see a way to get any expression/action/automation to read a column name and match up to the value in another table (Image below that hopefully shows what I mean). Like, check for a No value and then return the name of that column, or, select a list of columns from a row that have a value of No.
Even if I can't autopopulate a row in the Followup table based on a No answer, I would love for the user to be able to at least select from a list pre-filtered to only the questions they answered No on if they have to manually create a followup row.
Now, I am my department's himbo, so I can accept if the answer is "This is a really dumb way to do this, and also not possible." I'm just wondering if there's some way to meaningfully use the answer to a question (the value in a column) to record that question in a row elsewhere.
Obviously, it seems like the smarter play is to have a parent Inspection table that has no questions and a child table that is connects an InspectionID to a QuestionID to an Answer value but I don't know how to recreate a simple questionnaire format that would iterate through every question in the Question Table to create the child rows (this would also make reporting pretty whack I have to imagine, although the way I have it now is going to look like a mess if anyone tries to export).
Hope all that makes sense! Am I approaching the problem wrong? Is there any hope for this beautiful meathead of a designer??
Solved
1 ACCEPTED SOLUTION
I have built (still building) an Inspection app recently, something like yours. Here is an example of what it looks like:
Just to get straight to the point, from an app perspective, it will cause you all kind of headaches to list your questions as columns. Don't do it! Instead start off with a table like you show at the end with Question ID and QuestionText.
I also recommend having a parent Inspection table and a child Inspection Results table where the questions are COPIED for the specific inspection. Each inspection needs to have its own set of answered questions AND you need to be able to "remember" which questions were part of the Inspection at the time it was completed.
This row based approach will also allow you build a way to manage the Inspections through the app itself for things such as amending questions or adding/removing questions. You can't do that in a column-based approach without physically changing the app tables and redeploying the app.
Good luck! Feel free to reach out with questions
