X

Best Practices from Oracle Development's A‑Team

Implementing an SFDC Upsert Operation in ICS

Introduction

While designing SOA services; especially those ones that represent operations around a business object, a common implementation pattern used is upsert. Upsert is an acronym that means the union of "update plus insert". The idea behind is having a unique operation that decides which action to take - either update the existing record or insert a new one - based on information available in the message. Having one operation instead of two, makes the SOA service interface definition clearer and simpler.

Some SaaS applications offer upsert capabilities in their exposed services, and leveraging these capabilities can considerably decrease the amount of effort required while designing SOA services in an integration platform such as ICS. For instance, if you need to develop an upsert operation and the SaaS application does not have this functionality; you will have to implement that logic using some sort of conditional routing (see Content-Based Router pattern) or via multiple update and insert operations.

ics_cbr_sample

Figure 1: Implementing upsert using CBR in ICS.

Salesforce.com (or SFDC for short) is one of those SaaS applications that offers built-in support for the upsert operation. This post will show how to leverage this support with ICS.

Setting up External Identifiers in SFDC

Every business object in SFDC can have custom fields. This allows business objects from SFDC to be customized to afford specific customer requirements regarding data models. As part of this feature SFDC allows that any custom field can act as a record identifier for systems outside of SFDC. These systems can identify any record through this custom field instead of using the SFDC internal primary key, which for security reasons is unknown. Therefore, if you need to perform transactions against business objects in SFDC from ICS, you need to make sure that the business object carries a custom field with the External ID attribute set. This is a requirement if you want to make the upsert operation work in SFDC.

In order to create a custom field with the External ID attribute, you need to access your SFDC account and click on the setup link on the upper right corner of the screen. Once there, navigate to the left side menu and look for the build section, which is below the administer section. Within that section, expand the customize option and SFDC will list all the business objects that can be customized. Locate the business object that you want to perform the upsert operation on. This blog will use the contact business object as example.

Go ahead and expand the business object. From the options listed, choose fields. That will bring you the page that allows the fields personalization for the selected business object. In this page, navigate to the bottom of it to access the section in which you can create custom fields, as shown in figure 2.

creating_custom_field_in_sfdc_1

Figure 2: Creating custom fields for the contact business object in SFDC.

To create a new custom field, click in the new button. This will invoke the custom field creation wizard. The first step of the wizard will ask which field type you will want to use. In this example we are going to use Text. Figure 3 shows the wizard's step one. After setting the field type click next.

creating_custom_field_in_sfdc_2

Figure 3: Creating a custom field in SFDC, step one.

The second step is entering the field details. In this step you will need to define the field label, name, length and what special attributes it will have. Set the field name to "ICS_Ext_Field". The most important attribute is the External ID one. Make sure that this option is selected. Also select Required and Unique since this is a record identifier. Figure 4 shows the wizard's step two. Click next twice and then save the changes.

creating_custom_field_in_sfdc_3

Figure 4: Creating a custom field in SFDC, step two.

After the custom field creation, the next step is generating the SFDC Enterprise WSDL. This is the WSDL that must be used in ICS to connect to SFDC. The generated WSDL will include the information about the new custom field and ICS will be able to rely on that information to perform the upsert operation.

Creating a REST-Enabled Upsert Integration

In this section, we are going to develop an ICS REST-enabled source endpoint that will perform insertion and updates on the target contact business object, leveraging the upsert operation available in SFDC. Make sure to have two connections configured in ICS; one for the integration source which is REST-based and another for the integration target, which should be SFDC-based. You must have an SFDC account to properly set the connection up in ICS.

Create a new integration, and select the Map My Data pattern. From the connections palette, drag the REST-based connection onto the source icon. This will bring the new REST endpoint wizard. Fill the fields according as to what is shown in figure 5 and click next.

source_wizard_1

Figure 5: New REST endpoint wizard, step one.

Step two of the wizard will ask for the request payload file. Choose JSON Sample and upload a JSON file that contains the following payload:

request_payload_sample

Figure 6: Sample JSON payload for the request.

Click next. Step three of the wizard will ask for the response payload file. Again, choose JSON Sample and upload a JSON file that contains the following payload:

response_payload_sample

