1 Reply Latest reply on Jan 15, 2010 9:58 AM by philmodjunk

    Show fields from another table that match different fields



      Show fields from another table that match different fields


      I have a table (called Employee Records) with employee name and interviews they attended. Same employee has attended five different interviews at five different locations. In the employee table these are stated as Location 1, Location 2 etc.


      The Location field is looked up from another table (called Locations) containing the location name, address, city, post code etc.


      On a new layout I wish to print a list of the employee name, Location name (from Location 1) and the matching address & city (from table Locations). Then on the second line Location name (from Location 2) and the matching address & city (from table Locations) and so on for all five locations. If say locations 4 & 4 are blank then the address & city will be blank.


      I have managed to link the relationship between Employee Records table and Locations table with Location 1 and Location Name. But I cannot create any relationship between Location 2 and Location Name because it only allows on field to have relationship between both tables.


      How do I get the address & city for say Location 2 when I cannot link? I do not want to create any more fields in table Employee Records that look up Address 2, City 2 etc because these are only to print the list rather than for any other use.

        • 1. Re: Show fields from another table that match different fields

          "it only allows one field to have relationship between both tables."

          Actually you can do this, though creating an additional table to join employee records to location records instead of a series of location fields would be much easier for you to work with.


          First to explain how to link a second field to an already related table so that you have two relationships instead of one relationship with two pairs of fields:


          You probably have the following in your relationship graph:

          Employee::Location 1 = Locations::Location Name


          Click on the Locations box (this box is called a table occurrence) to select it.

          Click the button with two green plus signs to make a copy of this table occurrence. Filemaker will name this Locations 2, but you can change this name if you want.

          Drag from Employee::Location 2 to Locations 2::Location Name


          You now have two relationships linking the data source tables Employee and Locations using two different table occurrences, Locations and Locations 2.


          A better approach to make your database easier to work with:


          Define a new table, Employee_Locations, with two fields:




          Link it in between the Employee and Locations table:

          Employee::EmployeeID = Employee_Locations::EmployeeID

          Locations::Location Name = Employee_Locations::Location


          Now you can base portals and reports on the Employee_Locations table and you no longer need multiple location fields in your employee table.


          You can place a portal to Employee_Locations on your employee layout and add fields from the Locations table to show address etc once a location has been selected.

          You can then build your report on a layout based on Employee_Locations and add fields from both Employee and Locations to provide the info you need.


          Note: you might also use a Location ID number instead of a name in these relationships. Locations can change names and if that happens, changing the name in your Locations table will break these connections.


          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

          Table vs. Table Occurrence (Tutorial)