FoxPro Programming
Talking to SalesForce
Hello,
I know this might be an old subject but I'm trying to find the best way of integration with SalesForce and again I'm too new to the idea.
We need to do some basics to start like UPSERT for Accounts & Contacts.
I was wondering if I can use SalesForce REST APIs directly from Foxpro using wwHTTP & wwJsonSerializer.
Would you please let me know if it's something practical and/or recommended or if there are better ways of doing so?
Thank you,
Kathy
I've done this with several API's (Amazon and others). Unfortunately, there are not a lot of code samples to go by. Sometimes I have to massage the wwJSON structure to conform to the API's requirements, or construct the string in code (using the 5.x version of wwJSON).
There are also some curl libraries out there which others favor - I prefer minimizing external dependencies on other programs... HOWEVER I have found the Chilkat libraries to be particularly helpful and worth the extra install. https://www.chilkatsoft.com/ and particularly https://www.example-code.com/foxpro/oauth2.asp which specifically has oAuth for Salesforce.
Thanks for your help Michael.
I think I'll have to get back to you on this for more details.
Hello friends,
I would like to share some of my experience and challenges with Salesforce and its connectivity with Foxpro using wwHTTP & wwJSONserializer.
I appreciate other experiences on this as well as any correction.
1- For using open APIs you need to sign up for the Enterprise Edition(you can use the trial ver.):
- You need to sign up for the Enterprise Edition and get a username & password
- You need to register a New App and get a Consumer Key & a Consumer Secret so login to Salesforce.
- Go to Setup (Click on the industrial gear wheel icon on the top-right corner)
- Find/Search for Apps ' App Manager ' New Connected App (from the top-right corner of the page) ; for later use go to "Manage Connected APPs" https://help.salesforce.com/articleView?id=000205876&type=1
- Complete the New Connected App form
- Click Save. The Consumer Key is created and displayed, and the Consumer Secret is created (click the link to reveal it).
- Or you may go to "App Manager" and click on the down-arrow button in front of your connected app to "View" it (NOTE: Not edit it, view it to see the Consumer Key and Consumer Secret).
2- You may start your coding and the first thing you do is to loging into your Salesforce account and get a session ID using your username & password + your connected app Consumer Key & Concumer Secret.
You can do this by using wwHTTP but NOTE that you cannot send your login request as JSON, it has to be like loHttp.cContentType = "application/x-www-form-urlencoded" as for Salesforce (as of now) doesn't support JSON for login requests using oAuth!
But you're able to get the respond in JSON format! So you can get lcAccessToken = loResultObject.access_token which is your Session ID.
Now, you should be able to use your session ID for calling the rest of the REST APIs, like Insert/Update/Delete/Upsert Contacts or Accounts or else.
You will only need to pass the correct parameters to wwHTTP like the specific URL and other parameters.
After the following NOTEs below, there is an example of how to connect to Salesforce and use the connected app for getting a session ID and do all of the above by Foxpro & wwHTTP & wwJSONserializer.
NOTE for using External ID:
You can add External IDs for your connectivity purposes.
For adding External IDs, again:
- Login to your Salesforce Account on the browser.
- Add an External ID, name it.
o Under Setup click Object Manager -> click on your considered Sobject (Contacts in our case) -> Fields & Relationships -> Click on "New" button
o Then the new CustomField setup page will come up -> Follow the steps to set it up as an External ID and Unique ID if it's required.
In our case it's MyApp_cntPK__c is our External ID.
You may use it in your codes: https://developer.salesforce.com/docs/atlas.en-us.api_rest.meta/api_rest/dome_upsert.htm
NOTE for BULK UPSERT to Salesforce:
You may not be able to see the Bulk uploaded contacts on the websiteàContacts list due to permission or etc.!
So to check and see the Bulk uploaded contacts on the Salesforce website you will need to do this:
*
Login to Salesforce
- Go to Contacts and on the top-left corner of the page change the "Recently Viewed" to "All Contacts" to see the Bulk Uploaded Contacts (Same works with Accounts/other objects) https://success.salesforce.com/answers?id=90630000000hY9zAAE
And here is some examples and please note this is not a working code:
*******************************************************************************************************************************
*** Parameter ltMethod can be: "READACT", "READCNT", "INSERTCNT", "DELETECNT", "UPDATECNT", "UPSERTCNT", "BULKUPSERT" (ACT: account, CNT: Contact)
*** NOTE: My Accounts'/Contacts' data is hard-coded for this test purpose.
*******************************************************************************************************************************
*** Links ***
*** Link for Workbench for quick tests: https://workbench.developerforce.com/ And then choose RestExplorer
*** Link for seeing the Account/Contact/... object fields/properties: In workbench type: "/services/data/v40.0/sobjects/Contact/describe" or see the documentation: https://developer.salesforce.com/docs/atlas.en-us.sfFieldRef.meta/sfFieldRef/salesforce_field_reference_Contact.htm#!
*** 0- Loging into SalesForce (Should be Enterprise Edition for Open APIs) lcUrl = "https://login.salesforce.com/services/oauth2/token" && Note: loHttp.cContentType = "application/x-www-form-urlencoded"
*** 1- Reading Accounts by Query: lcUrlAct = "https://yourInstance.salesforce.com/services/data/v40.0/query?q=SELECT+Id,Name+FROM+Account"
*** 2- Reading Contacts by Query: lcUrlCnt = "https://yourInstance.salesforce.com/services/data/v40.0/query?q=SELECT+Id,MyApp_cntPK__c,Name,Phone,AccountID+FROM+Contact"
*** 3- Inserting a Contact by cHTTPverb=POST & External ID:lcUrlCnt = "https://yourInstance.salesforce.com/services/data/v40.0/sobjects/Contact/" && Note: loHttp.cContentType = "application/json"
*** 4- Deleting a Contact by cHTTPverb=DELETE & External ID: lcUrlCnt = "https://yourInstance.salesforce.com/services/data/v40.0/sobjects/Contact/" + ["lcMyApp_cntPK__c"]+ MyApp_cntPK__c
*** 5- Updating a Contact by cHTTPverb=PATCH & External ID: lcUrlCnt = "https://yourInstance.salesforce.com/services/data/v40.0/sobjects/Contact/" + ["lcMyApp_cntPK__c"]+ MyApp_cntPK__c && Note: loHttp.cContentType = "application/json"
*** 6- Upserting a Contact by cHTTPverb=PATCH & External ID: lcUrlCnt = "https://yourInstance.salesforce.com/services/data/v40.0/sobjects/Contact/" + ["lcMyApp_cntPK__c"]+ MyApp_cntPK__c
*** 7- Bulk Upserting Contacts: See the "BULKUPSERT" Case below for it needs multiple API calls (Creating a Job, Submitting a Batch, Closing the Job and probably monitoring the status)
*******************************************************************************************************************************
LPARAMETERS ltMethod
LOCAL lcMyApp_cntPK__c, lcSalesforceApiVersion, lcGrantType, lcClient_id, lcClient_secret, lcUserName, lcPassword
lcMyApp_cntPK__c = "WXUTY" && Hard-Coded External ID
lcSalesforceApiVersion = "v40.0" && The hard coded "v40.0" ones should be replaced with lcSalesforceApiVersion
lcGrantType = "password"
*** The Client_id and the Secret key are comming from our Connected APP as mentioned above. ***
lcClient_id = "TEST9g9rbsTkKnAX7NhL8VAIThQnu0YoWcNUinqBAWwk3PPDIQ3U9VwSn7fRK5_7Jey_wdVNbV26v40XWubwH"
lcClient_secret = "6316559857453632126"
lcUserName = "myemail@mycompany.com"
lcPassword = "MY3i5PASS"
*******************************************************************************************************************************
*** 0- Loging into SalesForce ***
*******************************************************************************************************************************
lcUrl = "https://login.salesforce.com/services/oauth2/token"
lcEncodedData = "grant_type="+lcGrantType + "&client_id="+lcClient_id + "&client_secret="+lcClient_secret + "&username="+lcUserName +"&password="+lcPassword
DO wwHttp
DO wwJsonSerializer
loHTTP=CREATEOBJECT("wwHttp")
loHttp.cContentType = "application/x-www-form-urlencoded"
loHttp.nHttpPostMode = 4
loHttp.AddPostKey(lcEncodedData)
loSer = CREATEOBJECT("wwJsonSerializer")
lcJsonResult = loHTTP.HTTPGet(lcURL)
loSer.FormattedOutput = .T. && Please do a revision for this. Has to be after serializing!
loResultObject = loSer.DeserializeJson(lcJsonResult)
lcID= loResultObject.id
lcIssued_at = loResultObject.issued_at
lcInstanceUrl = loResultObject.instance_url
lcAccessToken = loResultObject.access_token
lcTokenType = loResultObject.token_type && lcTokenType is "Bearer"
lcSignature = loResultObject.signature
*** END OF Loging into SalesForce ***
*******************************************************************************************************************************
*****************************************************************************************************************************************
*** You can keep using the same HTTP object for the rest of the REST API calls.
*** you will need to ADDHeader the Access_Token(Session_ID) and adjust the url according to what you need.
*** Also you will probably need to adjust loHttp.cHTTPVerb and loHttp.cContentType according to what you need and Salesforce documentation on using APIs.
*****************************************************************************************************************************************
loHTTP.AddHeader("Authorization: "+ lcTokenType + " " + lcAccessToken)
DO CASE
CASE ltMethod = "READACT"
*******************************************************************************************************************************
*** 1- Reading Accounts ***
*******************************************************************************************************************************
lcUrlAct = lcInstanceUrl + "/services/data/v40.0/query?q=SELECT+Id,Name+FROM+Account"
lcJsonActResult = loHTTP.HTTPGet(lcUrlAct)
lcJsonActResultObj= loSer.DeserializeJson(lcJsonActResult)
? lcJsonActResultObj
? lcJsonActResultObj.done
? lcJsonActResultObj.records
? lcJsonActResultObj.totalsize
LOCAL i
CREATE CURSOR cAccounts (;
Name c(100),;
ID c(20))
FOR i=1 TO lcJsonActResultObj.totalsize
m.Name = lcJsonActResultObj.records.Item(i).name
m.ID = lcJsonActResultObj.records.Item(i).ID
INSERT INTO cAccounts FROM MEMVAR
ENDFOR
*******************************************************************************************************************************
*** END OF Reading Accounts ***
*******************************************************************************************************************************
CASE ltMethod = "READCNT"
*******************************************************************************************************************************
*** 2- Reading Contacts ***
*******************************************************************************************************************************
lcUrlCnt = lcInstanceUrl + "/services/data/v40.0/query?q=SELECT+Id,Name,Phone,AccountID+FROM+Contact"
lcJsonCntResult = loHTTP.HTTPGet(lcUrlCnt)
lcJsonCntResultObj = loSer.DeserializeJson(lcJsonCntResult)
? lcJsonCntResultObj
? lcJsonCntResultObj.done
? lcJsonCntResultObj.records
? lcJsonCntResultObj.totalsize
LOCAL i
CREATE CURSOR cContacts (;
Name c(100),;
ID c(20),;
AccountID c(30))
FOR i=1 TO lcJsonCntResultObj.totalsize
m.Name = lcJsonCntResultObj.records.Item(i).name
m.ID = lcJsonCntResultObj.records.Item(i).ID
m.AccountID = lcJsonCntResultObj.records.Item(i).AccountID
INSERT INTO cContacts FROM MEMVAR
ENDFOR
*******************************************************************************************************************************
*** END OF Reading Contacts ***
*******************************************************************************************************************************
CASE ltMethod = "INSERTCNT"
*******************************************************************************************************************************
*** 4- Inserting Contact + Setting MyApp ID: && IMPORTANT: Before doing this, External Key has to be added in Salesforce(go to your salesforce page on the browser go to Setup)->Setup->Sobject->Fields & Relationships->New->CustomField->(Then follow the steps to set up): MyApp_cntPK__c
*** Using POST for Insert will give a "MyApp_cntPK__c duplicate value" message if it finds duplication, PATCH won't give any message! So keep PATCH for UPSERT ***
*******************************************************************************************************************************
lcUrlCntAdd = lcInstanceUrl + "/services/data/v40.0/sobjects/Contact"
loHttp.cHTTPVerb = "POST"
loHttp.cContentType = "application/json"
loSer = CREATEOBJECT("wwJsonSerializer")
loContact = CREATEOBJECT("Empty")
ADDPROPERTY(loContact,"FirstName","TestFirstName")
ADDPROPERTY(loContact,"LastName","TestLastName")
ADDPROPERTY(loContact,"AccountID","0011I000004Tw7test") && AccountID is hard-coded to create the new contact under my specific account.
ADDPROPERTY(loContact,"MyApp_cntPK__c",lcMyApp_cntPK__c) && Contact ID is READ-ONLY generated by Salesforce. We're setting our External ID for the connectivity.
*** Properties are always rendered lower case so we should make sure these properties render in proper case so we use the following method:
loSer.PropertyNameOverrides = "FirstName,LastName,AccountID,MyApp_cntPK__c"
lcJson = loSer.Serialize(loContact)
loHTTP.AddPostKey(lcJson)
lcJsonCntInsertResult = loHTTP.HTTPGet(lcUrlCntAdd)
lcJsonCntInsertResultObj = loSer.DeserializeJson(lcJsonCntInsertResult)
IF AT("success",lcJsonCntInsertResult) > 0
? lcJsonCntInsertResultObj.success
ELSE
? lcJsonCntInsertResultObj.item(1).message
ENDIF
*******************************************************************************************************************************
*** END OF Inserting Contact ***
*******************************************************************************************************************************
CASE ltMethod = "DELETECNT"
*******************************************************************************************************************************
*** 3- Deleting Contact by External ID; MyAppID ***
*******************************************************************************************************************************
lcUrlCnt = lcInstanceUrl + "/services/data/v40.0/sobjects/Contact"
lcContactMyAppID = "MyApp_cntPK__c" + "/" + lcMyApp_cntPK__c
lcUrlCntDelete = lcUrlCnt + "/"+ lcContactMyAppID
loHttp.cHTTPVerb = "DELETE"
lcJsonCntDeleteResult = loHTTP.HTTPGet(lcUrlCntDelete)
? lcJsonCntDeleteResult
&& If it's successful Deleting the contact record, there's no message and the result is empty!
&& If the contact is already deleted, the message/errorCode will be "ENTITY_IS_DELETED" which means the IsDeleted property is ture!
*******************************************************************************************************************************
*** END OF Deleting Contact ***
*******************************************************************************************************************************
CASE ltMethod = "UPDATECNT"
*******************************************************************************************************************************
*** 5- Updating Contact by External ID; MyAppID ***
*******************************************************************************************************************************
lcUrlCnt = lcInstanceUrl + "/services/data/v40.0/sobjects/Contact"
lcContactMyAppID = "MyApp_cntPK__c" + "/" + lcMyApp_cntPK__c
lcUrlCntUpdate = lcUrlCnt + "/"+ lcContactMyAppID
loHttp.cHTTPVerb = "PATCH"
loHttp.cContentType = "application/json"
loSer = CREATEOBJECT("wwJsonSerializer")
*** Properties are always rendered lower case so we should make sure these properties render in proper case so we use the following method:
loSer.PropertyNameOverrides = "FirstName,LastName,AccountID,MyApp_cntPK__c"
loContact = CREATEOBJECT("Empty")
ADDPROPERTY(loContact,"FirstName","TestFirstName")
ADDPROPERTY(loContact,"LastName","TestLastName")
ADDPROPERTY(loContact,"Phone","1-800-xxx-xxxx")
ADDPROPERTY(loContact,"AccountID","0011I000004Tw7test")
lcJson = loSer.Serialize(loContact)
loHTTP.AddPostKey(lcJson)
lcJsonCntUpdateResult = loHTTP.HTTPGet(lcUrlCntUpdate)
? lcJsonCntUpdateResult
*******************************************************************************************************************************
*** END OF Updating Contact ***
*******************************************************************************************************************************
CASE ltMethod = "UPSERTCNT"
*******************************************************************************************************************************
*** 6- Upserting Contact by External ID; MyAppID ***
*** Duplicated ones get updated and not inserted ***
*******************************************************************************************************************************
lcUrlCnt = lcInstanceUrl + "/services/data/v40.0/sobjects/Contact"
lcContactMyAppID = "MyApp_cntPK__c" + "/" + lcMyApp_cntPK__c
lcUrlCntUpdate = lcUrlCnt + "/"+ lcContactMyAppID
? lcUrlCntUpdate
loHttp.cHTTPVerb = "PATCH"
loHttp.cContentType = "application/json"
loSer = CREATEOBJECT("wwJsonSerializer")
*** Properties are always rendered lower case so we should make sure these properties render in proper case so we use the following method:
loSer.PropertyNameOverrides = "FirstName,LastName,AccountID,MyApp_cntPK__c"
loContact = CREATEOBJECT("Empty")
ADDPROPERTY(loContact,"FirstName","TestFirstName")
ADDPROPERTY(loContact,"LastName","TestLastName")
ADDPROPERTY(loContact,"Phone","1-416-xxx-xxxx")
ADDPROPERTY(loContact,"AccountID","0011I000004Tw7mQAC")
lcJson = loSer.Serialize(loContact)
loHTTP.AddPostKey(lcJson) && this sets the entire post buffer
lcJsonCntUpdateResult = loHTTP.HTTPGet(lcUrlCntUpdate)
? lcJsonCntUpdateResult
*******************************************************************************************************************************
*** END OF Upserting Contact ***
*******************************************************************************************************************************
CASE ltMethod = "BULKUPSERT"
*******************************************************************************************************************************
*** 7- Insert in bulk by BULK APIs Create a Job, Submit Batch, Close Job, Batch Status, if numberRecordsFailed is 0, then we're done! ***
*** Create a Job: POST /services/async/40.0/job
*** Submit Batch: POST /services/async/40.0/job/750280000018ch2AAA/batch
*** Close Job: POST /services/async/40.0/job/750280000018ch2AAA
*** Batch Status: GET /services/async/40.0/job/750280000018ch2AAA/batch/75128000001RukcAAC
*** As an example, we're doing a Bulk Data Load by Salesforce Bulk APIs passing our cursors as JSON using wwJSONserializer ***
*******************************************************************************************************************************
***************************
*** Creating a Job ***
***************************
LOCAL lcBulkApiJobID, lcCntBulkBatchId, lcUrlBulkJobStatus
lcBulkApiJobID = ""
lcCntBulkBatchId = ""
lcUrlBulkJobStatus = ""
lcUrlBulkJob = lcInstanceUrl + "/services/async/40.0/job"
*** According to Salesforce: The operation value must be all lower case.
*******************
TEXT TO lcJobJson
{"operation" : "upsert","object" : "Contact","externalIdFieldName" : "MyApp_cntPK__c","contentType" : "JSON"}
ENDTEXT
*******************
DO wwHttp
loHTTP=CREATEOBJECT("wwHttp")
loHTTP.AddHeader("X-SFDC-Session:",lcAccessToken)
loHttp.cContentType = "application/json"
loSer = CREATEOBJECT("wwJsonSerializer")
loHTTP.AddPostKey(lcJobJson)
lcJsonBulkJobResult = loHTTP.HTTPGet(lcUrlBulkJob)
? lcJsonBulkJobResult
lcJsonBulkJobResultObj = loSer.DeserializeJson(lcJsonBulkJobResult)
lcBulkApiJobID = lcJsonBulkJobResultObj.id
****************************
*** Submit Contact Batch ***
****************************
lcUrlBulkJobBatch = lcInstanceUrl + "/services/async/40.0/job/"+ lcBulkApiJobID +"/batch"
OPEN DATABASE ap
use("MyTable")
SELECT MyTable_FirstName as FirstName, MyTable_LastName as LastName, MyTable_phone as Phone, ;
MyTable_cellphone as MobilePhone, MyTable_fax as Fax, MyTable_email as Email, ;
"0011I000004Tw7Test" as AccountID, MyTable_pk as MyApp_cntPK__c;
FROM MyTable ;
INTO CURSOR c_CntBatch READWRITE
loSer = CREATEOBJECT("wwJsonSerializer")
*** Properties are always rendered lower case so we should make sure these properties render in proper case so we use the following method:
loSer.PropertyNameOverrides = "FirstName,LastName,Phone,Email,AccountID,MyApp_cntPK__c"
lcCntBulkJson = loSer.Serialize("cursor:c_CntBatch")
loHTTP.AddPostKey(lcCntBulkJson)
loHttp.cHTTPVerb = "POST"
lcJsonCntBulkResult = loHTTP.HTTPGet(lcUrlBulkJobBatch)
? lcJsonCntBulkResult
lcJsonCntBulkResultObj = loSer.DeserializeJson(lcJsonCntBulkResult)
? lcJsonCntBulkResultObj.state
lcCntBulkBatchId = lcJsonCntBulkResultObj.id
****************************
*** Status of a Job ***
****************************
IF !EMPTY(lcBulkApiJobID) AND !EMPTY(lcCntBulkBatchId)
lcUrlBulkJobStatus = lcInstanceUrl + "/services/async/40.0/job/" + lcBulkApiJobID + "/batch/" + lcCntBulkBatchId
loHttp.cHTTPVerb = "GET"
lcJsonCntBulkStatusResult = loHTTP.HTTPGet(lcUrlBulkJobStatus)
? lcJsonCntBulkStatusResult
lcJsonCntBulkStatusResultObj = loSer.DeserializeJson(lcJsonCntBulkStatusResult)
? lcJsonCntBulkStatusResultObj.state
? lcJsonCntBulkStatusResultObj.numberrecordsfailed
ENDIF
****************************
*** Close a Job ***
****************************
lcUrlBulkJobClose = lcInstanceUrl + "/services/async/40.0/job/" + lcBulkApiJobID
*******************
TEXT TO lcJobCloseJson
{"state" : "Closed"}
ENDTEXT
*******************
loHTTP.AddPostKey(lcJobCloseJson)
loHttp.cHTTPVerb = "POST"
lcJsonBulkJobCloseResult = loHTTP.HTTPGet(lcUrlBulkJobClose)
? lcJsonBulkJobCloseResult
*******************************************************************************************************************************
*** END OF Bulk Upserting Contact ***
*******************************************************************************************************************************
ENDCASE
RETURN