Hi Gliders, In order to help SMBs, I would like to create an app for business diagnostis. It is more or less like some question forms (300 questions divided into 10 topics) with 6 assessment criteria for each question. These criteria are either text or dropdowns (yes, no | high, medium, low | 1 - 5 …); the dropdown content is different according to the questions x criteria. I am “struggling” to find out how to structure the tables of the database (see image). I’d like to avoid 600 columns! (100 questions x 6 criteria) to build the form that users will answer… [image] If you had an idea, that would be great. Thanks in advance

I would reuse some of that criteria if it can be. For example, if you have a lot of questions that ask Yes or No, use the same Yes/No choices for multiple questions. You can also set up different types of choices in multiple columns instead a row for each choice. So one column could be YES, NO on two rows in one column, and another column could have choices 1,2,3,4 on separate row. Then just choose the column you need depending on the question.

Thank you @Jeff_Hager. What I don’t see is how I will be able to have several “criteria to assess” for each question in the form. It’s maybe a basic question, but to build the form, shouldn’t I normally set a table with 1 “question” per column (in my picture it’s in rows), and then the row would have only 1 possible answer (ie. criteria)? While I want 6 criteria to assess for each 100 questions, I just don’t see how set-up the table without having: question1-criteria 1 | q2-c2 | q3-c3 … || question2-criteria1 | q2-c2 | etc

Can you provide some more explanation and/or screenshots on how you are setting it up currently and how you want it to flow? Are you saying that there is one question per row with further sub criteria questions for each main question? Just trying to properly visualize this. I do better with real data. Maybe I’m misunderstanding the question. I thought you were trying to created choices for each question, but possible had duplicated choices that could be trimmed down.

Thank you @Jeff_Hager. My request is unclear, sorry. What I try to achieve is to create a very long questionnaire. But, for each question, several answers (“assessment”) must be provided. The picture below is a very design (…) illustration of the target form on the app. [image] Where I struggle is how to structure my various tables to build the form: If I had 1 “question” - 1 answer, it would be a basic table with each column being a “question”. But how I can make it for 1 “question” - 6 different answers … without having 600 columns. My initial picture (below) was the way I was considering to structure my “form” table in G-Sheet; this works well in G-Sheet of course (questions per row x 6 columns for the criteria) but for a Glide form… [image] Thank you!

I’ve been thinking about this and I think I have some follow up questions. Are you planning to present all of the questions and criteria on one page, or would there be one page per question? Are you planning to use a form to get the answers, or would it be some sort of detail screen? Will one company be filling this out at a time, or could multiple companies be filling out the answers at once? Do all of the answers need to be accessible to you, or are all of the answers used to calculate a final result? Would a user ever fill out the questionnaire again, or is this a one time only thing? Can answers be changed at a later time, or do they only have one chance to answer the question? Due to the large number of questions, I think in my opinion the first thing I would do it is to have a sheet with all of the questions listed in rows along with a RowID, Topic, and Question Number. Then I would create columns to indicate which type of criteria choices to display. This can be used to filter a list of choices that can be reused among all criteria without using up too many rows. Then I would probably create user specific columns to hold the answers and the comments. So it would look something like this. Row ID Topic Question NumberQuestion Criteria 1 Type Criteria 1 Answer Criteria 2 Type Criteria 2 Answer Criteria 3 Type Criteria 3 Answer Criteria 4 Type Criteria 4 Answer Criteria 5 Type Criteria 5 Answer Criteria 6 Type Criteria 6 Answer Comments 12345 Topic 1 1 Question1 YN **** HML **** 15 **** TF **** YN **** TF **** The Choices sheet would look something like this: Criteria Type Choice YN Yes YN No TF True TF False 15 1 15 2 15 3 15 4 15 5 … What I would do is probably have a tab that shows a list of questions. A user can select a question to view it on a details screen and begin answering the criteria. Once all the required fields are filled, you could use visibility to display a Save or Submit button. This button will be tied to a compound action that will write the user email, rowid, topic, question, and answers to a new sheet through the Add Row Action, then you will clear all of the user specific columns with a Set Columns action if you choose. At this point it gets a little to decide how you would want to navigate from question to question and there are quite a few ways to handle that. What I would probably do is add some extra user specific columns to your questions sheet. One to hold a value that you can increment to navigate through all of your questions, another to use that increment value to create a single relation to the corresponding question, and then a series of lookup columns to get the rowid, topic, question, criteria, etc. You would display the lookup columns and also use them for for displaying each question on the screen. Once a user answers the question, a third action on the submit button would be to increment the user specific value, which updates the relation, and then shows the next question. This is most likely how I would set most of it up. I’m sure there would be some issues along the way, but I think it would be pretty easy to work through it. I think in the end, it will save you the most rows and get result added to a separate sheet for analysis. I hope this gives you some ideas.

