Hi
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
Solved! Go to Solution.
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.
IF(
COUNT(
SELECT(Inspection Plan[Checkpoint],
AND([CheckPoint] = [_THISROW].[CheckPoint],
[Start] <= [_THIDSROW].[Time],
[End] >= [_THISROW].[Time]
)
)
) > 0,
true,
false
)
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.
I have created the data in excel to visualize what i think i need.
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.
HI
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.
IF(
COUNT(
SELECT(Inspection Plan[Checkpoint],
AND([CheckPoint] = [_THISROW].[CheckPoint],
[Start] <= [_THIDSROW].[Time],
[End] >= [_THISROW].[Time]
)
)
) > 0,
true,
false
)
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
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.
User | Count |
---|---|
42 | |
30 | |
25 | |
23 | |
13 |