07-11-2023 12:12 PM - edited 07-11-2023 12:16 PM
Huge thanks to everyone who attended today's training on Using Formulas for Advanced Data Standardization. Below is a link to the recording as well as a few additional resources that were referenced during the training. Also, I am going to add a comment with samples of the formulas that @JonG_Validity reviewed during the session. Feel free to drop questions below if you have them!
Session Recording
Modify Formula Dictionary
Wildcard Documentation/Examples
Solved! Go to Solution.
07-11-2023 12:14 PM - edited 07-11-2023 12:15 PM
Modify Formulas covered in today's session:
Adding US Country Code to Phone Numbers
Remove Special Characters from any field:
Fix 9 Digit US Zip Codes that do not have a hyphen:
Return US Zip Codes That are Malformed:
Return Canada Postal Codes that are Malformed:
Return UK Postal Codes that are Malformed:
07-11-2023 12:14 PM - edited 07-11-2023 12:15 PM
Modify Formulas covered in today's session:
Adding US Country Code to Phone Numbers
Remove Special Characters from any field:
Fix 9 Digit US Zip Codes that do not have a hyphen:
Return US Zip Codes That are Malformed:
Return Canada Postal Codes that are Malformed:
Return UK Postal Codes that are Malformed:
07-17-2023 01:18 PM
Hi Ashley,
Happy Monday!
Thanks for sharing the advanced formulas.
I've found an error with the last formula to Return UK Postal Codes that are Malformed:
error says: There seems to be a problem with the formula syntax, please check it over and try again.
Thanks,
Carlos
07-17-2023 01:25 PM
Hi @cj_malwarebytes, I just copied and pasted into my instance and I think it's just an issue with the quotations - try removing the set of quotes surrounding the RegEx formula and insert them again. It should appear red once the update has been made. Let me know if that doesn't work and I'll take another look!
07-17-2023 01:57 PM
Hi Jon, It worked, thank you so much!!
07-17-2023 02:06 PM
Hi Jon,
would I be able to include the account ids to get in an export?
I'm trying to check the malformed postal codes from the UK, however, the NewBillingPostalCode would make it completely Blank/Null and I don't want to do that unless it is a completely incorrect billing postal code. The problem with UK is that we have billing associated to those addresses, I might not want to update those.
Thanks,
Carlos
07-18-2023 04:49 AM
Hey Carlos, absolutely - just be sure to select ID from the Fields to Show on Preview section in step one as shown below. You can take that export file, make your updates & then upload it to Import and use the ID as your key field to make the changes in SF.
07-18-2023 06:40 AM
Hi Jon,
Perfect, thank you again!
07-12-2023 06:01 AM
Thank You
12-29-2023 11:59 AM
Hi @Ashley_Validity there are number of formulas in the Modify Function list that aren't listed on DemandTools - Modify Formula Dictionary page.
Is there another resource to find an explanation on these, or could you get the additional formula's added.
Here are few examples -
AVEDEV(number1, number2, ... )
AVERAGE(number1, number2, ...)
CEILING(number, significance)
CHOOSE(index_num, value1, value2, ...)
CLEAN(text)
CODE(text)
...
TEXT(value, format_text)
Thank you!
Michael W
Get industry news, expert insights, strategies, and hot tips, served up fresh every week.
Visit the Validity blog