[Salesforce Connector] Build a correct query

[Salesforce Connector] Build a correct query

Forge Component
Published on 22 Mar by OutSystems Labs
10 votes
Published on 22 Mar by OutSystems Labs
Hi All,

I'm having some trouble with the sintax of a query.

The purpose of the process is to sync the contact table that i have on the Outsystems platform with the Contact table on Salesforce. I started creating an action that go trough all my contacts and create or update them into Saleforce, it will be a clone of my internal db.

The problem is that when i'm filtering the SalseForce table i'm receiving this message:

And this is the query that i'm using, where "Name" and "Surname" are variables that contain the information from my db. Any suggestion? tks!

" FirstName = " + Name + " and LastName = " + Surname

Hi Simon,
Try wrapping your parameters in '. The way you're sending it, Salesforce will thing that's a local variable instead of a string/text

" FirstName = '" + Name + "' and LastName = '" + Surname + "'"
It's also probably a good idea to Encode the variables, just so you don't send invalid characters. I'd say the default platform function EncodeSql will work for salesforce queries as well, but haven't tried it. It would look something like this:
" FirstName = '" + EncodeSql(Name) + "' and LastName = '" + EncodeSql(Surname) + "'"
Let us know if it solved it.
That one was a really good suggestion! thanks!
Now i have another problem, some surname are like this one D'Alessio then the result is 'D'Alessio' and it create an error. Do you have any good suggestion for this problem?

Many thanks for the help!
So it looks like the encodeSql won't do it, as Salesforce encoding syntax is different from the standard SQL. (EncodeSql replaces ' with '')

You'll need to encode the single quotes in salesforce format ( ' becomes \')

Replace(var, "'", "\'")
should do it.

So, my suggestion would be for you to create a custom outsystems function that receives a Text, and outputs the encoded value. In the function (let's call it EncodeSFParameter) you just do the Replace I shown above.

Then call it as:

" FirstName = '" + EncodeSFParameter(Name) + "' and LastName = '" + EncodeSFParameter(Surname) + "'"
Hi Goncalo,

Another good suggestion, but i have changed the function into the following:

Replace(var, "'", " ")

Using the replace with "/ ' " was still creating the error.

I'have been able to sync almost 800 contacts but it take long time and I think that now i need to increase the maximum time on the query. I'm receiving the following message after two minutes since I started the process:

"There was an error processing your request. Please try again later..."

I wrote 5 minutes into the cache, but nothing is changed.

This is the error:
12:09 (2 hours ago)
Administrator Thread was being aborted. Thread was being aborted.
   at System.Net.Connection.SyncRead(HttpWebRequest request, Boolean userRetrievedStream, Boolean probeRead)
   at System.Net.ConnectStream.ProcessWriteCallDone(ConnectionReturnResult returnResult)
   at System.Net.ConnectStream.CallDone(ConnectionReturnResult returnResult)
   at System.Net.ConnectStream.ResubmitWrite(ConnectStream oldStream, Boolean suppressWrite)
   at System.Net.HttpWebRequest.EndWriteHeaders_Part2()
   at System.Net.HttpWebRequest.EndWriteHeaders(Boolean async)
   at System.Net.HttpWebRequest.WriteHeadersCallback(WebExceptionStatus errorStatus, ConnectStream stream, Boolean async)
   at System.Net.ConnectStream.WriteHeaders(Boolean async)
   at System.Net.HttpWebRequest.EndSubmitRequest()
   at System.Net.HttpWebRequest.GetResponse()
   at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
   at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
   at OutSystems.NssSForce.SForceConnector.SforceService.update(sObject[] sObjects)
   at OutSystems.NssSForce.CssSForce.MssSForce_UpdateObject(RCSForce_SessionRecord ssSessionToken, Object ssObject, RCSForce_SaveResultRecord& ssSaveResult)
   at ssSalesforce_Connector.RssExtensionSForce.MssSForce_UpdateObject(HeContext heContext, IRecord inParamSessionToken, Object inParamObject, IRecord& outParamSaveResult)

Do you have any suggestion?

Many thanks!!
Remember you should replace ' with \' (not /'). The \ is the escape character for salesforce...