MANY THANKS @Jeff_Hager for this long and detailed reflection on my ‘challenge’. I will test your ideas and come back to you. In the meantime, I answer to your questions in next post. Thank you again!

Are you planning to present all of the questions and criteria on one page, or would there be one page per question? Since it is an MVP, I can use the simplest way of building it. At target, I was considering having 1 form for each 10 topics (ie. 10 forms), with all questions of each topic in this form (probably with a ‘link to screen’ button to simulate different pages for UX matters) Are you planning to use a form to get the answers, or would it be some sort of detail screen? I was thinking about Form, but if it’s easier I can do it with a detailed screen. Will one company be filling this out at a time, or could multiple companies be filling out the answers at once? Ideally, the app can accessed by several companies at the same time; but again, I can make it one after each other at the mvp stage. Do all of the answers need to be accessible to you, or are all of the answers used to calculate a final result? I would need to see the answers, in particular because (at mvp stage) I (or another consultant) may have to guide the SMBs into its analysis. The “user” signing-in is by the way something that I did not work in detail because I am not sure that I am able to build it with my basic glide knowledge to build it: in the perfect world, I could have a company directly signing-in and conducting its analysis; or a Consultant having an “agency account” and creating new company (clients) for which she/he does the analysis. Probably not for the mvp stage … Would a user ever fill out the questionnaire again, or is this a one time only thing? The user will probably fill-out the questionnaire in several times (100 “complicated” business questions… maybe more). In addition, she/he will for sure need to modifiy some answers all along the analysis. Can answers be changed at a later time, or do they only have one chance to answer the question? Once again, thank you so much for your time!

I don’t think I mentioned it above, but there are a couple of reasons why I would stay away from a form. Most importantly, if for some reason you had all 100 questions on one form, and for some reason the app restarted, you would lose any progress that has been made. I think you can get a smoother experience using a detail screen and user specific columns to act as your “form”. You could have each individual question open a form, but that would be a lot of back and forth navigation for the user. I think you can get a better app flow making use of the compound actions on a button on the detail screen. Good luck!

[image] Jeff_Hager: Good luck! … I will need it! I’ll let you know, thank you again

[image] Jeff_Hager: Most importantly, if for some reason you had all 100 questions on one form, and for some reason the app restarted, you would lose any progress that has been made. This is important. I think around a month ago a guy with a crazy form had this same situation.

600 columns for a form?

Ask for Help

Darren_Murphy February 2, 2021, 1:43pm 12

I remember that…

Topic		Replies	Views
Long Form guidelines Ask for Help	25	1452	September 4, 2021
Personalized questionnaire with categories per user Ask for Help	11	468	January 29, 2021
How do I create a custom form? Community Resources	161	19035	March 24, 2025
Using one-dimensional tables Ask for Help	6	805	March 14, 2021
User Specific Columns + User Profiles Demo Community Resources tutorial	89	5579	May 31, 2020

600 columns for a form?

Related topics