Advanced Find and Replace

[Lorenz Ehrsam]

How can I find a search term in one field and replace it with another word in another field? For example: My pick list Summary has the word Swimming. I would like to populate 1Swim in the field Quick Glance for all records that list Swimming. Is that possible?

I know I can batch replace Swimming with 1Swim, but is it possible to batch process adding the replacing word to another field?

[Brendan]

Hi Lorenz,

On the Advanced Find & Replace screen there’s a function for replacing the searched value in a field with the value obtained from another field.

You can only replace the found value in the searched field with a value from another field though. You’re wanting it the other way around? Find a value in Field A, but set that value in Field B?

[Lorenz Ehrsam]

Basically as I have improved the database over time, I want to streamline and get rid of some fields. In the early days I had a pick list called Quick Glance, but later I added a pick list called Summary which I now use exclusively. Now I want to isolate the records that have Quick Glance filled and populate some of that info into the field Summary. In Quick Glance one item is called Swimming, and I now want to add swi1 to the Summary pick list for these contacts. Is that possible to do it in a batch fashion? Thanks.

[Brendan]

What do you mean by populate some of that info into the field Summary? So you don’t want the entire value from your Quick Glance field to be put into the Summary field? But only part of the field value?

You could also write a Form Script to loop through your records, looking at field values from specific fields and populating your Summary field with whatever info you deem necessary.

[Lorenz Ehrsam]

Yes I would be content to put the entire value into another field. But how do I do this?

[Sam Moffatt]

Use the script editor to grab the two fields ID’s and their values. For your old field, if it has a set of values then you need to translate them to the new value. I’d use a switch statement here on your old field to map it over, something like:

let oldValue = record.getFieldValue('fld-1234');
let newValue = record.getFieldValue('fld-4321');

switch(oldValue)
{
  case 'Swimming':
     record.setFieldValue(newValue + ', swi1', 'fld-4321');
     break;
}

Then for your new field, if you have a multipick list then get the current value and then use JavaScript to append “, <new value>”. It’ll look a little weird ofr empty fields but shouldn’t hurt anything. If it is a single value pick list, then you can just reset the field value.

If you create a form script, you can use form.getRecords to get all of the records and then process them one at a time. A pattern I use is to create a function script that accepts a record field and then label it as currentRecord to avoid getting confused in the scope. Then I can test using a single record or later on put it in a loop.

[Brendan]

But also you could go into the Multi-Column List View mode, select all the values from one field, then click in the first cell of another field and paste. All the values will be pasted in order into the other field. Make a backup first in case it doesn’t do what you expect. Or the script that Sam posted.

There’s also the Fill Down function too which can be used to fill a value from the selected cell into all the selected cell below it.

[Author]

Posts