Building a 50 Question Questionnaire: can column names be returned as values??

[PhilTheThrill]

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?? 

sample of columns in one table being rows in another

[WillowMobileSys · 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

[PhilTheThrill]

Oh wow, that's a clever way to do that and solves the problem I was having with the user experience of dealing with child rows instead of columns. Thank you for the inspiration and advice!

[PhilTheThrill]

Actually, one question: are you having AppSheet create a child row for every question when the Inspection is initially created? Like, they hit "Create New Inspection" and it autopopulates all the child rows and then they just scroll down and answer as needed?

[WillowMobileSys]

Yes, basically that is what I am doing.  In my case I actually have a Template table where the list of questions are chosen as child rows.  I could have several templates.  When the "Create Inspection" happens, a template is chosen along with other details including location.  Upon Save,  the Template Questions are used to pre-populate a set of Inspection Results rows - one for each question - with the question result initialized to some default.

[MultiTech]

I've got a sample app that will show you the basics of this technique
   - which I call "Blueprint Tables"

• I've got a sample app you can check out
• And video showing how it was put together

This technique is one of the most common things you'll find yourself doing on AppSheet; nearly every advanced app I build has some sort of this technique going on.

Another example of this, just to give you further perspective on how this technique can be utilized, you can check out my Shopping Cart evolution series; the customer products version is basically implementation of this technique, but I never commented on this.

Cheers!  (^_^)

[MultiTech]

---

@WillowMobileSys wrote:

pre-populate a set of Inspection Results rows - one for each question - with the question result initialized to some default.

---

Instead of creating the records, then and there, why not create the records when they're actually made by the person completing the individual task (or whatever you call it)?

• The way I see things: a person is going to interact with every single record no matter what
  • So why do two things here, when you could do one? 
  • Why create the record, then have the user edit the record?
  • Why not create the record, when the user needs to complete whatever record they're completing, and only have that one event in the system?

There's a bunch of reasons why I've settled on this technique vs. creating the records up front, but just to name a few:

1. You can easily capture the timestamp of when things happen
     - Literally just "NOW()" or whatever as the initial value, no complications
2. You can base advanced functionality off the presence of completed records
     - Or the count of the completed (or remaining) items
3. Having a list of "remaining items" become much simpler
     - Take the [master list] minus [a derivative list of completed items] and you get what's left

It does bring in a few "complications" for the app builder, but they're easily solved with standard build practices.

• You have to hold the parent record being completed by the user in a global slice
    - This allows you to hot-link the parent ref value
• You have to create a "Remaining Items" VC on the parent level

But these honestly turn out to be benefits later on

[WillowMobileSys]

---

@MultiTech wrote:

The way I see things: a person is going to interact with every single record no matter what

---

In my case, the users don't interact with every question.  In fact, for many questions, the default answer is sufficient and no other interaction is necessary BUT every question is required for a generated PDF report.  

[MultiTech]

Makes sense.  I was just throwing this out there for consideration.

When I started building things like this, my first inclination was to create the records. In fact this is what everybody, meaning my clients, was really looking for.

But in practice it actually kind of got in the way. We either had to create all of the items client side, and since there was so many it would cause the device to freeze for a few moments, or make them through automation, which meant we had to wait for a sync cycle to finish. 

What everyone was really wanting, I later came to find out, was to be able to see a list of the remaining things to be done. (This was for an inspection type of system, where every item needs to be completed on the list - and then be able to complete that list over and over again.)

That's when I settled on showing the items, instead of creating them.

But yeah, if most of the default answers are what you need - it makes sense to do it that way.

----------------------------

I'm curious: are you creating the records client side or server side?

• How many do you make?
• You run into any kind of peculiarities or difficulties? 

[WillowMobileSys]

---

@MultiTech wrote:

I'm curious: are you creating the records client side or server side?

• How many do you make?
• You run into any kind of peculiarities or difficulties?

---

I'm an equal opportunity programmer!  😁

It depends on the needs of the app. 

In the Inspection app I was referring to above, I use a Form Save action on the client side.  A user will create the Inspection, choose template (+ other details) and Save.  I am finding that the creation on the client side is fast enough that by the time they tap on the Inspection row, the Inspection Result records are there to interact with.  The number of rows hasn't exceeded 50 yet and likely will not anytime soon.

In other apps, when the pre-generated rows are not needed right away, I use automation to create them.

In one instance, I needed to pre-generate rows in about 6 different tables not needed immediately.  I resorted to automation using the API so the rows were generated swiftly in bulk.  Large occurrences, hundreds of rows, was reduced from 20 min to about 5-6 min.

There two main difficulties associated with these techniques I had to overcome:

1)  There is no true "bulk" processing using actions. Multiple rows generated in this manner are produced one-by-one.  This is slow and inefficient because of the overhead needed to cycle through each iteration.

2)  Because of 1), I used the API for bulk entry (as mentioned above).  The issue I found here is that each API step does not immediately know of activity happening in the step before.  I.e. new rows, and I imagine row updates, would not be available to reference in that next step.

[MultiTech]

---

@WillowMobileSys wrote:

1)  There is no true "bulk" processing using actions. Multiple rows generated in this manner are produced one-by-one.  This is slow and inefficient because of the overhead needed to cycle through each iteration.

---

I'm curious, how did you set this up?

• With action looping?
• Or with an action on the "template" side of things to create the copy?

[WillowMobileSys]

I've used both.

For the Inspection app, it was an action using a list of Template Questions with a single one-to-one correspondence to create the Inspection Results rows.

In many other cases I have used the action looping.  Typically, it has been with a user selected EnumList column as the looping "control".  On Save walk through the EnumList items, saving a new child table row for each item.  

[PhilTheThrill]

Just wanted to say thank you to everyone for the great discussion and inspiration and examples. This is WAY better than the old version I had and so much more customizable and extendable. I'm still playing around with look and feel and stuff, but this is such a better direction.

[MultiTech]

---

@PhilTheThrill wrote:

---

That's a good looking list of questions!