function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Callum CurtissCallum Curtiss 

Standardising countries on a record

Hi there,

I have created a field on a contact record that says in which country the contact is located - I have pulled it through from the companies billing address.  

The billing address is a free text field meaning there are spelling mistakes etc. where people have done bad data entry.  I want to now convert the field to a picklist to standardise which countries people can assign a contact against to avoid spelling mistakes etc.- this i know how to do.  

My problem though lies in converting the free text to a picklist that is spelt correctly without losing data.  Please see the screen shot to get a better idea what I am talking about - User-added image

You see that if I simply create a picklist there will be several different 'United Kingdoms' - what I need to do is have one picklist value for 'United Kingdom' which will be the text displayed on the contact record with all these mispellings as not to lose data.  

I feel I have described the problem quite badly but hopefully someone can help!

Kind regards,

Callum
LBKLBK
There is a way around it. But it involves some manual work.

1. Create a new picklist field with right country options in the list.

2. Update the picklist field with the free text field data using a small apex process.

If your picklist has 100 countries listed, your record will have either 100 in its list (if the country matches perfectly with an option in the pick list) or 101 (if the country does not match any of the 100 options in the pick list).
This 101st value will be available only for that particular record. And, if you manually change it to one of the existing values, this 101st value will be gone from the picklist options.

This way, you will not have numerous United Kingdoms in the list, but a maximum of two United Kingdoms.

Let me know if this helps.
Callum CurtissCallum Curtiss
Hi,

Kind of makes sense - i'm not a SF dev only work for a small company.  When your saying apex does that involve coding?

Thanks,

Callum
LBKLBK
Yes. Few lines of code to fetch the data and update them back.

I can help you with that, once you have the configuration part is done and share the object name and field names.
Callum CurtissCallum Curtiss
Hi,

So I have now created a picklist with all the correct countires in it.

What do I need to do now?  Sorry for being so unknoweldgable.

Kind regards,

Callum
LBKLBK
No worries Callum. Everyone starts at the place where you are now.

Please share your Object Name, Text Field Name and the Picklist Name with me. I will provide you the instructions to update the data.



Also, please check and make sure that you have UNCHECKED "Restrict picklist to the values defined in the value set." option when you have created your picklist field.

You can go to Setup >> Create >> Objects >> YOUR_OBJECT >> PICKLIST_FIELD.
Callum CurtissCallum Curtiss
So the object is a contact record (i think).  The free text field is called Contact_Country__c and the picklist is called Contact_Country_2__c

Is that the corect information?

Many thanks,

Callum
LBKLBK
Hi Callum,

Here is your Update Code.

Code to update all the contacts 
List<Contact> lstContact = [SELECT Id, Contact_Country_2__c, Contact_Country__c FROM Contact];
for (Contact objContact : lstContact){
    objContact.Contact_Country_2__c = objContact.Contact_Country__c;
}
update lstContact;

Beware, this code will update your entire set of Contacts (Only the Contact_Country_2__c field though). So, I suggest you to test this code with fewer (or one) record first before running it on all the records.

Here is the code to do that on ferwer record.

Code to update the Contacts whose name starts with Callum 
List<Contact> lstContact = [SELECT Id, Contact_Country_2__c, Contact_Country__c FROM Contact WHERE Name LIKE 'Callum%'];
for (Contact objContact : lstContact){
    objContact.Contact_Country_2__c = objContact.Contact_Country__c;
}
update lstContact;
In the above code, replace Callum% with any first name you have in your contacts (% symbol is important). Like, Michael% or David%.

Let me know if you have any queries before you execute the code.
 
Callum CurtissCallum Curtiss
Thank you so much for your help, really useful.  I do not have time to complete this today however will let you know in due course if I get it to work.

Kind regards,

Callum