4 Replies Latest reply on Mar 7, 2009 1:03 PM by ninja

    Basic Relationships

    carlz_1

      Title

      Basic Relationships

      Post

      Using FM 10.

       

      I am still new to this and am having a difficult time with Relationships.

       

      I have 2 tables, Applicants and facilities.  I want to be able to choose a facility by name and have the name, address, city state and zip automatically populate when I choose a facility name from a drop down menu.  I am able to get the drop down menu of the facilities to work fine but the address, city state and zip do not populate.  I use the structure _kp_appl for the primary key of the applicant table and _kf_appl for the foreign key.  The same is true for facilities, _kp_facil and _kf_facil.

       

      I am starting to understand this...  I think the issue is what table the layout is based on and whether I connect the primary to the foreign.  What i am doing now that does not work is the layout is based on the applicants table and the _kp_appl on Applicants is connected to _kf_appl in the facilities table.

       

      If anyone can help or give me a rule of thumb to do this properly, i would and do appreciate it very much.

       

      One other unrelated question, is there any way to show a field on a layout but disallow changing it?  I know i can use merge fields but those don't truncate the field name in layout mode and it makes it difficult to place them when they are aligned close to one another.

       

      Thank you.

       

      Carlz.

        • 1. Re: Basic Relationships
          ninja
            

          Howdy carlz,

          Thanks for the post.

           

          I think the issue is what table the layout is based on YES and whether I connect the primary to the foreign DEPENDS....  What i am doing now that does not work is the layout is based on the applicants table GOOD and the _kp_appl on Applicants is connected to _kf_appl in the facilities table.

           

          1. The dropdown for facility must be on the linking field in the Applicants table in order to link over to your facilities table.  It must be linked to the facility name field (or facility IDcode field, whatever you use) in the facility table.

          2. The Add, City, State, etc. on the layout must be from the Facility table.

          This way when you make the link by choosing the facility in the linking field, the information in the other fields will "come over" via the link.  The data will not really move, but FMP will now know what record in the facility table you want info from.

           

          Does this make sense?

          • 2. Re: Basic Relationships
            carlz_1
              

            Thank You!  I am starting to "Get it" and that worked.  Except that when I manage thevalue list, I tell it to attach to _kp_facil from _kf_appl and then when I tell it to show only data from a second field (FACIL::name) the primary key  (FAC0000000012) shows up, not the second field.  

             

            Any ideas on that?

            • 3. Re: Basic Relationships
              davidhead
                

              carlz wrote:

              Thank You!  I am starting to "Get it" and that worked.  Except that when I manage thevalue list, I tell it to attach to _kp_facil from _kf_appl and then when I tell it to show only data from a second field (FACIL::name) the primary key  (FAC0000000012) shows up, not the second field.  

               

              Any ideas on that?


              That is expected behaviour - the value list will insert the first value from the list into the field. The second field is only there for information.
              If you want to see the related name after the key has been set, you have a couple of options.
              One is to use a pop-up menu. This has some interface drawbacks but it will retain the display of the second field even though the field contains the key data.
              The second option is to overlay an opaque related field over the field you are entering data into. In your case, this is the FACIL::name field. Change the field behaviour so that it is not enterable in Browse mode.
              There is also another method of setting the key in the first place but I will leave you with that for now. 

               


              • 4. Re: Basic Relationships
                ninja
                  

                Howdy carlz,

                 

                Glad to hear it's working...lets work out the drop down details.

                 

                In your "Define value list", you would select the facilityID field from the Facilities Table on the left side (assuming that yours is laid out like FMP8 or 9), select "Use all values", DO NOT check "Include only related values".

                On the right side of that same subwindow, select "Also display values from second field", select "Facility Name" field from the Facilities Table and check "Show values only from second field"

                 

                That should now list out the facilities from the facilities table (where I'm assuming you have one record per facility).  The value list should be attached on your layout to your field which is Facility ID from the Appl. Table.

                When you click on the field, you'll get facility names in the dropdown.  When you select one, however, you'll see that the facilityID is what actually gets enered into the field.  To avoid confusion, you may want to add the Facility::FacilityName field onto the layout so that you can see the facility name. 

                 

                Does that finish the job, or did I miss a piece?

                 

                ULearIT beat me to it by a minute or two...and he is right as usual...Thanks!