Better Salesforce Insert/Update Operations with Jitterbit Caching
Tags: database, salesforce, tutorial, etl
Published: 2019-10-28 22:02:51

---
title: "Better Salesforce Insert/Update Operations with Jitterbit Caching"
description: "External IDs aren't just for Upsert anymore"
published: true
date: 2019-10-28 15:00:00 UTC
cover_image: https://katiekodes.com/images/header-jitterbit-cache-extid-en.png
tags: database,salesforce,tutorial,etl
canonical_url: https://katiekodes.com/jitterbit-salesforce-insert-update-caches/
---

Combined with “[external ID](https://help.salesforce.com/articleView?id=000325076&language=en_US&type=1&mode=1)” fields, Salesforce’s “ **upsert** ” data-loading option is a godsend for dumping data from legacy systems into Salesforce. But what do you do if you _only_ want to insert new records or _only_ want to update existing records, using the external ID as an inter-system matching key, considering Salesforce’s “insert” and “update” operations don’t support matching on external IDs?

The Jitterbit [ETL](https://en.wikipedia.org/wiki/Extract,_transform,_load) tool makes it relatively easy to use a [SOQL query](http://womencodeheroes.com/2015/04/cooking-with-code-a-sweet-intro-to-soql-part-one/) ahead of an `insert` or `update` operation and use data from the query to filter which “upload candidate” records _actually_ get sent to Salesforce. The trick is to write code in [Jitterbit’s scripting language](https://success.jitterbit.com/display/DOC/Jitterbit+Script+Language).

* * *

## TL;DR for experienced Jitterbit programmers

This is a step-by-step tutorial showing how to write small bits of code that use the Jitterbit execution environment’s memory to save a hashmap of IDs from both sides of an ETL process that’s about to run, and then to use your hashmap at data-upload-time on a record-by-record basis.

Skip to the parts that mention “`<trans>`”, the “Operation Script” sections, and the pro-tips / further considerations sections for the goodies.

* * *

## Today’s Data

### In My Source Database

My “data source” is an ordinary Oracle database whose **People** table looks like this when I run the following SQL query:

```sql
SELECT Id_Column, First_Name, Last_Name, Phone_Number, Email_Address
FROM People

```

| Id\_Column | First\_Name | Last\_Name | Phone\_Number | Email\_Address |
| --- | --- | --- | --- | --- |
| ERP10006 | Darweesh | Daher | 444-444-4444 | 444@example.com |
| ERP17685 | Helen | Hopper | 888-888-8888 | 888@example.com |
| ERP33014 | Grace | Gao | 777-777-7777 | 777@example.com |
| ERP71892 | Cathy | Combs | 333-333-3333 | 333@example.com |

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/00a-source-data.png)

### In Salesforce

My “data destination” is a Salesforce org whose **Contact** table looks like this when I run the following SOQL query:

```sql
SELECT Id, Name, My_External_Id__c, Phone, Email
FROM Contact

```

| Id | Name | My\_External\_Id\_\_c | Phone | Email |
| --- | --- | --- | --- | --- |
| 003010101010101 | Anush Amjit | ERP28190 |   |   |
| 003020202020202 | Benita Borges |   |   |   |
| 003030303030303 | Cathy Combs | ERP71892 |   |   |
| 003040404040404 | Darweesh Daher | ERP10006 |   |   |
| 003050505050505 | Ezra Ellis |   |   |   |
| 003060606060606 | Frances Fulvia |   |   |   |

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/00b-salesforce-before.png)

### Yes, Someone’s Missing

Note that even though Salesforce believes there’s a record in my “external data source” with ID `ERP28190` named Amjit Anush, it seems that Amjit has been deleted from my external database.

Dealing with that data integrity issue is another problem for another Jitterbit post.

* * *

## ETL Business Rules

Today we’re going to deal with some pretty unrealistic business rules, for example’s sake.

### Update

1. Whenever someone exists in both databases, we want to feed their Phone Number into Salesforce.
2. We’ll leave First Name, Last Name, and E-Mail Address alone upon record updates.

In other words, if we were to do an update right now, we’d expect to populate Cathy’s and Darweesh’s phone numbers but not their e-mail addresses.

