Hello, and welcome to Another Salesforce Blog!  Here I will be posting solutions to problems that I couldn’t find an answer to in hopes of helping those who find themselves stuck when using the Salesforce platform.

User Story

It’s the end of the fiscal year, and we need to update our currency conversion rates to the most recent rates. The rates are contained in a spreadsheet that was given to us by our financial team. We are unable to add a connected app due to business requirements. We must create new DatedConversionRate objects so that previous year DatedConversionRate objects remain in the organization for reference. Multiple Currencies and Advanced Currency Management are already turned on, and currencies are already set up for the required IsoCodes.

Background

Today’s topic involves the DatedConversionRate object, which is one of several legacy objects that do not support DML functions in Salesforce. Practically, this means that these objects cannot be updated, inserted, or deleted via Data Loader, they must be modified using Apex or the REST API.

It is important to note that, at this time, it is not possible to insert DatedConversionRate objects from scratch. For a new organization, DatedConversionRate objects must be entered manually.

When I started researching this problem, the bulk of the resources that I found suggest manually editing each record in the UI or using Workbench (time consuming, error prone), or using cURL to send an HTTP request to manipulate the records. I will likely write a tutorial on how to complete this using cURL at some point, but, for now, there’s an easier way that doesn’t require creating a connected app.

In the REST API Developer Guide, an SObject Tree is defined as a collection of nested, parent-child records with a single root record. It is important to note that “because an sObject tree can contain a single record, you can use this resource to create up to 200 unrelated records of the same type.” Given that there are 195 countries, this method will work perfectly for our purposes.

The example given for a request body shows how one would insert multiple accounts with related contacts:

{
"records" :[{
    "attributes" : {"type" : "Account", "referenceId" : "ref1"},
    "name" : "SampleAccount",
    "phone" : "1234567890",
    "website" : "www.salesforce.com",
    "numberOfEmployees" : "100",
    "industry" : "Banking",
    "Contacts" : {
      "records" : [{
         "attributes" : {"type" : "Contact", "referenceId" : "ref2"},
         "lastname" : "Smith",
         "title" : "President",
         "email" : "sample@salesforce.com"
         },{         
         "attributes" : {"type" : "Contact", "referenceId" : "ref3"},
         "lastname" : "Evans",
         "title" : "Vice President",
         "email" : "sample@salesforce.com"
         }]
      }
    },{
    "attributes" : {"type" : "Account", "referenceId" : "ref4"},
    "name" : "SampleAccount2",
    "phone" : "1234567890",
    "website" : "www.salesforce2.com",
    "numberOfEmployees" : "100",
    "industry" : "Banking"
     }]
}

Solution

In order to translate this to a workable JSON file for DatedConversionRate, first we declare the defining attributes:

{
   "records" : [
      {
         "attributes" : {"type" : "DatedConversionRate", "referenceId" : "uniqueRefId"},
...

In order to save you from some of the headaches I ran into, “records” must be in all lowercase, and the referenceId must be unique for each entry. More on that in a second.

Next, we add the required fields, IsoCode, ConversionRate, and StartDate:

...
         "IsoCode" : "ABC",
         "ConversionRate" : "123",
         "StartDate" : "2019-09-28"
      },
...

We would do this for each country code and conversion rate. If you are given the rates in a spreadsheet like I was, a little bit of Excel magic can easily generate the JSON:

Use a Concatenate formula to create your JSON building block, and use another Concatenate formula to combine them. I have used single quotes in order to successfully concatenate the fields in Excel, which I found and replaced once I pasted the final code block into VSCode.

Sample Concatenate formula for cell C2:

=CONCATENATE("{'attributes' : {'type' : 'DatedConversionRate', 'referenceId' : 'referenceID", A2, "'},
 'IsoCode' : '", A2, "', 
'ConversionRate' : '", B2, "',
'StartDate' : '2019-09-28'}")

This formula generates a referenceId of “referenceIdABC” where ABC is the IsoCode, guaranteeing that each referenceId is unique.

After the code is generated and formatted in VSCode, it will look something like this:

Finally, we take our generated code and input it into Workbench under the POST method, using a URI of:

/services/data/v46.0/composite/tree/DatedConversionRate

Hit “Execute”, and, voila! The new DatedConversionRate objects are created.

To verify, use the following SOQL query:

SELECT ConversionRate,CreatedDate,Id,IsoCode,StartDate FROM DatedConversionRate WHERE CreatedDate >= TODAYSDATE