Inspection app, compare time

I have an inspection app in which i have to inspect certain points between several times during a day.
Also i created a table the gives each checkpoint several time frames during the day.
For instance: check point 1 start: 7:00 end: 8:00
what i’m looking for is a way to have a notification if check point 1 was not inspected at all between those time and also if the check point was inspected before or after set time frame.

any thoughts would be mostly appreciated

I have created the data in excel to visualize what i think i need.

  1. from the inspections table, take the value of checkpoint and loop in the inspection plan checkpoint column until i have a match.
  2. once i have a match, check if my time in the inspection is between the start and end. if not, continue the loop. if yes, stop the loop and write TRUE in the inspection VALID column

This is a rather hard problem for me but I’ll give it a little try.

First of all, you mentioned a “loop” but I can’t imagine how a loop might work it AppSheet. Unless I’m mistaken, AppSheet is not that kind of platform.

When an inspection is done (and the Checkpoint and Time are written to your sheet), you can make the app determine what time bracket that time applies to. If the app is going to do this day after day, I think you would need the date too.

In regard to a notification (an email?), you need a trigger for the notification. Would you like the app to check at a certain time of day to see if a given time slot was neglected and then send an e-mail about that? If so, I think that can be done but I’d like to confirm that that is what you want to do.

Well, i need the app to check if all checkpoints in a route were inspected. perhaps a virtual column with multiple if statements would do the trick?

I think that is overly complicated for your problem.

Determine if Inspection is valid

If you have an Inspection Plan, that is likely already entered and exists in the app.

As the Inspections entries are made, all you need to do is insert an expression like the below in the Valid columns App Formula. If I am missing any processing steps that makes it less simple than this then please let us know.

          SELECT(Inspection Plan[Checkpoint], 
                         AND([CheckPoint] = [_THISROW].[CheckPoint],
                                  [Start] <= [_THIDSROW].[Time],
                                  [End] >= [_THISROW].[Time]
     )  > 0,

Basically, if there are any rows where the CheckPoint matches and the Time is between the Start and End times then Valid = true. Otherwise, valid is false.

Sending a Notification

When an Inspections row is either Added or Changed (depending on your apps entry process), you would have a Workflow triggered that sends either an email, an SMS(text) message or a Push Notification to the target recipient.

I’ll assume you know how to create such a Workflow. If not re-post here.


Worked great, many thanks

1 Like

So i ran into a little snag. When i create the inspection plan, i use an ID as the checkpoint itself, that means that if a checkpoint is to be inspected more than once a day i have duplicate id. I solved that by creating a virtual column that that would take the checkpoint id with the start time. Now i still have a problem with checkpoints that were not inspected. My thought was to add another column to the inspection plan and run a check there if a row has matching data in the actual inspection, if not then i will know if there was no inspection at that checkpoint. In order for that to work it should be based on time() as that column data would be over written all the time. Would like to hear your thoughts about this.

A common problem in data design is to try use some meaningful data as the key to a row. I would STRONGLY recommend to ALWAYS use a “dataless key” to assign uniqueness to a row - EVEN IF YOU EXPECT YOUR DATA ID TO NEVER NEED TO BE DUPLICATED. This is easily achieved by using the UNIQUEID() function. An exception might be in the case of very simple utility tables. For example, where you are simply providing a list of values to use in the app.

1 Like