3 Replies Latest reply on Nov 18, 2010 10:01 AM by philmodjunk

    Lookup, Then Change

    pvolkmar

      Title

      Lookup, Then Change

      Post

      How can I use a lookup to fill the fields in a table, then change the type of field but keep the looked up data in the field?  I'm setting up a new database and am looking up data that will, in new records will be manually entered.

        • 1. Re: Lookup, Then Change
          philmodjunk

          Looked up value fields can be edited after the fact. They are just data fields with an extra field option specified.

          If this is s one time operation, you can use Replace field contents or even an Import Records operation to copy the data over.

          • 2. Re: Lookup, Then Change
            pvolkmar

            Nope; When I make a new field then fill it by replacing with the contents of the looked up field, the replacement uses the related data that was the root of the lookup field.  In other words, I have filled the field ZIP with zip codes from another table using a relationship that relates a field called "CityState" to the zip code for that city.  When I fill a new field by replacing with the contents of ZIP (which contains a looked up zipcode) it is filled with the text "CityState" that was the root of the lookup.

            There's something I'm not understanding here.  I can get the ZIP field filled OK, but changing or deleting the relationship used to fill it,afterward changes or deletes the data in the field.

            • 3. Re: Lookup, Then Change
              philmodjunk

              Sounds like you are either using a calculation field  instead of a looked up value.

              In manage | database | fields, create a new field of type text, or number for you zip codes. Double click the field definition or click the options button to bring up Field Options. In Auto-enter, use the looked up value option to copy the data over from the related table's zipcode field whenever the value of the tables match field for this relationship is modified. This data will remain after you delete the relationship.

              Likewise, if this is a data field and you need to do this just one time, make sure your field is a simple data field such as text or number--not a calculation field. You can use Replace Field Contents with the calculation option to pull in data from a related record. Since this just copies data from the other table (much like the looked up value option in the first paragraph), the data may be edited later and removing/changing the relationship between the tables will not modify the contents of this field.

              If you change a calulation field to a data field, the current value of the calculation is stored in the field as data--so this might also be an option for you to try out.

              With all of these options, make a back up copy before proceeding so that if you get unexpected results, you can revert to the backup copy and try again.