2 Replies Latest reply on Feb 16, 2012 10:09 AM by ahcho

    Simple table relationship question



      Simple table relationship question



      Here's my question:

      _kf_EmployeeID and __kp_EmployeeID are related through an auto-serial number. So on the 'Some Layout' page, with the _kf_EmployeeID field, I can do a drop downlist with a valuelist tied to __kp_EmployeeID. My layout will look like this:




      Employees::Phone #


      By selecting the the values from the _kf_EmployeeID drop down menu, the related fields change accordingly. However, since the valuelist is based on auto-serial numbers, it would be difficult for the user to remember that "1" is actually Bob.

      But, if I make the drop down value list based on Employee::Name (which would be more meaningful to the user) the relationship breaks down and i don't see the related fields. I understand why that's the case.

      How do I allow the user to choose a name from the Employee table without resorting to picking from auto-serial numbers but still maintains the relationships based on the auto-serial numbers between the 2 tables?


        • 1. Re: Simple table relationship question

          This isn't actually a "relationship" problem. It's a value list problem.

          Open Manage Value List select your value list, and edit it. This should be a "Use values from field" value list. If not, select that option. In the next dialog you see, you get two boxes for "field 1" and "field 2". You can select the ID number for field 1, but then can select a name field for field 2. If you have a first name and last name field, you can define a calculation field that combines them and then select this calculation field for field 2. Now your value list will list both ID numbers and their names. Thus, you can select an employee by name, but the value list enters the ID number.

          There is an option you can select to hide the ID numbers so that only names are displayed in the value list. Do not select this option unless you also take steps to make sure that the employee's full name is unique. Otherwise, cases where you get two employees named "john smith" for example, only one will be listed in your value list when this option is specified.

          This is the simplest way to do this. It has some built in limitations that make it better suited for fairly small lists of values. There are a number of more advanced techniques that can be used that make working with large value lists easier to work with.

          • 2. Re: Simple table relationship question

            Always so simple! Thanks Phil!