I'm not totally clear on what you have set up and exactly what you want to see happen.
2 Tables: Shifts and Employees
You have one record in employees for each employee. What do you have in shifts? One record for each shift worked by an employee or something else?
Are they related like this?
Employees::EmployeeID = Shifts::EmployeeID
Or do you have some different relationship in place.
for every type I have a field (1,2,3,4) in Shifts table.
Is this a repeating field with 4 repetitions? You may need either 4 fields or a related table of 4 records here. Why do you need 4 repetitions instead of just one field here? (Perhaps I am misunderstanding the purpose of your shifts table.)
I think you want to set up each employee type (A, B, C, or D) so that only the type of shifts appropriate for that type are listed in your value list.
The more I look at this, the more I think that you need another table. Something like this:
Shifts would have one record for each shift in your plant's work schedule. Employee_Shift would be used to assign a given employee to one such shift for a specified time period.
Then a check box field can be defined in Shift with the 4 employeeType values listed. Call that field "EmpTypeList". For a given record in Shifts, you can then select all the employee type fields for which it applies. Then you can select Shifts in Manage | database | relationships and use the duplicate button (has two green plus signs) to make a new table occurrence box. It'll come up as Shifts 2, but you can double click it to rename it. Then use it to create this relationship:
Employees::EmployeeType = ShiftsByType::EmpTypeList
Now you can define a value list of Shift numbers by using the specify field option. List values from ShiftsByType, not Shifts and then select the "include only related values" option, selecting Employees as the "starting from" table.
Thank you for answering.
I understand that you see this as a many to many relationship, that needs to be broken down into two one to many relationships.
The Employee_Shift table is like a "Shift_Line" table (similar to Order_Line)? If you don't mind, I would like a little more elaboration on the solution you offered above. I sort of lost the part with the check box.
Would you have any suggestions on the steps to "merge" tables via one to one relationships (question 2 above)?
Thank you again for your time.
You need a third table so that you can set up which shifts are appropriate for each employee type. Your current shifts table is being used like I would use the Employee_Shift table. Whether you need it related like I have it or just the occurrence I specified for the conditional value list, depends on what you need to do here. A separate shifts table could be used to document all details relevant to each individual shift on your schedule, but you may or may not need that capability here.
I'll assume that your employee types are really A, B, C etc, not Office, Management, Supervisor, Union.... as those are the only values you've given here. I'm also sticking with the table names specified in my last post so you'll need to subsitute my names for yours here.
In the shifts table, define a text field and name it EmpTypeList. Place this field on a layout based on Shifts and use the inspector to format it as a check box set instead of an edit box. Click the pencil icon next to "values from" drop down to create a custom values value list. Enter the values A, B, C, D on separate lines in the custom values box. Now use this field in your relationship. WHen you use a list of values separated by returns like this in a relationship, the relationship will match records if any one of the listed values matches the value in the key field specified in the related table. Thus, if You click both A and B check boxes for Shift 1. Then shift 1 will appear in the conditional value list for both Type A and Type B employees.
Here are some links on conditional value lists you can use to learn more. The demo file includes a check box field used like I've described here.
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
For Question 2, you need only one table for all employees. If you have several tables at this time and real data is stored in each (not just data to test your solution), you should use Import records to import the data from the other tables all into one combined table and then you should remove the other tables from your database.
Thank you again!
I've been working on this all day. It all worked out in the end.
Your advice was very helpful indeed.
Until next time..