1 Reply Latest reply on Sep 4, 2013 10:29 PM by philmodjunk

    Related records - am I missing something?



      Related records - am I missing something?


           I'm really new to FMP so I think I'm missing a major chunk of knowledge that will help me solve my problem.

           I understand how to create relationships in the database, but I'm having some confusion about actually creating the related records with the user being able to reference a related record by name, not by actually having to choose a serial number. In other words, I want to hide the auto generated serial numbers, but still enable the user to create related records.

           I'm not sure if that makes sense, so I'll give a simple example.

           Let's say I have two tables, "Companies" and "Employees". Each company has many employees (a one-to-many relationship). So in my Companies table, my fields could be "CompanyID" and "CompanyName". In my Employee table, my fields could be "EmployeeID", "EmployeeName", and "CompanyID". Like this:


      •           CompanyID
      •           CompanyName


      •           EmployeeID
      •           Employee Name
      •           CompanyID

           Then I create a relationship between Companies::CompanyID and Employees::CompanyID.

           But then, while the user creates a new Employee record, they will have to choose a company ID (serial number) to relate that employee to? i.e., if I'm creating a record for "Joe", I have to enter it so Joe works at company "3219" instead of "Microsoft"? Is there some way to relate the records without the user having to choose the serial number? I feel like I'm totally missing something, but I just don't see the answer.

           Help would be much appreciated. Thank you.


        • 1. Re: Related records - am I missing something?

               First, the simple solution but not necessarily the best:

               Define a value list for your Company ID field in Employees. Select the "use values from a field" option and specify Company ID from the companies table for the first field. Then select the "also use values" option and specify the Company Name field as field 2. Now, the users can see the company name field in the drop down or pop up menu when they deploy the value list, but the value list enters the ID number. If you specify Pop Up Menu and choose to hide the first field, the users only see the company name.

               For a drop down list, similar results can be obtained with a bit of layout sneakiness by hiding the drop down list behind the Company Name field from the Companies table. The name field has to be set in the Inspector's behavior settings to deny access while in browse mode for this to work.

               Please note that there are also a number of more sophisticated approaches that can make working with larger lists of values easier than this basic approach. But since they build on this basic set up, it's a good idea to get it working first before trying to implement a more complex alternative.