### Insert

1. Whenever someone exists in Oracle but not in Salesforce, we want to feed their First Name, Last Name, External ID, and E-Mail Address into Salesforce.
2. We’ll leave Phone Number alone on upon record inserts.

In other words, if we were to do an insert right now, we’d expect to add new records for Grace and Helen to Salesforce _(both records having blank phone numbers but non-blank e-mail addresses in Salesforce)_.

* * *

## Jitterbit: Creating A Dictionary

### What The Dictionary Will Look Like

Ultimately, what we’re going to do today is run the following SOQL query:

```sql
SELECT Id, My_External_Id__c
FROM Contact
WHERE My_External_Id__c <> NULL

```

Which will give us data that looks like this:

| Id | My\_External\_Id\_\_c |
| --- | --- |
| 003010101010101 | ERP28190 |
| 003030303030303 | ERP71892 |
| 003040404040404 | ERP10006 |

We’ll transform and store that data into a data structure called a [dictionary](https://en.wikipedia.org/wiki/Associative_array) for use as a “[cache](https://en.wikipedia.org/wiki/Cache_(computing))” of values we can reference within Jitterbit without running another SOQL query.

The contents of our dictionary will look like this:

> - **`ERP10006`** : `003040404040404`
> - **`ERP28190`** : `003010101010101`
> - **`ERP71892`** : `003030303030303`

Note: If you’ve ever done Apex programming within Salesforce, you may have written similar code where you looped over the results of a SOQL query and used them to fill a `Map<Id,Id>` with values for later reference.

* * *

### Declare A Variable

First, declare a Jitterbit “global variable.” I’m going to call mine “`existingContactIdsByExtId`.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/01-globalvariable.png)

* * *

### Tell Jitterbit How To Log Into Salesforce

Before I write any code to populate `existingContactIdsByExtId`, I need to tell Jitterbit how to talk to my Salesforce, so I’ll create a “Salesforce Org” object in Jitterbit and give it my login credentials:

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/02-salesforceorg.png)

* * *

### Populate The Variable

Now I’ll create a new “Script” and call it “ **Lazy-Populate existingContactIdsByExtId**.”

Here’s the Jitterbit-script code I’ll put into it:

```
<trans>
If( IsNull( $existingContactIdsByExtId) || $existingContactIdsByExtId == "" ,
	WriteToOperationLog( "$existingContactIdsByExtId was null -- building it" );
	arr = SfLookupAll(
		"<TAG>Salesforce Orgs/my_salesforce_login@example.com</TAG>"
		,"SELECT My_External_Id __c, Id FROM Contact WHERE My_External_Id__ c <> null"
	);
	$existingContactIdsByExtId = Dict();
	i = 0;
	While( i < Length(arr) ,
		$existingContactIdsByExtId[arr[i][0]] = arr[i][1];
		i = i + 1;
	);
);
</trans>

```

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/03-script.png)

### Code Notes

#### Lazy Code

Note that this script is “[lazy](https://en.wikipedia.org/wiki/Lazy_evaluation).”

That means that if we have already put data into `existingContactIdsByExtId` during the runtime of a Jitterbit operation, a 2nd+ run of this script would skip running all code within the `If()` statement.

#### What Our Data Looks Like

Although Jitterbit doesn’t show us what the contents of the variable `arr` look like when we test-run our script, here’s what it would look like if we could get it to display _(I have my tricks…)_:

```
{{ERP28190, 003010101010101}, {ERP71892, 003030303030303}, {ERP10006, 003040404040404}}

```

And `existingContactIdsByExtId` looks like this:

```
[ERP10006=>"003040404040404",ERP28190=>"003010101010101",ERP71892=>"003030303030303"]

```

* * *

## Jitterbit: Updating Salesforce

### Update Operation

Next, I used Jitterbit to create an ordinary [Salesforce Update operation](https://success.jitterbit.com/display/DOC/Salesforce+Update+Wizard) that queries my Oracle database as a [source](https://success.jitterbit.com/display/DOC/Database+Source).

I’ll trust that you know how to do that in Jitterbit _(or are comfortable figuring out how to do it … or work with someone who is)_ and are here to learn my “caching” trick.

I named my operation “ **Update Contacts By External ID**.”

### Transformation

#### Mapping: Phone

Once you’ve set that up, in your Salesforce operation details screen, click the “Edit” button by “Mappings” to bring up the transformation involved in the operation _(for me, it’s called “ **Update Contacts By External ID Request** “)_.

Drag `Phone_Number` at left onto `Phone` at right.

#### Condition (execute vs. skip record)

Now we have to tell Jitterbit to detect that Grace and Helen aren’t _in_ Salesforce and skip over trying to update their Salesforce records.

Right-click the **`[E*] Contact`** folder at top and click “ **`Add Condition`**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/04-update_open_condition.png)

A new node will appear at right just below the “Contact” folder called “ **`Condition`**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/05-update_open_condition.png)

