PaaS Partner Community

  • February 4, 2017

Integration Cloud Service (ICS) – How to use Upsert Operation by Shalindra Singh

Juergen Kress
PaaS Partner Adoption


Oracle launches ICS around April, 2015 and is being accepted by customer well. As customers started using ICS for  Salesforce Integration,  one question was observed and asked by lots of customers, is related to Upsert operation usage. We will dig deep into this in this blog.

Salesforce to ERP Integration common scenarios are –

  1. Account and Contact Sync
  2. Product and Price Sync
  3. Opportunity or Quote to Order (Sales Order)
  4. Order Status Update back to Salesforce.com
  5. Invoice Data Transfer to Salesforce.com

Other Integration flows include, History, Attachments,  Warranty, Cases, etc…

In most of the scenarios where data needs to be inserted or updated into Salesforce.com, recommended operation is Upsert to be used. When Upsert Operation is leveraged, Salesforce SOAP API automatically check based on external Id defined whether incoming record exists or not; if it does exist then record gets updated or else inserted. Click here for more information on upsert operation.

But when you start using Upsert operation with any standard or custom objects from Salesforce.com, by default, external id is not defined in Salesforce.com. So if you have decided to use upsert operation in an integration flow, first thing to do is to define / create external id in the object(s) you are planning to use with upsert operation.

Creation of External ID:

External Id is important parameter for upsert operation. To avoid data duplication external id should be created with utmost care. Best practice suggests to use foreign key (unique key) as an external key. if foreign key is composite key, external key should also be composite. It allows you to create or update a record and relate it to another existing record in a single step instead of querying the parent record ID first.

Let’s take an example here. consider I am working on Account Sync and I am bringing customers master data from ERP to Salesforce.com. So to create external id in Account object, login to Salesforce.com and then navigate to setup -> App Setup -> Customize -> Account -> Fields. To create a new fields, click on new button on this page. Select the data type (for example Text) and click to Next. Provide name for this external id field (appending ext_id in the name is considered best practice) and select external id check box on this page as shown in the below diagram. Read the complete article here.

OA & BPM Partner Community

For regular information on Oracle SOA Suite become a member in the SOA & BPM Partner Community for registration please visit www.oracle.com/goto/emea/soa (OPN account required) If you need support with your account please contact the Oracle Partner Business Center.

Blog Twitter LinkedIn image[7][2][2][2] Facebook clip_image002[8][4][2][2][2] Wiki

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.