Identify skipped dates in a set of records

[mandydawson]

Good Morning,

 

In the trucking business, drivers are required to record their entire day on what is known as a "Driver's Daily Log". A driver has many logs, there is one log for every date. Drivers should have one log handed in for every day of the year. One of the problems I have is ensuring that everyone hands in a log for every day. 

 

I need a script that can identify for each driver the logs that have not been handed in and alert the user. 

[Lee Smith]

Hi Mandy,

 

The easy answer, don't pay them for those days they have failed to comply. Ok, real world.

 

How do you know when they have't turned one in, is there a date field or something is empty?

 

When you say everyday, do you mean work days or 365/366?

Edited September 10, 2013 by Lee Smith
added to the question

[eos]

The easy answer, don't pay them for those days they have failed to comply.

 

Lee – that is exactly WHY she wants those dates in the first place … 

 

Mandy –

 

here's one way to do it, using a relationship, filtered portals (with helpful visual feedback) and scripts. You could also loop through the records in the Log table directly (after doing a Find on the filter year, presumably). Note that you need to integrate any business rules (like Lee said: every day of the year?; workdays only? etc.) when you create the list.

 

You also must decide where you write the missing dates – one ever growing field in Drivers, or another child table? One idea would be to use the child table you already have – add records for the missing dates to the Log table and mark them as "submitted”. This would work in reverse of the other scripts: take a list of missing dates for a driver and loop through it to create new, ”unsubmitted“ Log records for the driver.

FindGaps_eos.fp7.zip

[Lee Smith]

Lee – that is exactly WHY she wants those dates in the first place 

And that is why I asked what was in placed now, if anything, as far as how they would know.

[mandydawson]

Drivers need to hand in a log for everyday of the year, 365 days that is, but I only ever need this particular data for the previous six months. This database isn't used for payroll purposes in any case, just to check and ensure compliance with Federal Legislation among other things. I need to be able to find which logs are missing by the records not existing in the database. 

 

Thanks for the help, eos. I'll play around with this and see if I can integrate it in. 

[mandydawson]

Works like a dream. Thanks for the help