Double-click it. A “Formula Builder” window will appear for editing the node named “ **`root$transaction.body$update$Contact.Condition`**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/06-update-condition-formula-blank.png)

At left, between the `<trans>` and `</trans>` tags, type:

```
HasKey($existingContactIdsByExtId, ID_COLUMN);

```

_(Note that my Oracle database upper-cases column names when queried, so what I was previously calling `Id_Column` needs to be referred to in this “Jitterbit transformation formula” as `ID_COLUMN`.)_

If I click the “Test” button, I can see that the result is `0` _(false)_, indicating that ID `ERP10006` _(Darweesh Daher)_ is in `existingContactIdsByExtId`.

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/07-update-condition-formula-test.png)

Hmmm. That’s not right. Darweesh actually _is_ in my Salesforce Contact table with `ERP10006` on file.

But the Jitterbit “Test” button doesn’t know that. As far as it’s concerned, `existingContactIdsByExtId` is just an empty variable.

To test our `HasKey()` code and ensure that we wrote it correctly, we can add 2 lines to the “Condition” formula so that it runs our script and populates `existingContactIdsByExtId` with data:

```
$existingContactIdsByExtId = '';
RunScript("<TAG>Scripts/Lazy-Populate existingContactIdsByExtId</TAG>");
HasKey($existingContactIdsByExtId, ID_COLUMN);

```

That’s better: now the answer is `1` _(true)_. This means that that Darweesh’s record _will_ be processed when we run our Salesforce Update operation.

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/08-update-condition-formula-test-fix.png)

Just keep in mind that we don’t _actually_ want to leave those extra `$existingContactIdsByExtId = ''` or `RunScript()` lines of code in our condition once we’re done testing it.

It’s wasteful of Jitterbit’s CPU time _(and hence our real-world time)_ – particularly if we have a lot of rows in our database query.

There’s no real-world need to try to “populate `existingContactIdsByExtId`” every time we try to load a new row of data from our database into Salesforce. It’s much better off being done just before the entire “Update” operation kicks off as a whole.

Next, in the upper-right area, I’m going to click the little “1 of 4” next to the “Manual SQL” folder under the “Source” folder, which should flip it to say “2 of 4” and show ID `ERP17685`’s _(Helen Hopper’s)_ data instead.

I’ll hit “Test” once again to validate:

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/10-update-condition-formula-test-fix.png)

Satisfied that my condition lets Darweesh through but skips over Helen, I’ll delete my “testing-only” lines of code so that my formula simply reads as follows:

```
<trans>
HasKey($existingContactIdsByExtId, ID_COLUMN);
</trans>

```

I click “OK” to close the formula editor, then immediately click the “Save” button at top right area of my “Transformation” editor pane.

#### Mapping: ID

I have one more job to do before I can save, deploy, and close my “Transformation”: since this is an Update operation, I need to specify a Contact ID for each row that I’m updating in Salesforce.

My Oracle database has no idea what the answer to this question is _(it just thinks of Darweesh as `ERP10006`)_, but luckily, Jitterbit knows.

It just has to ask `existingContactIdsByExtId` what the Salesforce Contact ID for `ERP10006` is _(in this case, `003040404040404`)_.

I double-click on “Id” at right to bring up _its_ formula editor, setting the code between the `<trans>` and `</trans>` tags to be:

```
$existingContactIdsByExtId[ID_COLUMN];

```

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/11-update-id-formula.png)

