Solved: Simulating a conditional multiple choice grid

Gaius · 08-26-2021 12:16 PM

I have an application for users to do an inspection in one of 17 labs. There are a bunch (39) issue types that can occur during the inspection, and each lab has a unique combination of issue types.

During an inspection, each issue type needs to be marked “Safe” or “Unsafe”. In Google Forms, I would use a multiple choice grid, but I understand that is not an option in AppSheet.

It would look something like the following, where Lab Alpha needs to check Item 1&2, and Lab Beta needs to check items 1&3.
Inspection: 1
Lab: Alpha
Inspection Items:

Item 1: Safe
Item 2: Unsafe

Inspection: 2
Lab: Beta
Inspection Items:

Item 1: Safe
Item 3: Safe

I’d like some guidance on how I can do this. Any help would be appreciated.

WillowMobileSys

Ok, It took me a bit to correlate your Lab Inspection table to the examples.

If I understand correctly, your intention is for the Lab Inspection Category to represent the list of items the Lab needs to inspect - i.e the Inspection Items shown in your original example.

A Ref column is meant to point to only a single row in another table. So I don’t think that will work here.

Here is a suggestion…

Make the “Safe” column into “Safe Items”; and the “Unsafe” column into “Unsafe Items”.

Both columns would be defined as EnumList with Base type as Ref. Then apply your expression to both columns Valid_If property to get the list of POSSIBLE items to be inspected.

A technician then would simply need to click on the “Safe Items” column to see a multip-choice dropdown and then go down the list checking each item that is considered Safe.

I would imagine that Items must be either Safe or Unsafe and cannot be both. If true, then you really only need to track the Safe list - all other items are considered by default Unsafe. You can automatically assign the Unsafe list. See images below

Safe Item EnumList Dropdown List

Unsafe Items Automatically Populated after choosing Safe Items

A solution more in line with your request

While you cannot easily create the conditional grid you wish, you could simulate it with some effort.

Before I go into more detail on it, let me know what your thoughts are on the above.

View solution in original post

WillowMobileSys

Could you show what this Google Forms multiple choice grid looks like? I’ve only built a few Google Forms and not understanding what this means.

Gaius

Here’s an example in Google Forms of the multiple choice grid.

WillowMobileSys

I see. And your goal is to conditionally decide which Item shows on which Inspection? If so, what determines if the Item does indeed show for a specific Inspection?

EDITED:

To elaborate…it is pre-determined which Items are to show for LabA and which other items are to show for LabB?

Gaius

Which items show is conditional on the Lab & the Inspection Category (Weekly, Monthly, Quarterly)

These items are pulled from a “Lab Inspection Issue Type” table. Here are some more details.
Lab Inspection Issue Types: Where “Inspection Items” are pulled from

Lab Inspection table, with ref column to pull in the "Inspection Items"

And the formula for that Ref column

SELECT(Lab Inspection Issue Type[Key], 
        AND(
            IN( [_THISROW].[Lab], [Labs]), 
            ( [_THISROW].[Lab Inspection Category] = [Inspection Category])
        )
    )

WillowMobileSys

Ok, It took me a bit to correlate your Lab Inspection table to the examples.

If I understand correctly, your intention is for the Lab Inspection Category to represent the list of items the Lab needs to inspect - i.e the Inspection Items shown in your original example.

A Ref column is meant to point to only a single row in another table. So I don’t think that will work here.

Here is a suggestion…

Make the “Safe” column into “Safe Items”; and the “Unsafe” column into “Unsafe Items”.

Both columns would be defined as EnumList with Base type as Ref. Then apply your expression to both columns Valid_If property to get the list of POSSIBLE items to be inspected.

A technician then would simply need to click on the “Safe Items” column to see a multip-choice dropdown and then go down the list checking each item that is considered Safe.

I would imagine that Items must be either Safe or Unsafe and cannot be both. If true, then you really only need to track the Safe list - all other items are considered by default Unsafe. You can automatically assign the Unsafe list. See images below

Safe Item EnumList Dropdown List

Unsafe Items Automatically Populated after choosing Safe Items

A solution more in line with your request

While you cannot easily create the conditional grid you wish, you could simulate it with some effort.

Before I go into more detail on it, let me know what your thoughts are on the above.

Gaius

This is a really good option and I think it will work! I will give it a try and check with my customer.

Gaius

I would imagine that Items must be either Safe or Unsafe and cannot be both. If true, then you really only need to track the Safe list - all other items are considered by default Unsafe. You can automatically assign the Unsafe list.

I’m struggling to get the Unsafe portion of your example to work. Can you provide a bit more detail on how to do this? Thank you.

WillowMobileSys

AppSheet provides List operators “-” and “+”

So you can automatically assign Unsafe items using this expression:

<<expression used in Valid_If for dropdown of Safe Items>>
- [Safe Items]

Gaius

Thank you again.

To clarify, I added a conditional statement into the Valid_If for the [Safe] column.
Then, for the [Unsafe] column, I added " - [Safe]" into the Autocompute. I’ve been able to reproduce your results. I think it will work well for our customer!

WillowMobileSys

Awesome! Thanks for following up and letting me know your end results!