How to replace the pick list values in existing records for a particular record type

Question

I have a picklist field 'Priority' on the Account object with the values 'A+, A, B+, B, C+' and also I have the record types 'Test, Test1, Test2' on the Account object. I have around 1M records in Account object,

My current need is to replace all my Priority field values as,

A+ -> High Priority
A  -> Priority
B+ -> Medium Priority
B  -> Low Priority
C  -> Low Priority

only for the Record Type 'Test'.

I can simply add the new picklist values to the field priority and can select only the record type 'Test'. But I need to replace the existing record priority values with the new values for the record type 'Test'. I don't think we can replace the values by clicking the replace button since it will update all the records instead of only the records that Record Type 'Test'.

Am I correct or can we do it by clicking the replace button for a specific record type?

If not,

What is the best way?

Data loader with bulk API?

Answer 1

You're correct.

Using replace will replace the picklist value completely across all record types.

Data Loader with bulk API is your best bet. You'll just have to analyze if there's any workflows or logic you might trigger in an update that you'd want to avoid, otherwise you listed the steps.

1. Create your new picklist value
2. Query/Pull the necessary records types into a CSV Search and replace the old values for the new
3. Set your dataloader settings to use bulk API and a batch size of 10,000
4. Run your CSV through dataloader in an update

Answer 2

The best way to solve this issue is to write a short batchable to make the changes. Generally your Excel will crash with that many records when you export/import through data loader.

It would work in the following manner:

1. Add the new picklist values to the picklist
2. Create the batchable class (you can base yourself on the one below), test it and deploy to prod (https://gist.github.com/AlwaysThinkin/99c01f13901ad06ab39a25e0d84c2769) - credit to https://gist.github.com/AlwaysThinkin
3. Run the batch

Keep in mind that the batch will work properly if there are not too many triggers/logic on the object. If this is the case, it might be worthwhile to also investigate bypassing these.

Answer 3

Along with other solutions, mentioned above, these type of data updates can be easily achieved without having to etl (extract, transform and load) yourself using Realfire.

For ex., here is one of the DML Updates you can run in parallel which will process the records. Assuming your org doesn't have triggers that prevents using full batch size, million records should be processed in about couple of hours or so.

Note that DML is not native Salesforce functionality (wish it was) so you cannot run this statement in Developer console or workbench.

update account set priority__c = 'High Priority' where recordtype.developername = 'Test' and priority__c = 'A+'

[Disclaimer: I'm part of the team that builds Realfire]