_(I clicked “# of 4” repeatedly until it looped back around to “1 of 4” again so that I could see test data with Darweesh, but as you can see, Jitterbit isn’t happy, because it’s convinced that the contents of `existingContactIdsByExtId` is a bunch of blank text \_(a “string”)_.

As before, I can add 2 quick `$existingContactIdsByExtId = '';` and `RunScript("<TAG>Scripts/Lazy-Populate existingContactIdsByExtId</TAG>");` lines of code beforehand that I won’t keep in production to make sure things work.

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/12-update-id-formula-test-fix.png)

* * *

### Operation Script

We need to add one more thing to our Salesforce Update Operation before it will work: we need to ensure that every time it runs, the first thing it does is execute the “ **Lazy-Populate existingContactIdsByExtId** ” script.

From your Salesforce Update Operation’s configuration screen, click the “Edit” button next to “Operation.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/13-operation-edit-button.png)

Right-click on the node of that Operation representing your data source _(mine is called “My Oracle Database” because I’m creative like that)_ and click “ **Insert Before This** ,” next clicking “ **Script**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/14-operation-add-script.png)

Right-click on the new leftmost node called “_Script_” and click “Select Existing Script.”

Choose “ **Lazy-Populate existingContactIdsByExtId** ” and click “OK.”

Drag the leftmost node around until things look pretty, then use the “Save” and “Deploy” icons in your upper-right corner of this operation’s editor tab to ensure that your work isn’t lost. It should look something like this:

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/15-operation-script-added-update.png)

* * *

### Validation

Let’s try running our Operation. Find it at left under “ **Jitterbit Connect** ” -> “ **Salesforce** ” -> “ **Salesforce Updates** ” -> _(whatever you named it)_, right-click on it, and click “ **Run Update**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/16-run-update.png)

Hooray – it works!

![Celebration GIF](https://media.giphy.com/media/ZUomWFktUWpFu/giphy.gif)

When I run the following SOQL query:

```sql
SELECT Id, Name, My_External_Id__c, Phone, Email
FROM Contact

```

My data looks like this:

| Id | Name | My\_External\_Id\_\_c | Phone | Email |
| --- | --- | --- | --- | --- |
| 003010101010101 | Anush Amjit | ERP28190 |   |   |
| 003020202020202 | Benita Borges |   |   |   |
| 003030303030303 | Cathy Combs | ERP71892 | 333-333-3333 |   |
| 003040404040404 | Darweesh Daher | ERP10006 | 444-444-4444 |   |
| 003050505050505 | Ezra Ellis |   |   |   |
| 003060606060606 | Frances Fulvia |   |   |   |

![](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/17-salesforce-after-update-1.png)

* * *

## Jitterbit: Inserting Into Salesforce

### Insert Operation

Time to try inserting.

I used Jitterbit to create an ordinary [Salesforce Insert operation](https://success.jitterbit.com/display/DOC/Salesforce+Insert+Wizard) that queries my Oracle database as a [source](https://success.jitterbit.com/display/DOC/Database+Source).

Again, I’ll trust that you know how to do this in Jitterbit _(or have a plan to figure it out)_.

I named my operation “ **Insert Contacts By External ID**.”

### Transformation

#### Mapping: Name, Phone, External ID

Once you’ve set that up, in your Salesforce operation details screen, click the “Edit” button by “Mappings” to bring up the transformation involved in the operation _(for me, it’s called “ **Insert Contacts By External ID Request** “)_.

I dragged `ID_COLUMN` onto `My_External_Id__c`, `FIRST_NAME` onto `FirstName`, `LAST_NAME` onto `LastName`, and `EMAIL_ADDRESS` onto `Email`. No surprises there.

#### Condition (execute vs. skip record)

Now I have to do the same kind of “ **`Add Condition`** ” steps that I did above for my Update transformation, only the formula is going to be different.

In this case, we want:

```
<trans>
!HasKey($existingContactIdsByExtId, ID_COLUMN);
</trans>

```

The difference is the exclamation point before the `HasKey` – it serves as a “not” and flips the true/false results of `HasKey`.

Of course, we can test with the following script:

```
<trans>
$existingContactIdsByExtId = '';
RunScript("<TAG>Scripts/Lazy-Populate existingContactIdsByExtId</TAG>");
!HasKey($existingContactIdsByExtId, ID_COLUMN);
</trans>

```

Now we can see that Darweesh returns 0 _(false)_ and Helen returns 1 _(true)_. That’s perfect: we _want_ to insert Helen but not Darweesh.

We’ll save and deploy our transformation _(making sure we took any test lines of code out of our “Condition” formula)_.

#### _(There’s no ID mapping)_

Note that we don’t have to play any games with the Salesforce `Id` field on an Insert the way we did on an `Update` operation. We’ll just leave it un-mapped.

* * *

### Operation Script

As with Update, need to add one more thing to our Salesforce Insert Operation before it will work: we need to ensure that every time it runs, the first thing it does is execute the “ **Lazy-Populate existingContactIdsByExtId** ” script.

From your Salesforce Insert Operation’s configuration screen, click the “Edit” button next to “Operation.”

Right-click on the node of that Operation representing your data source and click “ **Insert Before This** ,” next clicking “ **Script**.”

Right-click on the new leftmost node called “_Script_” and click “Select Existing Script.”

Choose “ **Lazy-Populate existingContactIdsByExtId** ” and click “OK.”

Drag the leftmost node around until things look pretty, then use the “Save” and “Deploy” icons in your upper-right corner of this operation’s editor tab to ensure that your work isn’t lost. It should look something like this:

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/18-operation-script-added-insert.png)

* * *

### Validation

Let’s try running our Operation. Find it at left under “ **Jitterbit Connect** ” -> “ **Salesforce** ” -> “ **Salesforce Inserts** ” -> _(whatever you named it)_, right-click on it, and click “ **Run Insert**.”

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/19-run-insert.png)

