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?

Employee type need only be a field.

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.

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.

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.

Can one employee belong to more than one department?

No.

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.

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.

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?

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.

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

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!