Problem with XML Input for Bulk API when Using Relationship Fields (INVALID_FIELD_FOR_INSERT_UPDATE)

Few days ago I discovered a strange bug in Salesforce, which costed at least half a day to resolve. The bug concerns the Bulk API and XML input. Suppose you have an input with references like this:

        <sObject>
            <LastName>a1-ContactWithRelationship</LastName>
            <Account>
                <sObject>
                    <account_external_id__c>ab123</account_external_id__c>
                </sObject>
            </Account>
        </sObject>

Here I was trying to update a Contact and set the account reference to a new value.
As a response I got the following error message from Salesforce:

<errors><fields>Account</fields><message>Contact: bad field names on insert/update call: Account</message><statusCode>INVALID_FIELD_FOR_INSERT_UPDATE</statusCode></errors>

The same request sent using REST API succeeded, so I had no evidence what I am doing wrong until I found this thread:

https://developer.salesforce.com/forums/?id=906F00000008kOpIAI

It turned out that this is a bug in SF Bulk API (known for at least 5 years!). To resolve this you have to remove all whitespaces and line breaks in your XML or at least in the sObjects section. So the problem was the pretty-print and that is kind of ridiculous nowadays!

Hope, this would be helpful.

Limitations of the Bulk and REST Query operations in Salesforce (and possible workarounds)

A few days ago I received a support request from a customer of our SFDC-PI-Adapter. The customer is a big company having hundreds of thousands of records on Salesforce. They implemented a query which would return a big number of records. At first they tried the Bulk API for the query. The problem was that Salesforce does not support foreign keys in Bulk queries, so you can use only one database table, which is not appropriate in the most cases. Despite an urgent need from developers, the support for foreign keys was not implemented by Salesforce yet.

The current solution is to use the REST API for queries. Salesforce allows use of foreign keys in REST API queries. The problem is however that it returns only 2000 records as response to the GET request. The next 2000 records are linked in the so-called nextRecordsUrl which is a part of the response. You can use it for the next request, which also contains a nextRecordsUrl and so on. The adapter uses the nextRecordsUrl to get all the data of the query result. The problem is that if you have a real big amount of data, the adapter have to make a big number of GET requests (one for each 2000 records chunk), thus the receiving of query result can take quite a long time.

One possibility to improve this is to use ORDER BY and LIMIT in your queries. By doing this you can order your result records, e.g. by Id, and use the biggest Id (if you use ascending order) as additional condition in your next query. This approach has the advantage that you will have well defined amount of records returned and nearly the same execution time for each query.

Overview of SFDC APIs

The Salesforce.com (aka Force.com aka SFDC) comes with four different API's which you can use in your application. Being involved in developing of an SFDC adapter for SAP PI and having some experience in real life problems while consulting clients using the adapter, I want to share some thoughts on which API is better suited for which purpose and which you should use in your application.

So which APIs do we have in Salesforce?

The first one is REST API. REST acronym stands for REpresentational State Transfer and is a software architectural style for WWW. It works with Uniform Resource Identifiers (URIs) and uses standard HTTP methods such as HEAD, GET, POST, PATCH and DELETE. A URI points to a REST resource, which is an abstraction that can be a record or a group of records in SFDC.
The characteristics of the SFDC REST API are:

• synchronous and easy to use API
• one HTTP request for one operation (like create, upsert, delete) on a single record. So if you have to update 10000 records, you have to make 10000 HTTP calls to SFDC. Which is to keep in mind, because you can easily hit your license limit.

SOAP API:

• is a SOAP-based web services interface which can be used to process synchronously multiple (up to 200) records at a time.
• requires some experience in developing web services
• uses WSDL to provide services. In SFDC you can generate and use two kinds of WSDL: the enterprise WSDL, which is tightly bound to your company and a more generic partner WSDL.

Bulk API:

• is asynchronous. Because of it's asynchronous nature you will not receive the result of your request automatically, but have to request it actively (after some waiting time, which may be required to complete your request)
• based on REST principles
• is optimized for processing big data sets (thousands to millions of records).
• consists of batches which are processed in the background (a batch can contain a maximum of 10000 records)

Topic Subscription:

• designed to get live updates from Salesforce
• you can subscribe to objects you are interested in (e.g. ContactEmail) and you will receive live notification, if a change was made on a Contact's email
• however the topic subscription is not reliable in the sense that you will not receive all updates in case there was e.g. network problems or your system was down
• so it is nice for receiving live updates, but you have to make a regression update regularly, e.g. making a bulk query every night, to make sure that data keeps consistent.
• is quite error prone at the moment. If you establish a connection to SFDC and listen for change events, you will receive exceptions like "unknown client" from time to time, which will break the connection and requires you to reconnect again

To be precise we have one more API in SFDC, the so-called metadata API. But this one is not intended to manipulate standard objects, so it is not in the scope of this post.

Conclusion

Use REST API if you want an easy synchronous communication and you have not to much records to manipulate

Use Bulk API if you have a big number of records to manipulate (the communication in this case is asynchronous)

Use SOAP API if you have expirience in developing web services an you want a SOAP based communication, which allows you to process up to 200 records in a single call

Use Topic Subscription to receive live updates on objects in Salesforce, but keep in mind that the data consistency with Topic Subscription alone is not guaranteed

Outlook

In following posts I will go into more details on the APIs and give some implementation specifics.