Scenario

One of the great features of Talend is its vast quantity of different components. No other ETL (Extract, Transform and Load) tool gets close to Talend in this regard. In this blog post, we are going to take a look at how we can automate sales price updates in SalesForce based on currency rates and conversions using Talend Open Studio.

If your company sells products and/or services in different currencies, then you need to update your SalesForce pricebook entries every month or so with the latest sale prices based on the most recent exchange rates. You can make these updates manually in SalesForce, or use a tool like Apex Data Loader to export pricebook entries, update the sales prices and import the updates back into SalesForce. Either way, someone is responsible for ensuring the prices are up to date and accurate, and this takes time, manual effort and is prone to errors. However, this process can be automated using Talend Open Studio!

Overview

With Talend Open Studio you can easily create a job that will take the existing currency specific values and update them against a base currency with the latest exchange rates in seconds. For the sake of sparing you a lengthy (and quite frankly, tedious) account of how this is done, I shall instead provide a list of items that you will most likely need to complete such an activity. I’ll also provide information in anticipation of any questions you are bound to have during the creation of a job like this.

1. You will need the following Salesforce Connection credentials:
   1. Web Service URL
   2. User Name
   3. Password (this should be made up of password and token; your Salesforce Admin should know this info).
2. You will need to obtain Currency Exchange Rates. I recommend using Yahoo API’s for this, as they are free and reliable. (Yahoo API is showcased in the ‘Examples’ section below);
3. Create custom metadata for certain Salesforce tables (also available in the ‘Examples’ section below);

Examples

Currency Exchange Rates

To get the latest Currency Exchanges from the free Yahoo API, use the tRESTClient component with the following settings:

An image of the configuration is demonstrated in table below.

After you set up the REST client you will probably want to store the XML response in either a tHash, CreateTemporaryFile or DB components. This is optional – it is completely up to you.

Using Custom Salesforce Table Metadata

The ‘PricebookEntry’ table in Salesforce contains the data we need to update (the ‘UnitPrice’). However, the Talend Salesforce components will not retrieve the ‘CurrencyIsoCode’ column which determines the currency for each pricebook entry. You will need to retrieve the required columns using custom Salesforce table metadata.

As you can see, a custom SOQL query is performed with an extra undefined column ‘CurrencyIsoCode’ being specified. This is not a mandatory step (you can leave the ‘Manual input of SOQL query’ unticketed) but it is recommended if you wish to limit the response of your component in order to make it faster and more manageable.

To retrieve extra columns that are missing from the standard ‘Module’ metadata definitions:

• Select Custom Object
• Enter Custom Object Name
• Select schema:
  • Set ‘Built-In’ if you wish to define schema within the component;
  • Set ‘Repository’ if you wish to add schema from a repository list.

Extras

• Tick the manual input of SOQL query
• Enter your Select statement

Important

Make sure that the names of the extra columns you define in the schemas are first letter uppercased. Like: ‘CurrencyIsoCode’ rather than ‘currencyisocode’. If you have defined the name in any other way, the result returned is going to be null.

Conclusion

Using these simple steps, you will be able to set up an automated job that will update the values in accordance with the latest exchange rates free of charge. You can even make it completely independent by running it on TAC or free of change using a scheduling program such as: Windows Scheduler or crontab (if Linux is your friend).

Both guides are available here:

• Scheduling Talend Open Studio Jobs in Windows without Talend Administration Center (TAC)
• Scheduling Talend Open Studio Jobs in Linux without Talend Administration Center (TAC)

* Please Note: Talend has a good Salesforce blog post of their own, showcasing some of the capabilities of these components.