2 Replies Latest reply on Jun 13, 2015 11:45 AM by jdevans

    Check to see if required records have been created for date range



      Check to see if required records have been created for date range


      I have a table that stores pdfs. These pdfs are required documents that each employee must turn in at the end of each week. I want to give specific users (via privilege set) the ability to go check to make sure all employees have created this record for a given week. If not, they will know who hasn't done it, so they can be notified. Eventually, I'll send automated emails based on this information, but first I want to know how to check for "missing" records. Each user has their own login, and that account_created is part of each of these pdf records. My psuedo-code goes something like this:

      1. Get list of all active account names
      3. Find all records in the specified table that were created in date range, and get account_created list for this found set.
      5. Compare active account name list to account_created list
      7. Report difference list

        • 1. Re: Check to see if required records have been created for date range

          What you're describing sounds like searching part of the audit trail (the "date_created" field in the record), with

          6/6/2015 .. 6/12/2015

          as the search criterion.

          But that won't work for the Johnny-come-lately who posts his record late: the audit trail will never match unless you allow your privileged users to alter it --- and what would be the point of allowing that?

          Put a date field for "Week of" or "Week Ending" in your PDF table and use that.  Assuming it's "week_of" a search for

          week_of .. week_of + 6

          should do the trick.  The field can be auto-filled on record creation; allow users (or some users) to change it as necessary.


          • 2. Re: Check to see if required records have been created for date range

            What I ended up doing was what you suggested, and I utilized variables to grab the lists. I used Begin Date and End Date fields that only the privileged user to can see. He selects those, the clicks a search button. The search runs a script that sets a $$all_users variable to all those who should have a record for the date range. After searching on the date range, a second variable gets set to the list of unique account_modified users who created their record. A third variable is set to the difference using a custom function I found over on the Brian Dunning site. Then a floating new window opens and shows the list of offenders. I also added a found_count field to report the number of offenders for the date range.

            It works pretty well, and the privileged user likes having the flexibility to search on a begin/end date range. He can also search using the regular Find mode button in the status bar for more specific users, if he wishes.

            BTW, we have lots of Johnny-come-latelys here. This will likely need to be tweaked. The old system we had, each pdf that was created was put in a shared directory folder, and had a very specific naming convention. Then you could parse the name of the file for the date/user, and spit out a very specific offender list. The naming wasn't based on creation date, just which week it is for, and who created it. Made finding records easy...but it wasn't in a managed database. Not much in the way of protection for fraud or security.