Hello my name is Stuart Drollinger, I work at MicroGREEN Polymers. I am in the process of developing a system that will track policies, safety guidelines or something similar to make people accountable for their actions, inside of the company, so there will be the ability to add, edit, and check up on policies. Each employee will be able to see the policies that are required of them to sign off on. I am trying to figure out a good way to do a once, weekly, biweekly, monthly, quarterly, and annually policy update where it will email people on the day the policy gets issued to the employee. Any suggestions are welcomed
What exactly are you looking for here? You could store all of the policies in a database and display one for the user based on the the most recently entered.
So, what I am trying to do is this. I want the supervisors to insert a policy.
Example:
Employees most wear cut resistant gloves when using a knife.
This policy gets assigned to, Bob, Joe, and Dillon.
When any of these three people go to the policy page i want them to see all the policies that are assigned to them. and which ones they have accepted.
There will also be instances of these polices. Some policies might happen on a weekly basis where an employee agrees not to do something or to do something, or a monthly basis.
The home page is going to look something like this. There will be a table of all the Policies the current user has assigned to them. There will be a second table to show which Policies the current user still has to accept. And there will be two text fields that will be filled as he clicks on a policy from each table.
That’s easy enough. Are you using a Database Authentication profile? If so you can use that users table.
So here’s how I’d do it:
Have 3 tables: users, policies, and user_policy_mapping.
The policies table would have the policy text.
The user_policy_mapping table would have a user id column, a policy id column, an accepted column, and an expiration date.
Then your user’s screen could have a table with a query that fetches the policies and joins to the mapping table where the user = the logged in user and the expiration date > CURRENT_TIMESTAMP
You could instead put the expiration date in the policy table if it should expire for everyone at the same time.
I am not concerned with an expiration date, just that some policies will have an aspect that is reoccurring
OK, then you just need the accepted column.
How would you handle a policy that has a quarterly frequency? or a yearly one?
You would have to add a gateway script that automatically inserts into the table each week/month/quarter/etc.
I would add another table for items that are regularly scheduled and have that gateway script pull from there.
Alright. so I am at the point where it will make a new instance of a policy when you accept. It will send emails out to everyone who has been assigned to policy, do you know of a way to send emails out based on the scheduled date of the policy? example. I accept a policy on 5/22/2013 the next time i need to accept this is 6/22/2013 i will have untill 6/23/2013 to accept it or it will be late. is there a way to send an email on 6/22/2013 to remind that i have to accept it?
Yeah, you can create another gateway script that checks your policy table and based on the accept date and whether they have accepted it, send out an email. You just need to rely on a link between the policy table and who should be getting it.