Simulating a conditional multiple choice grid

AppSheet AppSheet Q&A
1

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.

1 Like
2

Gaius:

In Google Forms, I would use a multiple choice grid,

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.

2 Likes
3

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

3X_d_e_de70cca261a73b3cb22472d66f1b51f4c1220c16.png
3X_d_e_de70cca261a73b3cb22472d66f1b51f4c1220c16.png656×351 9.17 KB

1 Like
4

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:

Gaius:

where Lab Alpha needs to check Item 1&2, and Lab Beta needs to check items 1&3.

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

1 Like
5

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

3X_2_5_25757ec41d81370bac77cb9b0cf020e5c3b6597c.png
3X_2_5_25757ec41d81370bac77cb9b0cf020e5c3b6597c.png625×769 42.7 KB

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

3X_0_d_0de2056f60cb3645d1097cf87a6b798431e7d96f.png
3X_0_d_0de2056f60cb3645d1097cf87a6b798431e7d96f.png737×670 30.9 KB

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

3X_4_d_4d53b1d5c3cc8d6f48d61baaeb8d6687b005797a.png
3X_4_d_4d53b1d5c3cc8d6f48d61baaeb8d6687b005797a.png739×745 40.1 KB

And the formula for that Ref column

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

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

3X_7_3_73d5ce11ba6ad500a0c8e1645f25bbc33c3c1815.png
3X_7_3_73d5ce11ba6ad500a0c8e1645f25bbc33c3c1815.png676×1164 28.8 KB

Unsafe Items Automatically Populated after choosing Safe Items

3X_5_1_51c0a056da3038715772995fb226a41356844c34.png
3X_5_1_51c0a056da3038715772995fb226a41356844c34.png684×878 23.3 KB

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.

1 Like
7

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

2 Likes
8

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.

9

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]
1 Like
10

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!

2 Likes
11

Gaius:

I’ve been able to reproduce your results. I think it will work well for our customer!

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

1 Like