I am trying to figure out the relationship structure of my tables. I have a Client Table, Client Employee Table, Job Title Table, And Required Training Table.
Obviously a client can have multiple employees. Employees may have multiple job titles. A job title can have multiple required training courses. What those training courses that are required are depends on the client.
So what I'd like to do is be able to add an employee to a client, pick their job title(s), and show what training is required for that job title, for that client. Not all clients will require the same training.
I know this is a very broad question, but any help into how I'd lay that out and design the functionality will be appreciated.
One Client has Many Job Titles, which in turn each have Many Training Requirements
One Client has Many Employees
One employee has Many Employee Job Title Assignments. This is a join table where One Employee has Many Employee Job Title Assignments and One Job Title also has Many Employee Job Title Assignments. When choosing a possible Related Job Title, this will be a conditional drop down where the Related Client of the Employee matches the related Client of the Job Title.
Now, here is the magic.
Set up a single record in a new table called Focus employee. It will be Record ID 1. Create the field Focus Related Employee by making a relationship where One Employee has Many Focus Employees. But there will only ever be one Focus employee record. Also add a formula numeric field to this Focus Employee table with a formula of 1, called [Employee is in focus (=)1]. It will be equal to 1 if the employee is in focus.
Ok, now we select a focus employee, who is named Fred Flinstone.
On the relationship between employee and Focus employee, make a summary field of the maximum of the field [Employee is in focus (=)1].
If you were now to check your employee tables, you would find that only Fred has that summary field set to 1. I call this lighting up the Employee.
Now, look up that [Employee is in focus (=)1] down to the Employee Job Title Assignments.
Now all of Fred's Job Titles are also lit up as the lookup field. Employee is in focus (=)1]. So perhaps we now know that the Employee job titles Assignments for Dinosaur riding and Driver of cars with no floorboards are also lit up.
Now we do a summary field between Employee Job Title Assignments and Job Titles for the maximum of [Employee is in focus (=)1] and call this [Job Title is in focus (=)1]
Great now the actual job titles for Dino riding and driver of cars without floorboards are lit up. In the field [Job Title is in focus (=)1] Is indeed equal to 1.
Now do a lookup down from the Job titles down to the Training required of that [Job Title is in focus (=)1] Field. Great now all the training requirements for those two job titles are lit up. That was your goal. So the cake is baked and you can simply run a report of lit up training requirements.
But now we need icing in the cake.
Make a formula numeric field on the Training requirements table with a formula of 1. Call it [Link to all Job Titles].
Make a report link field on that single record focus employee record and connect where the Record ID of the focus employee record is the same at the field [Link to all Job Titles].
You will have already made a report on the Training required table of all Training required which were lit up to see for yourself that it's working.
Put the Report link field on the focus employee record and choose to show the training required records on the form as an embedded report and use that report of lit up Training Required.
So now when you choose an employee, the light shines from the focus employee back through all the summary and lookup fields and shows the training required right on the record you are sitting on. Yay!
Now, the next question for another post is now that you have identified those training required records, how can you create Employee Training Requirement records directly as children to the employee records, so you can then track if the employee did compete their training in the time required.
I set up everything before that and it worked fine. I am trying to understand and work out what you had after that. I'll let you know when I get it.