4 Replies Latest reply on Oct 31, 2015 12:05 PM by MrsEaves

    Value List  and Imported Records

    MrsEaves

      I have a table of technologies. Each technology has a unique ID that is a auto-generated serial number (__pkTechnology) and a name (TechnologyName).

       

      I am using those pieces of data to populate a value list, where __pkTechnology is the first field and TechnologyName is the second field. I am only showing values from the second field.

       

      This value list is used in a pop-up menu on a second table, where I am indicating technologies used for a given product. Each product can use multiple technologies. Each one is stored as a separate record (with a bit of additional data).

       

      Most of my data is imported from Excel files in big chunks of a couple thousand records at a time. I can have it validate that each TechnologyName imported is on the value list. After import, I notice that when I click on the pop-up menu for an imported record, it does not jump to the name on the list that is displaying in the field. it just goes to the top of the list (e.g. the field reads "Vibram" but when I click on the field it goes too "500down", which is the first item on the list). I can scroll through and find the correct name and select it. Then it will jump to that name in the list the next time I click on it.

       

      I want to be able to change the TechnologyName on the first table and have it ripple through all of the entries on the second table, without having to go through thousands of records manually and select names from the value list. Right now, the only TechnologyNames that automatically update are the ones which I have manually selected from the pop-up menu.

       

      Any thoughts on how to correct this?

      Thanks!

        • 1. Re: Value List  and Imported Records
          erolst

          You have

           

          Technology --< ProductTechnology >-- Product

           

          and are importing into ProductTechnology?

           

          If I understand correctly, you're importing into ProductTechnology, and mapping the technology name from the Excel file to a field that is supposed to actually hold a technology ID – regardless of what the popup reads (due to “Show only values from second field”), the field holds the value from the first field of the value list.

           

          That means you need to either import such an ID, or use a separate field for import to drive a relationship by technology name to lookup the associated ID. Then find all freshly imported without a technology ID – here either the technology name is misspelt, or that technology doesn't have its own record yet.

           

          MrsEaves wrote:

          I want to be able to change the TechnologyName on the first table and have it ripple through all of the entries on the second table, without having to go through thousands of records manually and select names from the value list. Right now, the only TechnologyNames that automatically update are the ones which I have manually selected from the pop-up menu.

           

          The actual problem seems to be as described above. Once that is corrected, and you're using a foreign key as a reference, then any such change in the parent will be reflected automatically in the related child/join records. That is piece and parcel of a related database system …

          • 2. Re: Value List  and Imported Records
            MrsEaves

            erolst Thank you for your response. It took me a minute to wrap my head around what you were saying, but now I get that even though my field is displaying the TechnologyName, it is actually holding the __pkTechnology unique ID.

             

            Now, can you help me understand how I would create a field that uses the imported TechnologyName to lookup the correct corresponding __pkTechnology ID? (It won't be possible to have that ID as part of my import.)

             

            Thanks for your help!

            • 3. Re: Value List  and Imported Records
              erolst

              Add a text field like technologyName_import; create a new table occurrence of the Technology table,say, Technology_byName, and a new relationship where

               

              ProductTechnology::technologyName_import = Technology_byName::technologyName

               

              Then define an auto-enter option for ProductTechnology::__fkTechnology as

              Technology_byName::__pkTechnology

              • 4. Re: Value List  and Imported Records
                MrsEaves

                Thank you! You have been incredibly helpful!