+ Start a Discussion
NOTLSimpsonNOTLSimpson 

Data concatenation

Because of an old system requirementa that isn't in place anymore, all the contact phone numbers in Salesforce are in the form

Work_Country_Code__c

Work_City_Code__c

Work_Phone_Number__c

 

We now want to revert to having a single phone number field. Is there any wya using LexiLoader or some other too that I can contatenate the contents of these fields, and put them into the default Salesforce Phone field?

JitendraJitendra

Hi,


Create the workflow rule to update the phone field.

Now execute update operation for all the existing records using developer console. It will cause the workflow to execute.

Now disable the workflow so that it will not cause the update.

 

For new records, take care of conecatenation by any means or you can keep the old workflow also.

NOTLSimpsonNOTLSimpson

Hi -- that's exactly the sort of information I was hoping to hear about. I don't know how to force an update of all existing records using the developer console though. I'm becoming familiar with the developer console and writing triggers. Would it just be a trigger that is called on update, then selects all the contact records and updates them?

 

So something like this?

trigger updatePhoneNums on Contact (before update) {
	
	 for(Contact con : Trigger.new){
		List existingCons = [SELECT Email,Country_code__c,Area_City_code__c, Phone_Number__c, Phone
										FROM Contacts;
			for( Contact con1 : existingCons){
				

				con1.Phone = Country_code__c&Area_City_code__c&Phone_Number__c;
				update con1;
			}
		
	 	}
}
HariDineshHariDinesh

Hi,

This is also fine, but this trigger is used for one time only and after this trigger executed all the contacts will be updated and you can inactivate the trigger or delete it.

 

Few changes are needed in the trigger

 

trigger updatePhoneNums on Contact (before update) 
{
	List existingCons = [SELECT Email,Country_code__c,Area_City_code__c, Phone_Number__c, Phone FROM Contacts;
	list<contact> listcon = new list<contact>();
			for( Contact con1 : existingCons)
			{
			    con1.Phone = con1.Country_code__c&con1.Area_City_code__c&con1.Phone_Number__c;
				listcon.add(con1);
			}
		update Listcon;
	 	
}

 To fire this trigger edit and save any of the contact.

JitendraJitendra

In, Developer console execute this statement:

 

List<Contacts> conList = [SELECT ID FROM Contacts];
update conList ;

 

NOTLSimpsonNOTLSimpson

This is the trigger I ended up writing:

trigger correctPhoneNum on Contact (before update)
{
    List<contact> existingCons= [SELECT Email,Country_code__c,Area_City_code__c, Phone_Number__c, Phone FROM Contact];
    list<contact> listcon = new list<contact>();
            for( Contact con1 : existingCons)
            {
                con1.Phone = con1.Country_code__c + con1.Area_City_code__c + con1.Phone_Number__c;
                listcon.add(con1);
            }
        update listcon;
         
}

 

And when I run the update from the Developers Console, it says it's updating, but nothing changes in the records. Any ideas?

Santhosh KumarSanthosh Kumar

How many contacts are you talking about here? Rememeber that you can retrieve upto 50K rows in Soql so if you have more number than that, it is going to fail.

 

Another option is to export all the contacts using data loader, write a simple java program to process the file (to create new phone field) and upload the data back to Sfdc using dataloader.

NOTLSimpsonNOTLSimpson

There are 2 contacts -- it's my sandbox.

 

I've realized that it sent me an error report, subject line:

Sandbox: Developer script exception from Calgary Scientific : correctPhoneNum : correctPhoneNum: execution of AfterUpdate caused by: System.DmlException: Update failed. First exception on row 0 with id 003e0000001hIsXAAU; first error: CANNOT_INSERT_UPDATE_ACTIVATE_ENTITY, correctPhoneNum: maximum trigger depth exceeded Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger event AfterUpdate for [003e0000001hIsX, 003e0000001hIuS] Contact trigger eve

 

I'm trying to figure it out what it means now.

Santhosh KumarSanthosh Kumar

You are trying to update all contact in before update of contact trigger and hence getting into infinite loop and erroring out with stackoverflow. I think you should inactivate the contact and do the same logic in anonymous apex.

NOTLSimpsonNOTLSimpson

Okay -- I'm much closer -- I'm actually running the code from the Dev Console, and it's changing the field to nullnullnull.

 

The three fields I'm trying to put together to make up the Phone field are each text fields of a certain length. The Phone field is a phone data type. I've tried
using string.valueof(Country_Code__c + CityAreaCode__c + PhoneNumber__c) but it comes out as nullnullnull.

Is there a way to convert a text data type to a phone data type?