Creating Entities for an Employee Database

[emcclure]

I currently have an employees database that holds information containing employee information, departments, office locations, and managers.

I need to segregate the employee information by type of employee such as full-time employee, temp employee, contractors, and interns. I want to be able to create layouts for each type of employee and also be able to create an entire list of employees. I'm not sure if I should set them up using different tables and relationships for each one or not.

Any suggestions on how I should go about this?

[Vaughan]

Employee type need only be a field.

[emcclure]

But, if I just have it as a field how do I separate different employees to different layouts? Also, not all of the fields are the same for the employee types. For instance, I will need to track contract limitations for contractors and not for employees, or start and end for interns and not employees.

[Vaughan]

Script the process of navigating to the "different" layout and the script can select the correct layout based on the value in the type field.

[emcclure]

I'm now having another issue. I have created a Departments table in my database. When I try to create a relationship between my Employee table and Department table it gives me a many to many relationship and doesn't let me pull information from the employee table. How can I fix this? I know that I need a join table for many to many relationships but I cant think of a feasible join table.

[comment]

Can one employee belong to more than one department?

[emcclure]

No.

[comment]

Well, then you have a one-to-many. One thing you should make sure of: do not use the department name as the matchfied for the relationship. Names have a nasty habit of changing from time to time. Use a a DepartmentID instead. In the Departments table, set the field to auto-enter a serial number.

[emcclure]

I initially thought it was a one to many relationship but in FileMaker it in the relationship window it shows a many to many relationship. I have used DepartmentID as the fields I use to make my relationship between the Department TO and the Employee TO, but when I'm in the Employee layout and I select a department, it doesn't show in my portal in the Department layout.

[comment]

I initially thought it was a one to many relationship but in FileMaker it in the relationship window it shows a many to many relationship.

See:

http://fmforums.com/forum/showtopic.php?tid/216449/post/364457/#364457

when I'm in the Employee layout and I select a department, it doesn't show in my portal in the Department layout.

Are you sure you are selecting the department by DepartmentID?

[emcclure]

Yes. I set-up DeparmentID field (foreign key) in my Employee layout that has a drop down menu that shows a value list from my DepartmentID field (primary key) displaying values from the DepartmentName field.

But for some reason its not transferring over to the Department Layout portal I set-up to display all the employees in that department.

[comment]

Based on your description, it should work. Why don't you post a copy of your file (preferably simplified to show only the problem)?

[emcclure]

I don't know what I did differently, but it suddenly started working. Probably something to do with my relationship graph. Thanks so much I'm all set now!