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...
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?
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!
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?
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.
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.
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 ...
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!
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.