On the other topic, you might want to create your action instead on an action which is invoked by a Timer (a background job). There you can configure the Timeout (default is 20 minutes). A timer can be started on a given schedule, but also, if no schedule is set, invoked from an action -> Each Timer will have a WakeTimer action that you can call from your screen. Check documentation here: http://www.outsystems.com/help/servicestudio/9.0/Using_Timers/Use_Timers.htm

It also means you'll need to store your data somewhere (e.g. the database) so that the Timer action can access it when it runs.

In alternative - if you really want to run that logic on the page! - to extend the timeout for your particular request, you should use the HTTPRequestHandler extension's setRequestTimeout action (check how to add a reference to another module)

There you can say what is the Timeout for the current page request. The Cache in Minutes is something completely different - it says that your page will answer back the same result for a number of minutes (check doc) which is really not what you want here...

One more tip... if you're writing that many records to salesforce, you might want to use the "ContactsCreate" or "ContactsUpdate" (plural) actions. They receive a list of Contacts, instead of a single contact, and use Salesforce's Batch Create / Update API, which is better fit for creating/updating a large number of records, rather than executing a request to salesforce for each individual record... It will save you time, and also will cost you way less Salesforce API requests (which, depending on your salesforce subscription, will be limited and charged separately :-| )
Hi Goncalo,

Many thanks!!! let me check all the documentation and implement all your suggestions and then i'll let you know.


Hi Goncalo,

I'm still struggling with the Create or Update contactS it.
On my mind i would like to take all my contacts from the DB compare each one of them with the Sales Force DB and if there is anything different update SalesForce.
Each time that I need to create I'll use  "CreateContact", and for the update "UpdateContact".

I'm not able to understand how i can update a list of records. How sales force can understand which one need to be updated if it receive a list? And how can i create that list? I have tried few times but even with the contactSupdate it look like you are still updating one contact each time.

Could you please draw a sort of schema that perform this action?

morover the escape comand is still not working, but it look fine to me. I'm not able to understand what is wrong with it.


Hi Simon,
I've been traveling so it has been hard to keep up with this thread... but anyway, found some time to put up an example of an end-to-end synchronization flow, based on bulk create and/or update actions

The example module is attached, and it has some comments on it which you should be able to easily follow

Some assumptions:
  • The logic stores the last synchronization date with salesforce (in a Site Property). It fetches changed records on our local database to update them in SF. I'm not getting changed records from SF to update them locally
  • Each local record (I called it ContactLocal) has a SFId attribute, which is where the corresponding Salesforce record Identifier is stored. I assume that if a record has that SFId filled, there is a corresponding, valid, Salesforce object/contact. In that case we should update the SF record, instead of creating a new one.
The comments in the module describe the logic but it goes a little like this:
  • Get from our database all contacts that were created or changed since last synchronization date
  • Build a list of new SF Contacts (by creating a SF Contact record and adding it to a list) and a corresponding list storing the new ContactLocals. Both lists have the same "Contact" in the same order (but one list has a ContactLocal and the other a Salesforce Contact)
  • Do the same for updated contacts (in 2 other lists)
  • Call the ContactsCreate salesforce action (the one that creates multiple contacts in batch) to create ALL contacts in the list of new contacs. Iterate the responses and update each ContactLocal (in the original list, with the same index as the response array) with the just created Salesforce Id.
  • Call the ContactsUpdate salesforce action (to update multiple contacts in batch). We don't need the response to update local contacs, so I'm just auditing...
There's a sample screen to experiment, and the logic is full of audits so you can see how the synchronization process is going....

It is the "high performance" way of doing these type of synchronizations... e.g. updating 10 records or only 1 takes a constant ~350ms (which is basically the latency of that one single call to salesforce). Plus, it will only count as 1 API call to salesforce... so it will save you some extra $$s.

Hope this helps you get on the right path for a solid integration process.

You will no longer need the string escaping, as this one will use the salesforceId as the key to match "remote and local" records (instead of contacts with the same name...)

Hi Goncalo,


I took some parts of the application, but there are still few things that i'm working on, and i have almost found the solution.

If a record is updated in salesforce it has to be updated also into my local db, and if a record is updated on salesforce and on my db i need to understand which one is the most updated version. The syncronization is really hard espacially because all the contacts from salesForce are inside a Structure and not an Entity.
On top of it, the maximum amount of contats that you can send each time is 200, so you need to send them, clean the list and send other 200 again.

Thanks again for the help!!