Formula for Display Name? to randomize questions for auditing

[Work_Goscinski]

Ultimately, I am creating an audit app that randomizes questions. The critical data are part number (have a table for that), a list of questions categorized (a table for that. Question #, Category, and question as columns), and the main check sheet where the questions are answered (a table for that).

I have a table with column labels “Random Communication 1” and “Random Communication 2” and “Random PPE 1” and so on.

What I would like to do is create a formula that selects a question from a pool of questions pertaining to the subject of Communication, PPE, and others. Those questions are in a different table. All questions are answered Yes, No, N/A.

Is a way to accomplish this writing a formula for Display Name that selects a question from the pool? If so, what are some good resources to begin?

[LeventK (Levent Kulacoglu)]

ANY(
    SELECT(
        QuestionTable[QuetionColumn],
        RANDBETWEEN(2, MAX(QuestionTable[_RowNumber])) = [_RowNumber]
    )
)

INDEX(
    SELECT(
        QuestionTable[QuestionColumn],
        {Y/N Criteria if availabel or TRUE only}
    ),
    RANDBETWEEN(1, COUNT(SELECT(QuestionTable[QuestionColumn],TRUE)))
)

[Work_Goscinski]

@LeventK

SELECT(
        QuestionTable[QuetionColumn],
        RANDBETWEEN(2, MAX(QuestionTable[_RowNumber]) = [_RowNumber]
)

The first method gives me a "Parameter 2 of function RANDBETWEEN is of the wrong type.

[Work_Goscinski]

@LeventK

INDEX(
    SELECT(
        QuestionTable[QuestionColumn],
        {Y/N Criteria if availabel or TRUE only}
    ),
    RANDBETWEEN(1, COUNT(SELECT(QuestionTable[QuestionColumn],TRUE)))
)

The second one says SELECT has invalid inputs

[Work_Goscinski]

@LeventK

SELECT(
        QuestionTable[QuetionColumn],
        RANDBETWEEN(2, MAX(QuestionTable[_RowNumber]) = [_RowNumber]
)

Writing it all as 1 formula, I get Parameter 2 of function RANDBETWEEN is of the wrong type

[Work_Goscinski]

@LeventK

MAX(QuestionTable[_RowNumber]) = [_RowNumber]

[LeventK (Levent Kulacoglu)]

@Work_Goscinski
It’s my bad…I believe there might be missing paranthesises…For the first one, try with this:

ANY(
    SELECT(
        QuestionTable[QuetionColumn],
        RANDBETWEEN(2, MAX(QuestionTable[_RowNumber])) = [_RowNumber]
    ) 
)

How have you constructed the second one? Can you post a screenshot?

[Work_Goscinski]

@LeventK

SELECT(
        QuestionTable[QuetionColumn],
        RANDBETWEEN(2, MAX(QuestionTable[_RowNumber])) = [_RowNumber]
) 

Does the 2nd one go underneath the first?
I am not sure if this is all 1 large formula or 2 different ones.

[LeventK (Levent Kulacoglu)]

@Work_Goscinski
I have proposed 2 different options for you. Both provide the similar result.

[Work_Goscinski]

I understand now. The first one works. I put the formula in Display Name and Description.

However, when I click Y or N, the question changes. Even if I click the 2nd question.

I will try the 2nd solution.

[LeventK (Levent Kulacoglu)]

@Work_Goscinski

I understand now. The first one works. I put the formula in Display Name and Description.

You need to use either the Display Name or Description, not both of’em.

[Work_Goscinski]

If I do it as display name, the question does not display.

[Work_Goscinski]

All I did was click on No under the first question. The questions changed.

[Work_Goscinski]

Another issue: The answer is recorded, but not the question that was chosen.

Hmm. I would need both recorded: question and selected answer. Maybe this is the wrong method?

Or Maybe I have to create 2 columns for each question? Like, the text field gets populated with the question. The next column records answer.

What I did was autocompute Communication 1 with the formula you wrote. Then I made Communication 2 as the answer.

This records the question AND answer.

[Work_Goscinski]

Next question: If I have 8 Categories (1-8) and differing number of questions for each category. So I have 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, 2.4, 3.1 (are the labels for each question. the 1st digit is category and 2nd is question in the category. does that make sense?)

So I would like to randomize each category. So the first questions would randomly select between 1.1 and 1.3. The next set would randomize between 2.1 and 2.4. I need to group the questions by category not just from an entire pool. (The pool question was the start to see that I was on the right track.)

I figure some alteration of SELECT(
QuestionTable[QuetionColumn],
RANDBETWEEN(2, MAX(QuestionTable[_RowNumber])) = [_RowNumber]
) is where to start.

Or I create a table for each category.

[LeventK (Levent Kulacoglu)]

@Work_Goscinski

Hmm. I would need both recorded: question and selected answer. Maybe this is the wrong method?

The question is your column (*though the display name value is a lookup and actual sheet header could be Q1, Q2 etc.) and recorded values are the row values. I’m a bit confused what you are trying to do actually. Why do you want to record the question to the sheet? Is there a meaning?

[LeventK (Levent Kulacoglu)]

@Work_Goscinski

Next question: If I have 8 Categories (1-8) and differing number of questions for each category. So I have 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, 2.4, 3.1 (are the labels for each question. the 1st digit is category and 2nd is question in the category. does that make sense?)

So I would like to randomize each category. So the first questions would randomly select between 1.1 and 1.3. The next set would randomize between 2.1 and 2.4. I need to group the questions by category not just from an entire pool. (The pool question was the start to see that I was on the right track.)

How is your sheet structured for these categories? Can you give a screenshot if possible?

[Work_Goscinski]

Great questions.

I work in the automotive industry. We have to conduct Layered Process Audits. Simply put, we do audits at various steps in the production of a piece. In theory, there can be generic questions asked and specific questions asked. The generic questions can be asked of all parts we produce. Specific questions, however, are individualized for each part (of which we make hundreds).

Every audit, which is done daily, should not contain hundreds of questions to ask, thus why I want them randomized. There needs to be a certain number of questions asked per category–let’s say 2. But, if my pool for that category contains 8 questions, I have to know which question was asked. The answer choices are always the same “Yes” “No” “N/A”. The idea is so that our auditors and supervisors don’t get into a “rut” always asking the same questions AND not looking for other issues. “I didn’t know to look for that, you never asked.”

Does that help explain the Why a little better?

I am going to provide the structure of the question pool via a screenshot in a second. Let me explain a little of what you are about to see.

In addition to randomizing questions, I need to be able to track anything that gets marked as a “No.” (Column H) That means we did something wrong and that question for that part should be asked again every day for the next 30 days. We want to ensure that we have truly corrected the problem. (Column G will record when the part was last audited no matter the answer.)

In addition, a “No” answer will trigger a Reaction Plan specific for that question. (Column F). What I need to exactly do with that column? I don’t know at this time. It is incomplete with data anyway. But it will be incorporated at a later date.

I wasn’t sure if that was the best table of my spreadsheet to capture the dates, so I also included it on the Parts table. Because I will want to know the last time a question was asked AND the part. So I may actually have to concatenate the Part and Question and Answer to best track the date. (Here is the data structure as it exists right now, however.)

Structure for main app page:

I hope that provides a clearer picture of what I am looking to do.

Thank you. I appreciate the help.

[LeventK (Levent Kulacoglu)]

@Work_Goscinski
I’ve understood the issue quite well and I can say that your initial database set-up is quite wrong for your purpose. For the best effective result, we need to set-up the database very well, so that you can reach any required data very quickly may be with a couple of taps. So provided it suits you, I will quickly construct a demo app for you just to show how you should handle those audits. I’m pretty much sure you can grab the proof of concept there and apply (and if needed re-structure) to your app. Provided it’s not too much to ask, can you only share the questions table only with me? Following your sharing, I will be needing a couple of days from you. You are free to contact me and share any file you see fit with levent@able3ventures.com. Thanks.

[Work_Goscinski]

I appreciate your input. I have added you to the entire spreadsheet. (You will find 2 question tabs.)