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

    Lookup from same table

    theFox

      Title

      Lookup from same table

      Post

      Hi

       

      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:

      ID

      First Name

      Last Name

      Manager

       

      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?

       

      Thx 

        • 1. Re: Lookup from same table
          david_lalonde@d-cogit.ca
            

          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
            philmodjunk
              

            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
              theFox
                

              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.

               

              Appreciated

               

              theFox 

              • 4. Re: Lookup from same table
                theFox
                  

                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 

                Manager 

                cManagerName Manager One 

                Manager  x

                cManagerID 38 

                 

                Personnel ID 41 

                FirstName A 

                LastName 

                Manager 38 

                cManagerName 

                Manager 

                cManagerID 

                 

                Any suggestions?

                • 5. Re: Lookup from same table
                  david_lalonde@d-cogit.ca
                    

                  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
                    theFox
                      

                    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.

                     

                    thx

                     

                    theFox