Jitterbit: backfill Salesforce ID into a database
13 Sep 2022
Last year, I shared a great data-load pattern called “Insert, update, and delete from a database to Salesforce.”
If you implement it with Jitterbit, I’ve recently discovered some tricks you can do to combine steps that load data into Salesforce with steps that load the results of that operation back into your database, which keeps your project tidier.
Instead of writing Salesforce IDs resulting from a successful “upsert” or “delete” operation to CSV, and then reading from CSV to the database, you can write successfully upserted/deleted Salesforce IDs directly to the database as part of the Salesforce data-load operation itself.
This tutorial will focus on combining “part 2” with “part 3” of that architecture – that is, it will demonstrate 1-operatoin sample code for working with upserts (and writing the resulting Salesforce IDs back to a database).
Instructions
Replace the built-in transformation
When you arrive at the “Schedule & Options” screen of the wizard, below “Run on schedule,” within “Response Option,” uncheck “I need to interact with the response from Salesforce” before clicking the “Next” button.
Once you have created your new Salesforce operation, double-click it from with the “Operations” area toward the top of the left-navigation, or if the Salesforce panel of the operation is already open, click the “Edit” button next to the word “Operation” (below “Mappings,” above “Run on Schedule”).
There should be 4 components to the operation:
- A source
- A transformation
- A globe labeled “SFDC Insert,” “SFDC Update,” “SFDC Upsert,” etc.
- A second transformation labeled “
(Salesforce Status Response)”.
Right-click on the fourth item – the one labeled “(Salesforce Status Response)” – and click “Select Existing Transformation,” then click the “Create New Transformation” button.
Note: If you already checked “I need to interact with the response from Salesforce” when creating the operation, you can still replace it with “Select Existing Transformation” and “Create New Transformation” – you’ll just have an orphaned Transformation beginning with the name of your Salesforce operation and ending with the words “Response” that you should find in the left-navigation under “Transformations” and delete – or at least move to a folder called “old” – for tidiness.
Give it a useful name, and choose a target of type “Database,” then click the “Next” button.
(As a naming convention, personally, I like to name it after my operation, but end it with the phrase “ Response Handler (handwritten)“).
Under “Available Database Targets,” click the database you’d like to load data into (or use the picklist to create a new one if necessary) and click the “Next” button.
Find the table you’d like to write the Salesforce ID into and click the “Next” button.
Select “Insert/Update,” “Insert Only,” or “Update Only” as appropriate and click the “Finish” button. You’ll probably want to choose “Update Only.”
Tip: If only “Insert Only” is available, then your database table doesn’t come with a primary key inside of the database itself, so click the “Edit Selected Table” button to tell which column(s) you’d like to match records on, which will expose the other two options.
Transformation details
Primary key logic
Jitterbit will open your new Transformation so that you can map data from the “left side” (the Salesforce data-edit response) to the “right side” (your database table).
In the right-hand panel, double-click on the database table column that you chose as a primary key.
Fill it with logic along the lines of the following:
<trans>
If(root$transaction.response$body$upsertResponse$result.success$ == true,
FindByPos(
SourceInstanceCount()
, root$transaction.request$body$upsert$Contact#.My_External_Id__c$
);
);
</trans>
Note: In this case, it was an Upsert operation against the Salesforce Contact object, using the Salesforce custom field My_External_Id__c as the matching criteria for the Salesforce operation.
Note the “FindByPos(...)” function and its first parameter of “SourceInstanceCount().”
While data from the root$transaction.response (response) part of the left-hand side of things will always be tailored to “which record Jitterbit is currently looping through,” data from the root$transaction.request (request) part of it always needs to give Jitterbit a hint about exactl which record you’re talking about.
Tip: Sadly, even though the left-hand side of a Salesforce response will look like it gives you all sorts of useful information under a structure such as root$transaction.request$body$upsert$Contact#., my experience is that I’ve found that the only item in that structure that actually has a non-null value is the field you used for matching when it comes to the Salesforce Upsert.
This is one of many reasons that, when I don’t need lightning-fast performance and don’t fear hitting daily Salesforce API-usage governor limits, I avoid creating Insert or Update operations against Salesforce in Jitterbit.
Instead, everything is an Upsert and then the Request transformation has a “Condition” that performs a SfCacheLookup(...) Jitterbit scripting operation to determine whether or not to proceed with data-loading a record into Salesforce.
- For insert-like behavior, return
TRUEfrom the condition script only if Salesforce doesn’t yet contain such a record. - For update-like behavior, return
TRUEfrom the condition script only if Salesforce does already contain such a record.
Note: A further consequence of your “external ID” that you used in the upsert being the only non-null data under root$transaction.request$body$upsert$ is that if you built it by string-concatenating extra details like “fromdatabase_” to a value from the actual primary key column in the database, you’re going to have to strip all that cruft away as you make the return-trip.
For example, here’s one where I knew that the actual database primary key would be immediately after the first underscore in the value of Contact.My_External_Id__c (so I turned a string into an array with “Split(...)” and then chose the 2nd part of the array with “[1]“):
<trans>
If(root$transaction.response$body$upsertResponse$result.success$ == true,
Split(
FindByPos(
SourceInstanceCount()
, root$transaction.request$body$upsert$Contact#.My_External_Id__c$
)
, '_'
)[1];
);
</trans>
If you always want to load data back into the database, whether a record succeeded or failed at loading into Salesforce, then you can use something like this instead,taking off the outermost “If(...)”:
<trans>
FindByPos(
SourceInstanceCount()
, root$transaction.request$body$upsert$Contact#.My_External_Id__c$
);
</trans>
Click the “OK” button to exit the mapping-editing popup.
Salesforce ID logic
In the right-hand panel, double-click on the database table column that you’d like to load a Salesforce ID into.
Fill it with logic along the lines of the following:
If(root$transaction.response$body$upsertResponse$result.success$ == true,
root$transaction.response$body$upsertResponse$result.id$;
);
Note: This particular piece of code is for an Upsert operation – you’ll have to change it if you did some other type of data-write into Salesforce.
You could remove the “If(...),” but personally, I think it only makes sense to write a Salesforce ID back to the database if the record was successfully committed to Salesforce.
Click the “OK” button to exit the mapping-editing popup.
Timestamp logic
Perhaps you’re also keeping track of when you wrote this data back to the database.
In the right-hand panel, double-click on the database table column that you’d like to load a datestamp or timestamp into.
Fill it with logic along the lines of the following:
<trans>
If(root$transaction.response$body$upsertResponse$result.success$ == true,
ShortDate(Now());
);
</trans>
Note: Again, this particular code example is for the aftermath of a Salesforce Upsert operation. The “If(...)” is up to your judgment. And the “ShortDate(...)” around “Now()” is for producing a datestamp rather than a timestamp.
Error-message logic
Finally, if you have a column in your database for keeping track of error messages (although that’s not how I like to do error messages – I prefer to collect them as attachments and e-mail them out out to people using native Jitterbit functionality), you could use a mapping definition for that field along these lines:
<trans>
If(root$transaction.response$body$upsertResponse$result.success$ == false,
SumString(root$transaction.response$body$upsertResponse$result.errors#.message$, ". ", false);
);
</trans>
Save and deploy transformation
Save and deploy your new transformation to Jitterbit servers, then close the transformation editing tab.
Add a database target (and save and deploy operation)
Back in your Operation tab, you should now see 4 components to the operation:
- A source
- A transformation
- A globe labeled “SFDC Insert,” “SFDC Update,” “SFDC Upsert,” etc.
- A transformation that you just made.
Right-click on the fourth item, click “Insert After This,” and then click “Target.”
Double-click on the fifth-item that now exists and, in the popup, find your database target, select it, and click the “OK” button.
Save and deploy your operation to Jitterbit servers, then close the transformation editing tab.
Tip: I recommend editing the 2nd item in the operation – the “request” transformation – to work with a very limited dataset the first time that you run this operation, so that you can feel confident you’ve done things correctly.
Further reading and credits
- This can be a useful way to keep a Jitterbit implementation of the “Insert, update, and delete from a database to Salesforce” pattern tidy – essentially, it lets you combine pattern #2 & pattern #3 for upserts into a single Jitterbit operation. (It probably lets you combine pattern #5 & pattern #6 into a single Jitterbit operation for deletes, too, but I haven’t played with writing code for that yet.)
- Learn more about why I prefer Upsert over Insert/Update at “Better Salesforce Insert/Update Operations with Jitterbit Caching.”
- Official Jitterbit documentation about their available scripting functions
Credit: I reverse-engineered my code examples from what Jitterbit auto-generated over in the right-hand side of the mapping it created when I did check “I need to interact with the response from Salesforce:”
<trans>
// This mapping is part of a Salesforce wizard.
// Modifying it may cause the wizard to malfunction.
If(root$transaction.response$body$upsertResponse$result.success$ == false,
WriteToOperationLog("The following record failed to process. Contact " +
FindByPos(SourceInstanceCount(), root$transaction.request$body$upsert$Contact#.My_External_Id__c$) +
" The issue was: " +
SumString(root$transaction.response$body$upsertResponse$result.errors#.message$, ". ", false)))
</trans>
