Using a Formula in a DemandTools Job to Modify a Value (Not in Excel)

[jcrandall]

We’re working on a DemandTools job where we want to modify a value that looks like this: "$9.09 CAD". Specifically, we want to remove the dollar amount and retain only the currency code—so for example:

• Input: "$9.09 CAD"

• Desired Output: "CAD"

We do not want to do this in Excel before importing—we want the transformation to happen as part of the DemandTools job itself. We want to transform the data in the excel file and then update the CurrencyISOCode with the accepted format after the excel file data for that field has been changed to a supported value (such as CAD). 

Is there a way to use a formula within DemandTools (such as in the Transform or Modify module) to accomplish this kind of string manipulation?

Also, can you clarify:

• Are formulas in DemandTools only intended for creating new values based on Salesforce fields and data?

• Or can formula logic be applied to values coming from an Excel file as part of a DemandTools job (for example, to clean or standardize values before import/match)?

We’d appreciate guidance or examples if this is possible.

Thanks!

[carilovorn]

I have a similar use case and would like to use a formula to set the new value.

[Pugs_Validity]

@jcrandall @carilovorn - This is a great use case for a relatively new feature, Formula Templates. In Import, once you've mapped your corresponding source file fields to your desired Salesforce fields, you can transform the data when importing using one of the out-of-the-box transform formulas or utilize a Formula Template that opens the door to our entire Pre-Built Formula Dictionary as well as the ability to build custom formulas. We've seen a myraid of use cases for this, everything from complex functions that serve as a make-shift VLOOKUP to simple substitutions, replacing one value for another. As you progress to the preview screen in Import, you will be able to see your transformed spreadsheet data prior to executing anything in Salesforce. To clarify, this will NOT change the original source file, the process only transforms your data to bring it into Salesforce through DemandTools.  

@jcrandall - For your instace, you'll want to utilize RegExReplace(${CurrencyIsoCode}, "[^A-Za-z]+", "") -CurrencyIsoCode representing whatever the field is you are wanting to transform. Using Formulas Article

[carilovorn]

We have over six million contacts we want to update, doing this with a file is not feasible.  Is there a way to use the Modify or Tune modules to do this?

[Pugs_Validity]

@carilovorn - Absolutley! Using Formulas in Modify

Are you looking to perform the same data transformation or something different?

[beavery]

Hi @Pugs_Validity ,

Thank you so much for your reply, super helpful. Using the original example, we actually need the formula to do the reverse however, and need to remove the CAD, leaving just 9.90 (ideally without $). Is there an alternate formula that can achieve this? Research was suggesting string.gsub but I don't see that in the list of supported formulas. Any other ideas? Thanks! CC: @jcrandall 

[Pugs_Validity]

@beavery - My pleasure! A slight tweak to the aforementioned Regex should solve this for you.

For your usecase, you'll want to use: RegExReplace(${CurrencyIsoCode}, "[a-zA-Z$]+", "")