Long time lurker, first time poster. I'm still fairly new to FM and have taken on a project at work to build a database to track when employees have read and agreed to a company policy or procedure. I'm fine with that part, I have an Employees Table, a Documents Table. I've scripted it so that when employees has opened a document and clicked the "I have read and understood this document" button it creates a new record in the Employee_Document Join Table, with the date it's been signed-off and the expiry date for the sign-off (employees need to re read polices etc every 12-24 months).
So far so good.
The issue I've run up against is that we have 200+ employees in some pretty diverse fields. We have carpenters, designers, marketing, actors, technical staff.. the list goes on) and each group only needs a subset of the documents. and these groups aren't necessarily fixed, people may be in multiple groups and group membership can change.
My first attempt to resolve the assignment problem was to add employees to groups (via join to a Groups Table) and assign documents to these groups (through a Groups_Documents join table). The intent being that HR users could create new groups and assign documents to these 'special groups'. I think I was attempting to be too clever!!!
I then thought it may be simpler if I created an Employee_Document join for every Employee to every Document and then set the document as active based on whether the employee is a member of a group that requires the document i.e. If employee is member of [group x] and [document] is listed in group set field (active;"TRUE").
I'm having trouble:
1. I need to run a script to check there is a join record for every employee to every document and create a record when new employees or documents are added, I've not used scripting of this type before and I'm not sure where to start.
2. I'm not sure what the best approach to scripting document assigned to group and employee is also assigned to group check? Would it just be easier to hard-code the group membership, creating a field for each group in the employee table?
Sorry for the rambling question!
PS I know my naming conventions aren't great, and I should be using Employee_ID as the PK for Employees, rather than the Employee_Number