Transform Map Script to Change Integers to proper Phone Number formatting

[ksmithdev]

Hi there!

Nice to meet your acquaintance. I am running into a problem where I have an excel spreadsheet that has numbers formatted like this "8107463849" on the Excel spreadsheet, but I need them to be imported into a String field as "(810) 746-3849" without the quotes, and with a space between the ) and the 7.

I have been trying to come up with a way to do it, but I can't seem to get anything to work. I was trying to run it Onstart, as a New Transform Map Script (not the first one you see on the Transform Map form). I also looked into researching setting up the Phone Number in the System Properties and setting it to True with the glide UI, but the instance I am working with does not want this global. They want it only for this specific field.

This is also in Dublin.

Does anyone have something that does this?

[ksmithdev]

Mandar,

Yes I set it as a OnBefore script. It is trying to update approx. 9,000 records in total. It is importing the 10 integers smashed together in the Excel, and printing the "###,###,###,###" .

Justin,

I'm afraid I can't set a individual number to be formatted due to I am passing many numbers through.

[justin_drysdale · Accepted solution]

It is adaptable to a transform:

target.u_phone = formatPhone(source.u_phone);

function formatPhone(str) {  

var area_code = str.slice(0,3);  

var first_three = str.slice(3,6);  

var last_four = str.slice(6,10);

var ph = "(" + area_code + ")" + first_three + "-" + last_four;  

return ph;  

}  

[ksmithdev]

This worked. Thank you!

I used this, manually mapped the fields, and get this. It didn't work.

So, I then tried to map a new field to see if maybe the old field was throwing it off. I forgot to update the underlying transform script to match the new field I mapped, and ran it.

It worked.

Upon matching the field mapping correct, and the script variable, it did not work.

Imagine that. Thank you Justin!

[justin_drysdale]

var unformated = "8107463849";

var formated = formatPhone(unformated);



function formatPhone(str) {

var area_code = str.slice(0,3);

var first_three = str.slice(3,6);

var last_four = str.slice(6,10);



var ph = "(" + area_code + ")" + first_three + "-" + last_four;

return ph;

}

[Bradford Shelle]

So to add onto the original ask, what's the best way to handle a phone number being imported if, let's say, the phone number has some letters in it, or if the field is blank? Right now when I use this script to transform a blank field it just comes through as undefined.