It works again. Yay.

![Celebration GIF](https://media.giphy.com/media/26u4cqiYI30juCOGY/giphy.gif)

When I run the following SOQL query:

```sql
SELECT Id, Name, My_External_Id__c, Phone, Email
FROM Contact

```

My data looks like this:

| Id | Name | My\_External\_Id\_\_c | Phone | Email |
| --- | --- | --- | --- | --- |
| 003010101010101 | Anush Amjit | ERP28190 |   |   |
| 003020202020202 | Benita Borges |   |   |   |
| 003030303030303 | Cathy Combs | ERP71892 | 333-333-3333 |   |
| 003040404040404 | Darweesh Daher | ERP10006 | 444-444-4444 |   |
| 003050505050505 | Ezra Ellis |   |   |   |
| 003060606060606 | Frances Fulvia |   |   |   |
| 003050505050505 | Grace Gao | ERP33014 |   | 777@example.com |
| 003060606060606 | Hopper Helen | ERP17685 |   | 888@example.com |

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/20-after-insert-1.png)

* * *

## Fix A Typo

Oh wait – there’s a typo.

![Dejected GIF](http://giphygifs.s3.amazonaws.com/media/9Y5BbDSkSTiY8/giphy.gif)

My database had Helen’s first and last name flipped.

![Screenshot of previous data with Helen highlighted](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/21-after-insert-1-typo.png)

_(Did you all notice this whole time? Am I the only person who didn’t notice in any of my previous screenshots about her? 😳)_

### Can I Just Update?

All right – I just fixed her data in my source database.

In the real world, I’d probably have my UPDATE on a daily schedule, so Grace’s and Helen’s phone numbers are likely to end up in Salesforce by tomorrow, anyway.

How about I just manually run another update now?

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/16-run-update.png)

…\*Drumroll\*…

| Id | Name | My\_External\_Id\_\_c | Phone | Email |
| --- | --- | --- | --- | --- |
| 003010101010101 | Anush Amjit | ERP28190 |   |   |
| 003020202020202 | Benita Borges |   |   |   |
| 003030303030303 | Cathy Combs | ERP71892 | 333-333-3333 |   |
| 003040404040404 | Darweesh Daher | ERP10006 | 444-444-4444 |   |
| 003050505050505 | Ezra Ellis |   |   |   |
| 003060606060606 | Frances Fulvia |   |   |   |
| 003050505050505 | Grace Gao | ERP33014 | 777-777-7777 | 777@example.com |
| 003060606060606 | Hopper Helen | ERP17685 | 888-888-8888 | 888@example.com |

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/22-after-update-2.png)

