9 Replies Latest reply on Jan 24, 2012 10:25 AM by pademo57

    Storing Values in Related Tables

    pademo57

      Yet another Newbie question. I have taken over a project from someone who left the company a year ago and everything has been going fine until now. So I'm still learning FM Pro.

       

      Our company takes on projects in various places around North America. When an Employee is put on a new job in a new city we need to change his location.

       

      Currently when you click on an Employee Layout Form View, there is a drop-down box which allows you to change the location. In the drop-down box is the Work Location ID and the Work Location Name. After clicking on the new location the box changes to the new code and beside it in another field the name of the city as in:

       

      WorkID0001 Vancouver

       

      My new boss doesn't like this. He doesn't like the Work ID showing up on the form. He wants just to click on a drop-down box and have only the name of the city show up.

       

      The Work table primary key is linked to the Employee table foreign key using:

       

      Work:pk_WorkID - Emp:pf_WorkID

       

      How do I get the Work location Name to show up only in the Drop-down box and still have the primary key from the Work table automatically entered in to the Employee table?

       

      I told you it is a "Newbie" question.

        • 1. Re: Storing Values in Related Tables
          Mike_Mitchell

          In the "Specify Fields" dialog, click the "Show values only from second field" checkbox. HTH.

           

          Mike

           

          dialog.png

          • 2. Re: Storing Values in Related Tables
            pademo57

            Hi Mike,

            Thanks for your suggestion, and maybe I should have been a little more clear in what is happening on my form.  I did check the "Show values only from second field" box. But what happens is the drop-down box shows only the Work Location Name but after the user has selected a Work Location, the drop-down box disappears and the WorkID is shown in the box.  Beside this is another field which shows the Work Location Name.

             

            What my boss wants is to have only one field on the form which shows only the Work Location Name.  So I am wondering, how do I keep the two tables related? How do I put the key field info from the Work table into the Employee table if the drop-down box is supposed to only have the Work Location Name?

             

            Am I making this clear as mud??? 

            • 3. Re: Storing Values in Related Tables
              Mike_Mitchell

              It's doable. Do this:

               

              1) Put the field with the ID behind the field with the name.

              2) Set the text color to match the background.

              3) Set the tab order to omit the field with the name (but include the field with the ID and pop-down).

              4) Make a script that goes to the field with the ID.

              5) Make the field with the name a button that executes the script.

               

              What will this do? Your user will see a field with the name in it. When he clicks into it, it'll take him to the ID field, but it will look like he's in the name field. If he tabs, on the other hand, it'll land him on the ID field - which will also look like he's in the name field.

               

              HTH

               

              Mike

              • 4. Re: Storing Values in Related Tables
                jonagnew

                i'm also a newbie and was having a similar problem.  changing the drop-down box to a pop-up menu worked for me.

                • 5. Re: Storing Values in Related Tables
                  pademo57

                  Thanks Mike,

                  I'm sorry to be so pedantic. So if I have this right:

                   

                  Table: Employee

                  pk_EmpID // Primary Key

                  pf_WorkID // Foreign Key

                  Emp_Fname

                  Emp_Lname

                  Etc.

                   

                  Table: Work

                  pk_WorkID // Primary Key

                  Work_Name

                  Work_Loc

                  Etc.

                   

                  On the Employee Layout Form

                   

                  I put the pf_WorkID field behind the Work_Name field

                   

                  Script: GoToWorkID

                  Go to Field [Employee::pf_WorkID]

                   

                  I make the Work_Name field a button and use the Script: GoToWorkID

                   

                  Again, I am sorry if I have to resort to such a basic understanding, but I am truly thankful for your help.

                   

                  Paul

                  • 6. Re: Storing Values in Related Tables
                    comment

                    pademo57 wrote:

                     

                    On the Employee Layout Form

                     

                    I put the pf_WorkID field behind the Work_Name field

                     

                    Script: GoToWorkID

                    Go to Field [Employee::pf_WorkID]

                     

                    I make the Work_Name field a button and use the Script: GoToWorkID

                     

                     

                    There is no need for a script or a button. Just make the Work_Name field non-enterable in Browse mode.

                    • 7. Re: Storing Values in Related Tables
                      Mike_Mitchell

                      Well now. Props to Michael. He's right; clicking the overlying field will put you in the ID field if the field on top is non-enterable.

                       

                      Learned something new. I can go home now.   

                       

                      Mike

                      • 8. Re: Storing Values in Related Tables
                        beverly

                        TIP: You can stack them on the layout, if you want to "hide" the ID field. You can play with tab order as to whether go to either field. You can also make the "name" field (if on top) a button to go to the "id" field (if on the bottom of the stack).

                         

                        I do this when space is precious.

                         

                        Beverly

                        • 9. Re: Storing Values in Related Tables
                          pademo57

                          Thank you all!!!

                          This is working great and I too ... just keep on learning.

                           

                          Thanks again,

                           

                          Paul