9 Replies Latest reply on Jan 28, 2016 6:09 AM by siplus

    How to create a report based on Expiry Dates

    hongman

      Hi all

       

      I have created a basic Training Matrix table which lists the following fields:

       

      trainingID

      Name - Dropdown list from Contact table

      Q1 (Qualification) - Dropdown List of Qualifications (preset list)

      Exp_1 - Date field with conditional formatting (Get ( CurrentDate ) > Training Matrix::Exp_1) - Turns text RED

       

      Q2

      Exp_2

       

      Q3

      Exp_3

      ...

       

      Q8

      Exp_8

       

      The idea is, quite simply, all staff member's qualification's are entered along with the exp date.

       

      2 more things I'd like to do with this for now:

       

      1. Turn text YELLOW if expiry is due in 90 days

       

      2. Run report to show the names of all people who have qualifications expiring in 90 days, or expired.

       

      3. Can I make it send an email to alert on condition trigger?

       

      How do I do this?

       

      Thanks in advance

        • 1. Re: How to create a report based on Expiry Dates
          siplus

          Your structure is quite rigid, with

           

          - a fixed number of qualifications for each staff member (8) and 16 fields taking care of that

          - a fixed number of days in expiry due alert (90 days)

           

          I would go to a more flexible structure, with 3 tables: staff, qualifications, staffQualifications (the latter being a Lineitem table between the first 2). ExpiryDaysAlert should be a global set on Database opening, as a gToday global, set to Get(CurrentDay).

           

          1) Turning yellow is just a matter of adding another formula to conditional formatting; beware the order as they are evaluated one after the other so make sure the "RED" one is the last.

           

          2) You would run the report on the staffQualifications Table, doing a simple find, sorting by StaffMember name

           

          3) If your solution is on a server, you can schedule a script running at 00:01 which will find the data and send emails to the people, but beware of sending 90 emails, one per day...

          • 2. Re: How to create a report based on Expiry Dates
            hongman

            Thank you siplus for your help!

             

            I must admit, I am very new to this and this paragraph:

             

            "I would go to a more flexible structure, with 3 tables: staff, qualifications, staffQualifications (the latter being a Lineitem table between the first 2). ExpiryDaysAlert should be a global set on Database opening, as a gToday global, set to Get(CurrentDay)."

             

            Is making me very confused!

             

            Can you break it down please?

             

            3 Tables

             

            Staff - Would I need this? I already have a linked table with staff details (contact table)

            Qualifications - What fields would this table contain?

            staffQualications - Lineitem table between the first 2 - sorry but that went straight over my head!!

             

            Point 2 also might as well be a foreign language to me at this point!!

             

            As for the conditional formatting, I was after help on what the formula would be. I dont know how to write "Subtract 90 days from (current date)" to do a calculation on expiry date?

             

            This is not on a server, yet.

             

            Sorry, super super new!

            • 3. Re: How to create a report based on Expiry Dates
              hongman

              Ok so I figured out the 90 days turning yellow conditional formatting, I'm an idiot and had subtracted instead of added on the formula for 90 days (and I didnt know I could literally just use "90" without any additional syntax

               

              So, just to clarify on the reporting and additonal tables?

              • 4. Re: How to create a report based on Expiry Dates
                siplus

                See the attached example, and I guess you'll understand 95%.

                 

                After playing with it, tell me what the remaining 5% is

                • 5. Re: How to create a report based on Expiry Dates
                  hongman

                  Wow, thank you so much!

                   

                  Will you faceplam really hard if I were to tell you its more like the other way round with the percentages?

                   

                  I have no idea to take what you produced (which looks 100 times better than what I have and took me many days...and still doesnt work) to implement into mine.

                   

                   

                  • 6. Re: How to create a report based on Expiry Dates
                    siplus

                    you're being ruthless with yourself ! Give yourself some time to study what I did there - just implementing without understanding what you're doing is bad and does not help you become better.

                     

                    Basically you focussed upon a single Contact and tried to add fields to it to solve a problem; I created a table with the problem's name and added what was needed.

                    • 7. Re: How to create a report based on Expiry Dates
                      siplus

                      I fixed some small bugs, added the option of deleting scheduled qualifications, added a control that won't let you add an already existing qualification to the same contact and added a report button (Qualification report) to give you the situation you wanted right from the start (works at its best on Macs only).

                       

                      If you like what you see, take a deep breath and examine it with patience. This forum is not only about problem solving, it's about learning, too ...

                      • 8. Re: How to create a report based on Expiry Dates
                        hongman

                        Thanks.

                         

                        I have already changed it to match my own Qualification list.

                         

                        I just need to figure out how the following:

                         

                        1. Change the contacts to integrate with my own Contacts database for the Names

                        2. You have implemented the Planned Qualifications portal, but I want it to show all upcoming qualifcation expiry records instead

                         

                        I will be trying to rip this apart to get to grips with it, but everytime I look it all gets a bit dizzying!

                        • 9. Re: How to create a report based on Expiry Dates
                          siplus

                          1) Depending on how complex your database is, you can add my tables, relationships, layouts and scripts to it, or add your database to mine, doing the same thing in the other direction.

                           

                          2) If the qualifications also have an expiry date after having been completed (was not in the original requirements), you can simply add an expDate to the ContactQualifications table. If what I called DueDate is in fact the ExpDate, just rename things.