2 Replies Latest reply on Aug 11, 2011 12:51 PM by FMNewbie

    Lookups, photos and repeating fields

    FMNewbie

      Title

      Lookups, photos and repeating fields

      Post

      Okay, I'm sure there's a better way to do this, but I have one table called EMPLOYEES with records including NAME and a container field for their PHOTO.  In another table, we can call it INCIDENTS, I would like to enter several employee names and have their photos pop up in a corresponding container field.  The two tables are related through the NAME field.

      I have been trying to do this using two repeating fields of [10] values and various permutations of the LOOKUP function, but I'm going in circles. 

      Any suggestions?

        • 1. Re: Lookups, photos and repeating fields
          philmodjunk

          Neither repeating fields nor look up seems like a good idea here...

          If you define the container field for each Employee in the Employees table, you can store one photo per employee which seems like what you need here. You should already have an ID field in Employees that uses a serial number to uniquely identify each employee. If not, you should add one and use Replace Field Contents to update your existing Employee records with serial numbers. You should also have an ID field in Incidents to uniquely identify each Incident.

          Since an incident can involve many employees and an employee can be involved in many incidents, this is many to many relationship and you need a join table so that you can link employee and incident records without duplicating data.

          Define a table called Employee_Incident. One record in this table will link one employee to one incident.

          Define these relationships:

          Employees::EmployeeID = Employee_Incident::EmployeeID
          Incidents::IncidentID = Employee_Incident::IncidentID

          A portal to Employee_Incident on the Incident layout can be used to link that incident to any number of employees. Format Employee_Incident::EmployeeID as a drop down or pop up with a value list of EmployeeID in field 1 and their name field in field 2. You can use this list to select an employee by name, but the value list enters the ID number. You can add the container and name fields from Employees to this portal to display names and pictures. You can also just use this portal set up for linking the records, but use a list view layout based on Employee_Incident to produce a report of your incident. You'd perform a find or use Go To Related Records to pull up just the join records associated with a specific incident to get the right records for this report.

          Here's a link to a demo file that matches "contracts" to "companies" in such a many to many relationship: http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          PS. I wouldn't link employees by name. Names are not unique and people change their names. Using unique ID numbers avoids such problems.

          • 2. Re: Lookups, photos and repeating fields
            FMNewbie

            I guess I don't know enough about portals yet.  It seems I have some reading to do.  :)

            Thanks for the info!