4 Replies Latest reply on Feb 2, 2012 2:06 PM by jared944

    Problem with related fields...

    jared944

      Title

      Problem with related fields...

      Post

      Hey everyone,

       

      I have two related fields, the parent is "Locations", the child is "Personel". I have a primary key in Locations that adds an increment with each record and is non-modifiable. I have a foreign key in "Personel" that is plain text and is modifiable. Both of these tables are related and do not allow for creation of records with each other.

       

      The location table is full of addresses, building names, phone numbers and faxes. I want to be able to select the primary key from the Personel table and have that liked together. The only way I can get it to work is if I allow for modification of the Locations primary key, or allow creation of records. When I allow for this it just starts creating more records on the Locations side, which is not what I want.  I don't want to modify or create any records from this side, only select them. Any ideas?

       

      Thanks!

       

      Jared

        • 1. Re: Problem with related fields...
          philmodjunk

          Let's see if I get this straight:

          Relationship:

          Location::__pk_LocationID = Personnel::_fk_LocationID

          You are on a layout based on location and you want to create a new personnel record and link it to the current location record?

          Seems backwards as you would normally create a new record on the personnel layout and select a value in _fk_LocationID to link it to a specfiied location. This could be done, however, with a script.

          • 2. Re: Problem with related fields...
            jared944

            Sorry, reading it it does sound backwards to me, too. Let me be more clear,

             

            I am in a layout based on the Personnel table and want to "import" the information from the Location table. 

             

            Sorry about the confusion,

             

            Jared

            • 3. Re: Problem with related fields...
              philmodjunk

              Ok, that's simpler to do.

              Do not put the primary key field from location on the layout.

              Instead, format the foreignkey field as a drop down list or pop up menu. Define the value list to list the Primary key values from Location in Field 1 and a name field from location in field 2. (Sometimes you have to define a calculation field for field 2 that combines data from several fields.)

              When you select a location by name from this field, the value list enters the matching primary key value and links it to your personnel record. If you place other fields from Location on this layout, they will then display data from the selected location once you have selected a location from the value list. (You can hide the primary key in the value list by clicking "show only values from second field", but if you make this selection, you should also make sure that whatever field you use in field 2 stores only unique values or duplicates in this field will omit the second instance of the same name.)

              This approach with the two column value list is the simplest way to set this up. It's not the only option. As the number of values in the value list increase, either a conditional value list or a script enhanced value selection method often becomes necessary to keep from scrolling through very long lists of values.

              • 4. Re: Problem with related fields...
                jared944

                Excellent! This has been driving me nuts for days...

                 

                Thanks!

                 

                Jared