topic Re: Today's Training Recording: Using Formulas for Advanced Data Standardization in DemandTools

Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization

Ashley_Validity — Tue, 11 Jul 2023 19:16:42 GMT

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

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

Ashley_Validity — Tue, 11 Jul 2023 19:15:44 GMT

Modify Formulas covered in today's session:

Adding US Country Code to Phone Numbers

CONCATENATE("+1", ${Phone})

Remove Special Characters from any field:

RegExReplace(input_string, "[\!\@\#\$\%\^\&\*\_\-\+\=\.\;\:\?\<\,\>\{\}\[\]\`\~]", "")

Fix 9 Digit US Zip Codes that do not have a hyphen:

CONCATENATE(MID(${MailingPostalCode}, 1, 5),"-", MID(${MailingPostalCode}, 6, 4))

Return US Zip Codes That are Malformed:

RegExMatch(${BillingPostalCode}, "^[0-9]{5}(?:-[0-9]{4})?$")

Return Canada Postal Codes that are Malformed:

RegExMatch(${BillingPostalCode}, "[ABCEGHJKLMNPRSTVXY][0-9][ABCEGHJKLMNPRSTVWXYZ] ?[0-9][ABCEGHJKLMNPRSTVWXYZ][0-9]")

Return UK Postal Codes that are Malformed:

RegExMatch(${BillingPostalCode},”^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$”)

Re: Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization

BBF — Wed, 12 Jul 2023 13:01:54 GMT

Thank You

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

cj_malwarebytes — Mon, 17 Jul 2023 20:18:59 GMT

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:

RegExMatch(${BillingPostalCode},”^[A-Z]{1,2}[0-9][A-Z0-9]? ?[0-9][A-Z]{2}$”)

error says: There seems to be a problem with the formula syntax, please check it over and try again.

Thanks,

Carlos

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

JonG_Validity — Mon, 17 Jul 2023 20:25:19 GMT

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!

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

cj_malwarebytes — Mon, 17 Jul 2023 20:57:02 GMT

Hi Jon, It worked, thank you so much!!

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

cj_malwarebytes — Mon, 17 Jul 2023 21:06:06 GMT

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

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

JonG_Validity — Tue, 18 Jul 2023 11:49:22 GMT

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.

Re: Today's Training Recording: Using Formulas for Advanced Data Standardization

cj_malwarebytes — Tue, 18 Jul 2023 13:40:07 GMT

Hi Jon,

Perfect, thank you again!

Re: Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization

michael-wtds — Fri, 29 Dec 2023 19:59:25 GMT

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

Re: Training Recording & Formula Examples: Using Formulas for Advanced Data Standardization

AnthonyValidity — Fri, 29 Dec 2023 20:03:23 GMT

@michael-wtds ,

Any formula in all caps should be easily found via google. They are spreadsheet formulas that you can find in other programs like Google Sheets and Excel.

Regards,