2 Replies Latest reply on Feb 4, 2016 7:41 AM by nbray@brandywinebus.com

    Find Related Oldest Date Within Specific Date Range

    nbray@brandywinebus.com

      Hi All:

       

      I'm looking for help creating a calculation field that will be the value of the oldest related record, but restricted to dates within the last five years.

       

      To explain:

       

      We maintain an employee database, which tracks various credentials, clearances, and training sessions. In this case, we have a training which may take place over the course of five years, but must maintain a minimum number of hours in every five year period.

       

      I have a report that will give us the sum of the number of hours within the last five years, but I would also like to include the next date that time will "drop off" from the report, so that we can easily see when someone is coming up on a period of time that they will need to attend retraining.

       

      Does anyone know if/how this is possible?

       

      Thanks!

       

      Nate

        • 1. Re: Find Related Oldest Date Within Specific Date Range
          keywords

          Without thinking through the whole scenario, I reckon I'd start by tracking the hours in their own separate table so that each record contains employeeID, datePerformed, and whatever other links and data are relevant to the situation—so you can track the relevance of particular hours to particular credentials, for example. Then I'd set up a date calc in the employee table that was current date minus five years, and then use that to flag records in the hours table that were older than that date, or within a month of that date, or whatever. Then I'd show employees hours in a portal sorted by date worth oldest showing first. Something along those lines.

          • 2. Re: Find Related Oldest Date Within Specific Date Range
            nbray@brandywinebus.com

            So I've actually done something similar, but am stuck on the last part - putting it on a report.

             

            There is a field that is is calculated by today's date, less five years. It is a text field, and the value is either "Valid" or blank.

             

            I could easily filter a portal, but we are looking to have only the oldest "Valid" date appear on a report, along with other information that needs to be tracked about each employee.

             

            I tried the following "if" calculation:

             

            If(Employees_Child_Abuse_Rec_Reporting_Training_History::ValidToday="Valid";Min(Employees_Child_Abuse_Rec_Reporting_Training_History::Attended_Date);"")

             

            It returns the oldest date if all training was completed within the last five years. However, once any training time is outside of five years, it sets the value of that field to blank.