Missing time entry application / finding missing records...

[ronbrumbarger]

Hello friends,

I am building an application that looks through a list (view/query) of employees, builds an array of objects (Employees), then spawns (window.open) a new datapage/window and shares the Employees object/array with the child window. The child window/datapage invokes another query into a time entry database and looks for all time entries for today.

My Employee object looks like this:
name = employee name
id = employee id #
phone = employee cell #
check = boolean / status of a time entry for today or not

In the child datapage/window, a report is created retrieving all time entries from today. With each record, I loop through the Employee array looking for a matching employee ID. If a match, I set the 'check' attribute for that employee to true; time entered for the day. This approach basically enables me to look for records in the time entry table that are not present (yuck).

At the conclusion of looping through each time entry record for today, in the 'Footer' of the datapage, I loop through the Employee array and for each entry where the check flag is false and send an SMS. I want this process to run every hour from 7:00 pm until 11:00 pm every evening Monday-Friday. This "nagging" application will message employees who have not entered their hours worked that day.  (READ: Our staff is tired of nagging reminding employees to enter their time - imagine that!)

I think I have the Twilio SMS API on the verge of success (check back Monday), but my vexing problem is this:
**** How do I initiate running the initial datapage which creates the Employee array which starts the cascade of events? ****

Or... am I full of mud with my approach and should throw it away for a more elegant solution?  Any of you geniuses out there have an elegant answer to this? Zapier? Scheduled tasks in Caspio?

Help a guy out?

Thanks - Ron

[TellMeWhy]

Have you checked out Application Task? You don't need to create DataPages, it will just automatically check the records based on the criteria you've set and then notify

https://howto.caspio.com/tasks/

[ronbrumbarger]

Thanks @TellMeWhy - I appreciate the response. I'm familiar with tasks, but they're looking for records that exist... not seeking to find those that do not. My problem is I have to see who has not yet journaled their time for today. Am I missing something?

 

Ron

[TellMeWhy]

2 minutes ago, ronbrumbarger said:

Thanks @TellMeWhy - I appreciate the response. I'm familiar with tasks, but they're looking for records that exist... not seeking to find those that do not. My problem is I have to see who has not yet journaled their time for today. Am I missing something?

 

Ron

Is the time in and employee table different tables? If yes, you can check if the employee ID exists on that time table, if not, email them.

 

If no, and the time is just a checkbox, you can check if it's still not checked, then email those records

[ronbrumbarger]

Thanks again @TellMeWhy

They are separate tables. The time entered is not a checkbox, but the actual start/end times for an employee's work day (or portion thereof). It's a record in the time entry table; assuming it is present.  If it's not there, the query is empty - hence my first step of building the Employees object/array. 

While looping through the employee table, how would I go about querying the time entry table? Mind you, this is intended to be automated (as per my post above) as I don't want to have to run this manually every night.

[TellMeWhy]

6 hours ago, ronbrumbarger said:

Thanks again @TellMeWhy

They are separate tables. The time entered is not a checkbox, but the actual start/end times for an employee's work day (or portion thereof). It's a record in the time entry table; assuming it is present.  If it's not there, the query is empty - hence my first step of building the Employees object/array. 

While looping through the employee table, how would I go about querying the time entry table? Mind you, this is intended to be automated (as per my post above) as I don't want to have to run this manually every night.

I see. Just to confirm, that time entry table also stores previous days time entry, correct?

 

This is an example Task


What this task does is select from the Employee table, if the employee ID does not exist in the Time Entry table. you need to set that Employee ID = Employee ID in the Time Entry Table

and another criteria to only check the records that are made today, hence, the difference in day between date entered and timestamp is 0. You can set this to run at a specified time so it will automatically send an email to those employees

 

[ronbrumbarger]

WOW @TellMeWhy I  owe you a dinner! THANK YOU. I'll give your approach a try. That took some time - much appreciated.

What city do you live in?

[TellMeWhy]

16 hours ago, ronbrumbarger said:

WOW @TellMeWhy I  owe you a dinner! THANK YOU. I'll give your approach a try. That took some time - much appreciated.

What city do you live in?

Oh, you don't have to! Appreciate it, though! Hope it works for you.