Randomize Columns or Questions or create Question pool

[Work_Goscinski]

I am working on an inspection app. My company produces over 300 parts. To perform a certain type of audit on the processes used to create those parts, I would like to do 2 things:

1. Randomize the questions for the auditor. For instance, “Check parts for burr.” and tomorrow “Check parts for bad paint.” Basically pull questions from a pool of questions.
2. Certain questions that would fade out over time. I work in the auto industry. If we find a defect (“Bad Paint”), we want to track our process over 30 days to make sure that our updated procedure prevents recurrence of the defect. So is there a way to make this question ask for the given time period and then be randomized again?

If these are possible, some points in the right direction to find the answer.

Thanks,

[Rich_E]

Yes this is certainly possible, sounds like an awesome process! You will need 3 tables, one for tracking assets, one for your question pool and one for tracking the individual checklist items for each asset. The tracking table will be a child table to the asset table.

The question pool table will contain all of the questions that could ever be displayed. In that table you could have 1 date column (to track whether a question will be tracked over 30 days) and 1 virtual column. The date column that will populate today’s date when the question starts a 30 day cycle. The virtual column will be a yes or no column with this formula:

if ( and([DateColumn]>today()-30), isnotblank([DateColumn])), true, if(randbetween(0,1)=1, true, false))

This virtual column acts as a flag so your app can query the questions when it generates a list for the inspection. It will set all questions that are being tracked for 30 days as true and then randomly pick questions from the rest of the list.

Next you would have a asset table that will track the status of the overall inspection for each asset. When a user views the details of an asset, they can chose to “start inspection” by clicking a button “Start Inspection”. This will start a grouped action with these components:

1. Set asset status to “generating checklist”

2. An action that is “execute an action on a set of rows” with a formula of this action would look something like this.

select(questionpool[ID], [virtualcolumn]=true)

This action will reference an action in the question pool table to copy records to a tracking table that will act as a child table to the asset table. The column used as a reference back to the asset table will be populated with a formula that performs a lookup of any asset that is of status “generating checklist”.

3. Set asset status to “started Inspection”

Hopefully that helps. Let me know if you run into any roadblocks.

[Work_Goscinski]

I will keep you posted. Thanks for the pointers.

[Work_Goscinski]

May I ask more questions of you concerning this same question?

I did some things slightly different than you suggested above. I may be offbase. Partly because I wasn’t sure how to start with your suggestions above. I get the “theory” of what you mean, but not sure exactly how to build it in a spreadsheet to convert to appsheet.

[Work_Goscinski]

I ran into a few roadblocks with your solution above, especially your first expression. I get an error that says 2 expression(s) needed.

Let me start fresh with the question…

[Work_Goscinski]

Here is what I currently did. The scope of the original question has changed, so I need to change my train of thought. Tell me if I am not on the right track.

I have 4 tables. I created the set of questions in Google Forms.
Table 1: Form Responses (Contains the answers)
Table 2: Part Numbers (we need to track the questions per part number, not asset, like I originally thought)
Table 3: Checklist Pool (all checklist questions that could be asked, broken up into categories)
Table 4: Template Pool (all template questions that could be asked, broken up into categories)

What I envision is that

is filled out first. Then go to the template or checklist screen.

What I would like to see is “Communication Random 1”, “Communication Random 2” and so on replaced with questions from the template pool. ![image|690x190] and they answer Yes, No, or N/A

The reaction plan will be triggered whenever a person answers “No” to a question.

Is this possible as I have made it, or do I need to start over with more of what you described above? If I need to start over, that is where I am not sure where to start. I know how to make a google sheet. I am just not sure how to pre-fill the 3 rows of data to import it into appsheet. I get your theory of how things are connected, but can’t see how to start. (I tried to make another app using your template above, but I don’t think I have it structured right.)

[Rich_E]

The problem with the current setup is that those questions are individual columns and you cannot dynamically change these column names.

What is the difference between the template and checklist questions?

I would recommend you stick with my above recommendation as it allows you to scale easily. Have your question bank table, a table that lists each unique part and then a table that will track your responses to the questions that are generated. The response table needs to have a column that holds the part ID that is a REF back to the part table.

[LeventK]

@Rich
I’m building a demo app for @Work_Goscinski. His current sheet and table set up is completely contradictory what he’s trying to achieve unfortunately. I’ll show him the correct path and he will build the rest up onto that demo app.

[Work_Goscinski]

Well, I appreciate all the help and advice from all involved.