Figure 7: Sample JSON payload for the response.

Click next. The wizard will wrap up the options chosen and display for confirmation. Click on the done button to finish the wizard.

source_wizard_4

Figure 8: New REST endpoint wizard, final step.

Moving further, from the connections palette, drag the SFDC-based connection onto the target icon. That will bring the new SFDC endpoint wizard. Fill the fields according as to what is shown in figure 9 and click next.

target_wizard_1

Figure 9: New Salesforce endpoint wizard, step one.

Step two of the wizard will ask for which operation must be performed in SFDC. You need to choose the upsert operation. To accomplish that, first select the option Core in the operation type field and then select the upsert option in the list of operations field. Finally, select the business object in which you would like to perform upserts as shown in figure 10.

target_wizard_2

Figure 10: New Salesforce endpoint wizard, step two.

Click next twice and then the wizard will wrap up the options chosen and display for confirmation. Click on the done button to finish the wizard.

target_wizard_4

Figure 11: New Salesforce endpoint wizard, final step.

Save all the changes made so far in the integration. With the source and target properly configured, we can now start the mapping phase, in which we will configure how the integration will handle the request and response payloads. Figure 12 shows what we have done so far.

integration_before_mapping

Figure 12: Integration before the mapping implementation.

Create a new request mapping in the integration. This will bring the mapping editor, in which you will perform the upsert implementation. Figure 13 shows how this mapping should be implemented.

request_mapping

Figure 13: Request mapping implementation.

Let's understand the mapping implementation details. The first thing that needs to be done is set into the externalIDFieldName the field name from the business object that will be used to identify the record. You must use any valid custom field that has the External ID attribute set. Any other field will not work here. To set the value into the field, click on top of the field link to open the expression editor.

setting_external_field_value

Figure 14: Setting the "externalIDFieldName" using the expression editor.

The best way to set the value is using the concat() XLST function. Set the first parameter of the concat() function to the custom field name and the second parameter to a empty string.

Keep in mind that the field name in ICS can be different from what you set in SFDC. When the SFDC Enterprise WSDL is generated, it appends into the custom fields a suffix to make them unique. In most cases, this suffix is a "__c" but a better way to figure this out is reviewing the WSDL for the field.

The next step is making sure that the custom field cited in the externalIDFieldName field has a value set. This is necessary because that field will be used by SFDC to decide which action to take. If no value is set in that field, it means that SFDC will create a new record for that business object. Otherwise if that field has a value; then SFDC will try to locate that record and once found, it will update the record with the data set in the other fields. In this example, we will populate the custom field with the identifier value from the request payload, as shown in figure 13. Map the remaining fields accordingly. Once you finish the mapping, save the changes in click on the exit mapper button to come back to the integration.

Now create a new response mapping in the integration. This will bring the mapping editor, in which you will perform the mapping implementation for the response. Figure 15 shows how this mapping should be implemented.

response_mapping

Figure 15: Response mapping implementation.

Simply map the success field from the source with the result field from the target. According to the SFDC documentation, the success field is set to true if the operation is successfully performed into the record, and it is set to false of any issues happen during the operation. Once you finish the mapping, save the changes in click on the exit mapper button to come back to the integration. Figure 16 shows the integration after the mapping.

integration_after_mapping

Figure 16: Integration after the mapping implementation.

Finish the integration implementation by setting the tracking information and optionally mapping any faults from the SFDC connection. Save all the changes and go ahead and activate the integration in ICS. Once activated, you should be able to get the information from the REST endpoint exposed by ICS. Just access the integrations page and click in the exclamation link situated on the upper right corner of the integration entry.

checking_endpoint_details

Figure 17: Getting the information from REST endpoint exposed by ICS.

Before testing the endpoint, keep in mind that the URL of the REST endpoint does not contain the "metadata" suffix present in the end of the URL shown in figure 17. Remove that suffix before using the URL to avoid any HTTP 403 errors.

Conclusion

The upsert operation is a very handy way to handle insert and update operations within a single API, and it is a feature present in most SaaS applications that expose services for external consumption. SFDC is one of those applications. This blog showed how to leverage the upsert support found in SFDC and the steps required to invoke the upsert operation using the externalIDFieldName element from ICS.

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.Captcha