Table Structure Question

[Mike_Wrob]

I would like to create a checklist app for department heads to record their daily inspections. I will have a table for each department with columns for each item that needs to inspected and a row will be added each day. My question is I would like the plant supervisor to be able to view which departments have completed their inspection each day. What is the easiest way to do this?

[LeventK]

@Mike_Wrob
How many columns of data will each department have? How many departments do you have? I believe the best approach could be having a single table and with a security filter (note that requires a Pro plan) you can filter out each department’s and/or user’s data easily in the app. And provided you create a Users table, you can also show/hide specific column set as per department/user as well in the Form View which will ease your users when creating records.

Additionally, you can create a slice depending on this table for your plant supervisor(s), assign it to a Table UX which could only be seen by them and you can group that view as per department.

This approach also depends on your data size for sure. How many records are you expecting per day or per month? For example; provided you will have 150 columns of data and 200 records per month, than any VC that you will need to create will be calculated/iterated 30.000 times. For sure you can consider adding a date filter to your security filter expression(s) too, for reducing the amount of data that will be cached in the app.

Provided you can clarify the data model that you have or thinking about, we can provide more help on this aspect.

[Aleksi]

How about a related table structure… department as a parent and inspections as child records?

[Mike_Wrob]

That was my original thought. However, that is only going to give a view of inspections that have been completed. I would like the supervisor to be able to view a list of all the departments and see which ones have completed their inspections and which ones have not.

[Aleksi]

You could add a virtual column like IN(“Not completed”,SELECT([RelatedInspections][Status],TRUE)) into your parent table. If it’s TRUE, you know that all inspections are not completed.

[Mike_Wrob]

OK, I was on vacation for 3 weeks and then catching up once I got back. I’m ready to try again. A little more detail to help advise me.

-There are about 20 departments
-There will be 10-20 items to be checked in each department, every day.
-Inspection items are not the same in each department. Although there will be some common items.

My plan was to have a sheet for each department with a column for inspection date and columns for each item to be inspected. Also a sheet that will list the departments.

I would like a way for plant manager to see who completed their inspections the previous day and who did not. If he selects a department it will then bring up the inspection so he can see the results.

I’ve got a couple ideas but neither seems very clean. One is to add a column in the departments sheet for date of last inspection and have that field updated when an inspection is completed. The other is to make a common sheet for the department inspections with 100-200 columns of items to be inspected. The departments sheet would specify which columns are applicable to the particular department.

I’m open to other ideas.

[Aleksi]

What kind of inspections you are doing? I mean are they like text fields or dropdowns or Y/N? Is there a common pattern like could you use the same 20 fields for all inspections? I’m thinking a structure where the inspection would be a child record for your department.

[Mike_Wrob]

Mike_Wrob:

-Inspection items are not the same in each department. Although there will be some common items.

Most items are yes/no but would also like some text and some photo fields.

Another set up I thought about was making a table of inspection items and each record would have a field setting the department it belonged to. Then have an inspections table and a child table with inspection line items. However it will get several hundred lines per day added, so it will get large rather quickly.

I’m thinking I’m just going to do the simple way and make a table for each department and have the inspection fields in them.

[1minManager]

So for this:

Mike_Wrob:

I would like a way for plant manager to see who completed their inspections the previous day and who did not. If he selects a department it will then bring up the inspection so he can see the results.

What I’ve done in the past is to have a user table. Then against each user have a virtual column that checks that this person has done their checks. This VC can, if required, look at multiple tables.

You could repeat the above with a table for each department.

You could also create a sort of ‘check summary’ table in Google Sheets, that it then displayed as a table in Appsheet

Though the best strategy would be to try and combine all the checks into one table. That way you easily display all the data into one table for each manager to see.

Simon@1minManager.com

[Aleksi]

If you have like 20 departments I would create the app with two tables… one is a template where you have all questions, dropdowns for these questions and then another table for inspections. With this kind of structure the data amount would be the same. Your form structure seems to be the same… first generic fields like Department name (this will select the template), Created, Created by and then Enum and text questions and finally few image or drawing fields. I have used this method and the good thing is that you have all data in one table.

When you have a departments table that table could be the question table as well. If you use Ref field in the inspection form, you can easily then calculate the status department by department with the virtual column.

[Mike_Wrob]

I am getting confused (that happens easily).

Let’s just say your kids were responsible for checking a room of the house each day to make sure stuff was clean.

Kitchen:
-stove
-refrigerator
-sink

TV Room
-TV shelves
-Remotes in place
-Light off

Bedroom
-Bed made
-Clothes hung

Are you saying make one table with stove, refrigerator, sink, TV shelves, Remotes, lights off, bed made, clothes hung as fields? If you make a view for each room to just show the relevant fields, how do you make sure all three inspections show up on the same line for a given day if they each perform their inspections independently?

[Mike_Wrob]

OK, I think I found a sample app that is similar to what I want to do, the rig inspection apps. It looks a little complex for my first app, but it should be a good start.