4 Replies Latest reply on Sep 29, 2011 3:46 PM by MeaCulpa

    Value list content filtering - One to one relationship.



      Value list content filtering - One to one relationship.


      Can someone please help me.


      Question 1:


      2 Tables: Shifts and Employees

      We have four types of employees(A,B,C,D). 

      I want to keep track of their shifts, and for every type I have a field (1,2,3,4) in Shifts table.

      I want to have a pop up field that will display a list of employees of only the one particular type on each of the 1,2,3,4 fields.


      Tried using the following:

      In table "shifts" created four calculation fields resulting "A","B","C","D". In the Relationship Graph I used 4 multipredicate relationships ( Employees:kpEmployees = Shifts:kfEmployees  AND   Employees:type = Shifts:cA.....cB........cC........cD) in multiple table occurences. [My idea was to match A with A, B with B etc.]

      Then to populate the 1,2,3,4 pop up fields I created value lists, one from each table occurence, but either did not come up with any results (show only related records) or got back the full employee list (show all records).


      Any ideas?


      Question 2:


      To solve the above, I ended up having four tables (one for each type of employee), which works fine, but now I have a problem creating a united employee table with one to one relationships.


      Tried doing the following:

      I connected directly EmployeesA:kpEmployeesA = EmployeesAll:kpEmployeesAll (had no pigs feet), but the EmployeesAll table was not updating as I entered records on the EmployeesA table. (initially both kp's were auto serials, but then tried only EmployeesA as an auto serial and still nothing).


      Can you please guide me step by step (literally) on how to make this united table work (one to one relationship)? 


      Any ideas for either question (preferably both for future reference)?


      Thank you.

        • 1. Re: Value list content filtering - One to one relationship.

          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.

          • 2. Re: Value list content filtering - One to one relationship.

            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. 

            • 3. Re: Value list content filtering - One to one relationship.

              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

              Demo File: http://www.4shared.com/file/f8NsU2DJ/ConditionalValueListDemo.html

              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.

              • 4. Re: Value list content filtering - One to one relationship.

                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..

                Good day!