App Structure

[james77]

I'm trying to create an assessment app which can filter a form, based on an initial configuration form. 

The initial configuration form is where basic information is captured (Project ID, Client, Date), as well as yes/no for the categories of questions I want to evaluate for that Project, e.g. "Fire Safety", "Electrical Safety", "Identified Hazards". 

From there, I have ALL of the possible questions for ALL categories as columns in a separate sheet. 

How do I filter to ensure ONLY the selected categories for that project are shown? I know I need to associate the individual questions with a category, but not quite sure how to structure this.

For example:

For Project ID= 123, the initial form to configure the assessment is filled out & only "Fire Safety" and "Electrical Safety" as selected as "yes".

"Identified Hazards" is selected as "no". I'd like only the questions related to "Fire Safety" and "Electrical Safety" to show based on the initial configuration. 

Additionally, I'd like the Project ID to be the parent record, and the individual assessments to be the child record per se...

--

I know I can use "show-if" based on inputs as an option, but I've simplified the example above for ease of explanation.... there are actually 30+ categories and 5-10 questions per category that I'd like to include. 

Thoughts about approaching this?

[Marc_Dillon]

Appsheet has no method to get the current column's name. Because of that, with a structure like yours where you have so many different columns, you'll be stuck with putting different show_if expressions in for every single column. No way around that. The other option would be to generate a set of child records, 1 record per question, from a master list of questions per category.

The separate yes/no questions in the parent form also present an issue, as you'd have to check against every one of those in separate expression clauses. I'd present an EnumList for the user to select their categories, instead of separate yes/no columns. But if you want to keep the yes/no selections, you can at least add a new column to combine them all into a category list.

Then set up a new table, where each category is a record, and another column holds a list of all question column names.

What I'd do next is to build a temporary helper GSheet to generate the individual show_if expressions for me. Basically just copy the header row from the questions table. Then in the second row, you enter a concatenation expression that stringifies the column header/name, and concatenates it into an IN() expression that's built to check against a filtered list from the new table, filtered by the categories selected in the parent. Then you just copy and paste all of the show_if expressions from the GSheet into your app.

[james77]

Thank you. Following your thoughts on this one & changing the yes/no questions to an EnumList. 

Having a bit of trouble with the last expression, I'm using a "showIf" formula to filter the question bank table by the preselected categories in the EnumList, and I'm not getting the results I want. 

[Marc_Dillon]

https://community.appsheet.com/t/referencing-a-column-value-in-a-table-versus-referencing-all-column...