7 Replies Latest reply on Aug 17, 2009 4:55 PM by philmodjunk

    Best Way To Easily Replace Multiple Data Fields

    NeoTekCorp

      Title

      Best Way To Easily Replace Multiple Data Fields

      Post

      Okay, I've received excellent assistance here before and now I was hoping to get a clear and concise solution to the following scenario. I'm consolidating a couple business listings and am finding it tedious to organize things uniformly. Here is the situation...

       

      I have three fields

       

      Field #1: old_BusCode

      Field #2: Category

      Field #3: Subcategory

       

      I've set up a second database with all the category/subcategories in them. Now, as I import the old businesses I need an easy way to perform a find for a particular "old_BusCode" and then be able to enter the Subcategory from a drop down list which would automatically populate the related Category field.

       

      I had a few thoughts on this but figured I'd throw it out here to those with more experience and wisdom on their side.

       

      Regards,

       

      Adam 

        • 1. Re: Best Way To Easily Replace Multiple Data Fields
          philmodjunk
             Check out "Replace field contents". This tool can put the same value or a calculated result in a specified field of every record in the found set. You can do this manually or in a script.
          • 2. Re: Best Way To Easily Replace Multiple Data Fields
            NeoTekCorp
              

            Thanks for the advice. I've used the find/replace feature quite well on other data but I'm running into a hurdle to easily get the category & subcategory fields to populate when replacing just one of them. Any tips as I know there is an easier way.

             

            Basically here is the "manual" process I've used...

             

            Step One: Sort data by the "old_BusCode" field

            Step Two: Perform find on one of those to only show all the records (ex: 32175 in that field)

            Step Three: Fill in one of the records "Subcategory" field with the appropriate one and then use the find & replace to populate all found records

            Step Four: Do the same thing to populate the "Category" field on all the found records

             

            Isn't there a better way? 

            • 3. Re: Best Way To Easily Replace Multiple Data Fields
              etripoli
                

              If your DB was linked to the new database by the Subcategory field, and Category was setup as a lookup from the new database, it should populate the Category field whenever you change the Subcategory field.

               

              Although, it seems backwards to populate Category by first filling in the Subcategory...

              • 4. Re: Best Way To Easily Replace Multiple Data Fields
                NeoTekCorp
                  

                Hello Etripoli,

                 

                Thanks. I was actually fooling around with the relationship/lookup and got it to work that way. It doesn't seem like the most efficient way to do this but it is effective and fairly quick.

                 

                Regards,

                 

                Adam 

                • 5. Re: Best Way To Easily Replace Multiple Data Fields
                  philmodjunk
                    

                  NeoTekCorp wrote:

                  Thanks for the advice. I've used the find/replace feature quite well on other data ....


                  Find/Replace and Replace Field contents are two different tools.

                   

                  Replace Field contents is found in the Records menu and Find/Replace is found in the File menu.

                   

                  Replace Field contents allows you to put the same value, a calculated value (which can then be different for every record), or a serial number value into a specified field of every record in the found set.

                   

                  You should be able to build a script that does what you need quite easily.


                  • 6. Re: Best Way To Easily Replace Multiple Data Fields
                    NeoTekCorp
                      

                    Hello PhilModJunk,

                     

                    You'd think I could whip together a script pretty easy but that isn't my strength. Any chance you could "whip" one together that I could implement??? IT'd be much appreciated and it'll save me hours of manually going to each "old code", finding all records with that, updating the category/subcategory for the first record and then using the replace field contents to populate the rest.

                     

                    Regards,

                     

                    Adam 

                    • 7. Re: Best Way To Easily Replace Multiple Data Fields
                      philmodjunk
                        

                      I re-read the thread and don't see enough info to tell if you can automate selecting the original Category, and SubCategory information. Let's assume you have to do this manually.

                       

                      Set up a table, "Updater", with three fields:

                       

                      BusinessCode, Category, SubCategory.

                       

                      Set the following validation rule on BusinessCode: Unique values, always validate.

                       

                      Now use import records to copy all the records from your existing table into this new table. (you'll need to map your "old business code field" to match BusinessCode.) The above validation rule will only accept the first instance of each Business Code, thus filtering out duplicate values. Now hand enter the appropriate Category and SubCategory values for each of these new records.

                       

                      Define a relationship matching YourTable:: OldBusinessCode = Update::BusinessCode

                       

                      Now you can script the conversion or do it all in two big manual Replace fields operations:

                       

                      As a script:

                      Show All Records

                      Replace Field Contents [no dialog; Yourtable::Category; Update::Category]

                      Replace Field Contents [no dialog; Yourtable::SubCategory; Update::SubCategory]

                       

                      Note:  Make a copy of your file first so you can try this again if you don't do it right on the first try.