\*Womp Womp\*

Grace and Helen now have phone numbers, but Helen’s name isn’t fixed … because my **business rules** I implemented said not to change anything but “phone number” in Salesforce upon update.

### 2 Options To Actually Fix It

At this point, I can either:

1. Fix Helen manually in Salesforce, or
2. Delete her from Salesforce and run the INSERT again.

I’m going to go with the latter for funsies.

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/23-deleted-helen.png)

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/19-run-insert.png)

…\*Drumroll\*…

| Id | Name | My\_External\_Id\_\_c | Phone | Email |
| --- | --- | --- | --- | --- |
| 003010101010101 | Anush Amjit | ERP28190 |   |   |
| 003020202020202 | Benita Borges |   |   |   |
| 003030303030303 | Cathy Combs | ERP71892 | 333-333-3333 |   |
| 003040404040404 | Darweesh Daher | ERP10006 | 444-444-4444 |   |
| 003050505050505 | Ezra Ellis |   |   |   |
| 003060606060606 | Frances Fulvia |   |   |   |
| 003050505050505 | Grace Gao | ERP33014 | 777-777-7777 | 777@example.com |
| 003060606060606 | Helen Hopper | ERP17685 |   | 888@example.com |

![Screenshot](https://katiekodes.com/images/screenshots/screenshot-jitterbit-cacheids/24-after-insert-2.png)

That’s better.

### Lesson Learned?

- **Q:** Is there a moral to this mistake?
- **A:** Maybe …    
  - Don’t double your work and break out a business process into “insert” and “update” operations where “upsert” will do?
  - _(That is, if your field-by-field mappings are the same for each process.)_

I really painted myself into a corner by deciding I needed a special phone-only update process as a “business rule.”

* * *

## Consolidation Pro Tip

I recommend that you kick off every individual operation with an execution of the “ **Lazy-Populate existingContactIdsByExtId** ” Jitterbit script so that it’s easy to test or run the components individually.

Then, if you’re planning to run them back-to-back in actual scheduling, make a new Script-only operation called “ **Run Me** ” with a script called “ **Execution Controller** ” that has, say, the following contents:

```
<trans>
RunScript("<TAG>Scripts/Lazy-Populate existingContactIdsByExtId</TAG>");
RunOperation("<TAG>Operations/Jitterbit Connect™/Salesforce Inserts/Insert Contacts By External ID</TAG>");
RunOperation("<TAG>Operations/Jitterbit Connect™/Salesforce Updates/Update Contacts By External ID</TAG>");
</trans>

```

Technically, when “ **Run Me** ” runs, “ **Lazy-Populate existingContactIdsByExtId** ” will run 3 times _(once because of “Execution Controller” and twice more because of the sub-operations)_, but because it’s “lazy,” it won’t actually do much the 2nd and 3rd times it runs.

I think it’s worthwhile to keep the “lazy” script as part of each sub-operation so that each sub-operation remains independently testable.

After all, it’s not like I’m running the script for every _row_ that gets data-loaded _(ahem … you **did** take that “test code” out of your row-by-row transformation logic, right?)_.

* * *

## Further Considerations

I hope this post has helped give you an architecture to handle business cases when you _do_ need to have special “insert by external ID” vs. “update by external ID” business processes.

One thing that’s really fun about this architecture is that Jitterbit couldn’t care less if you’re using a proper Salesforce external ID as the “matching” field.

I’ve done this with, say, a concatenation of `FirstName` + `"|"`+ `LastName` + `"|"` + `Email` as the “key” to my dictionary _(keep in mind that if multiple Salesforce Contacts share a name+email, you’d only have **one** result stored in your “Jitterbit cache” dictionary variable, so think through how you want to handle that, business-logic-wise)_.

Note that I don’t typically load data directly on “soft” cache-matches like that. Instead, I’m looking to surgically fill in `My_External_Id __c` in Salesforce before doing a normal “upsert” against `My_External_Id__ c`.

I’ll save that architecture for another post … so stay tuned for more Jitterbit tips!