6 Replies Latest reply on Nov 6, 2009 6:56 AM by theFox

    Lookup from same table



      Lookup from same table




      I am new to FileMaker Pro, and have a basic understanding of a relational database.  I work on the MAC and PC version.  I am busy creating a Personnel dB.  In my Personnel table I have:


      First Name

      Last Name



      I want Manager to be a drop down of names from the Personnel table.  Users should be able to select the manager for a new Personnel record from existing personnel records.  I want to show the First Name and Last Name in the drop down somehow. Should I create a separate table called Manager and link these tables?  What is the most elegant way to do this?



        • 1. Re: Lookup from same table

          Hello theFox,


          The most elegant way depends greatly on the solution itself. Here is a good basic method:


          1. Create a field that contains the personnel category. It should contain a value list of categories (example: employee; manager; CEO, etc.).


          2. Create a calculation field that stores the first and last names only if their category is manager.


          3. Create a value list that is based on the first and last name field created in step 2.


          4. Attach this value list to the Manager field you mentioned in your original post.


          That's it! 

          • 2. Re: Lookup from same table

            I'm assuming Manager stores a number.


            Create a self join relationship.


            In Manage Database | Relationship, Drag from Personnel::ID outside the box and then back in to Personnel::Manager. Name the new table occurrence ManagerTO. You can use this relationship to show the manager's name and any other data on a regular person's layout.


            Define a calculation field to concatentate first and last names--you'll need this since Filemaker only allows you to show data from two different fields in a value list and the other field will show the Manager's ID number. cFullName = FirstName & " " & LastName  // you can change this to show Last, First if you prefer


            Define a text field, IsManager; and format it with a check box to show "Manager". Click this check box on every manager record to identify them.


            Define a calculation field : cManagerID = If ( not isEmpty (IsManager) ; ID ; "" )


            Now set up a two column value list.

            In manage value lists, click the Specify FIeld option and select cManagerID as the first field and cFullname as the field for the second column.


            Format your manager field to use this value list in either a pop up or drop down menu. If you choose a drop down menut, add the cFullName field from the ManagerTO so you can see the Manager's name after you exit the drop down.

            • 3. Re: Lookup from same table

              Thx PhilModJunk


              I used your detailed reply to figure out calculated fields and used David's flow to create only calculated manager full names when a manager checkbox was selected.





              • 4. Re: Lookup from same table

                Hi David


                My lookup for manager works fine, and the Manager field now contains the Personnel ID of the manager (cManagerID).  This is great since I can then modify the name of the manager without any issue.  If I stored the managers name (cManagerName) in the Manager field, it would not change when someone modifies the first or last name of the manager's record.


                Next problem that I face is to display the manager's full name, rather than the ID.  I played around with the pop up settings on the Manager field, but .....


                Personnel ID 38 

                FirstName Manager 

                LastName One 


                cManagerName Manager One 

                Manager  x

                cManagerID 38 


                Personnel ID 41 

                FirstName A 


                Manager 38 





                Any suggestions?

                • 5. Re: Lookup from same table

                  Hello theFox,


                  The decision to store the manager ID is indeed the approach to take if you want to propagate changes made to a manager's name. This means, however, that the control you use to display a manager's name is more complex.


                  A Pop-up Menu field can do the trick, but it requires the value list to be set in a special way:


                  1. In your value list, select the option "Use values from field:".


                  2. Click on "Specify field...".


                  3. In the "Use values from first field" panel, select the manager ID field.


                  4. Click the "Also display values from second field" check box and select the manager name field.


                  5. At the bottom of the window, click the "Show values only from second field" check box.


                  6. In "Sort values using:", select the "Second field" radio button.


                  That's it! Pop-up menus do not display the first field information when a value list is configured in this way. The first field information is still stored in the field (you will see this if you create a regular edit field, for example) however its display value is tied to the second field.


                  There is another way to do this, but it requires relationships and is more complex. If you want to get into deeper programming techniques, I recommend you pick up a good book, get yourself a coach or trainer or take some classes (or all of the above). D-Cogit does coaching and training. You can find other resources on FileMaker's WEB site. In the short run, it may seem expensive. In the long run, if you will be programming a lot, it save you money in time. 

                  • 6. Re: Lookup from same table

                    Thanks David


                    I will try this.  I have played around with this, but I could have easily changed too much.  I have just ordered a e-book.