4 Replies Latest reply on Nov 2, 2011 11:32 AM by user15130

    Data Base Find and Replace



      Data Base Find and Replace


      If I have 3 fields that I want to do a find and replace on what would the case or if statment look like.  I have about 30,000 records and it needs to do the following:


      Field 1 is now Doug and I need it to be replaced by Jonh

      Field 1 is now Matt and I need it to be replaced by Chris

      Field 1 is now Steve and I need it to be replaced by Tony

      I want to script it so I can do it each week.  Each week I do an import of data and then I want to run this script to make the necessary find and replace.


      Thank you




        • 1. Re: Data Base Find and Replace

          What, in your data, tells you which values to replace with which? Need to know that in order to set up the script.

          Replace Field Contents can be used to do these kind of batch updates. If you first perform a find to pull up just the fields to be changed, this can be done more quickly by performing the operation on a smaller set of records.

          • 2. Re: Data Base Find and Replace

            Actually now that I think about it, it is one field that has several different names in it.  They are always the same.  There are 6 names but the six are not what I need so I want to replace with another name.  Its a zone, so its coming in with WA OR AL and I need it to say North West then go to the next set of names like CA NV and replace with South West.


            Sorry for the previous bad example.  Trying to make it simple.




            • 3. Re: Data Base Find and Replace

              The method is the same either way, as long as the naems are listed in exactly the same order in each case. WA OR AL in one record and AL WA OR in another will make this much more complicated, but it can still be done.

              You can use the Replace Field Contents with the calculation option to update all newly imported records. Us a calculation like this:

              Case ( YourTable::Field = "WA OR AL" ; "Northwest" ;
                        YourTable::Field = "CA NV AZ" ; "Southwest"
                       /* else */ yourTable::field
                      ) // case

              This depends on an exact match of the values in the field to the text in quotes. The last clause (else) insures that if there is no match at all, the data is left unchanged. I've assumed a space between each character. If this is a return delimitted list of values use ¶ between each listed value. If the order is not consistent or some values are omitted, a more sophisteicated approach can be used.

              You can also keep your original field unchanged and just define a calculation field with the above calculation, using it to display your regional names instead of physically changing the data in this field.

              • 4. Re: Data Base Find and Replace

                Perfect. Thanks a lot. :)