Formula for Display Name? to randomize questions for auditing

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?

0 31 506
31 REPLIES 31

LeventK
Participant V
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)))
)

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

The second one says SELECT has invalid inputs

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

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

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

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

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.

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

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

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

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.

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

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.

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?

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.)

3X_c_a_ca77f5509a8fac15296186068cf689f5a5aabe43.png

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.

@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.

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

@Work_Goscinski
Appreciated. I will check the sheet. Please allow me a couple of days.

Absolutely. Thanks for your help.

Youโ€™re welcome.

@Work_Goscinski
Can you tell me whatโ€™s the difference between LPAChecklistPool and LPATemplatePool both as a table and in terms of usage? Thnx.

No, I cannot. At this point I have not received further clarification from those above me. I know they are different, but Iโ€™m not sure how. So, I was treating them differently.

Gotcha. So I will base and rely on LPAChecklistPool only if thatโ€™s OK for ya.

Perfect. I will mimic whatever you do for the other.

Again, I appreciate it.

LeventK
Participant V

Youโ€™re welcome @Work_Goscinski. Thinking that the back-end data might contain either yours or your companyโ€™s IP rights, please pass me your email adress at your convenience. After I build the demo app, I will add your email as a co-author, so that you can take look under the hood and grantly take a copy of it as I will not be presenting the solution/demo app as a POC under this post as per afore-mentioned rights.

Provided you think the opposite, please deliberately inform me, so that I can deploy it as a public sample app.

Thanks.

Dgoscinski@gil-mar.com is my email on here

Thanks @Work_Goscinski, duely noted.

Hello @Work_Goscinski,
Thanks for joining me in Zoom today. Hope the demo app that I have shared with you will be helpful with your development. Please do post an email or a DM (whichever you see fit) should you require any further assistance. Wish you a Merry Xmas and a Happy New Year full of health and joy.
Cheersโ€ฆ

Hi, Iโ€™m having some issue that I think is related to this particular idea, but seems at first, simpler. Iโ€™m trying to make a survey app. I need to have images as answers, but I wanted them to be displayed ramdomly. Do anyone of you think that this is possible?

Top Labels in this Space