Work with Salesforce
Salesforce is a web-based Customer Relationship Management (CRM) service. This topic guides developers in the use of Logi Info with the Salesforce web service, in order to provide reporting and business intelligence based on your Salesforce data.
- What's a Web Service?
- Using Salesforce Data in a Report
- Getting a List of Available Salesforce Objects
- Reading and Writing to Salesforce
The formal definition of a web service is "a software system designed to support interoperable, machine-to-machine interaction over a network". In practical terms, developers can think of a web service as a programming API that happens to be hosted on an external machine that your program reaches over the Internet. You send it parameters and the web service sends you results, with the benefit that the whole process is language neutral.
The diagram above illustrates how this fits into the Logi application architecture. Parameters and queries sent to the web service may rely on data retrieved from local data sources. The results returned from it are used in the HTML pages that are output to a browser. Logi Studio connection elementsmake the process of connecting to, and communicating with, the web service very easy.
Web Service providers, like Salesforce, make their web services available to Internet users for a fee. This means you must sign up and pay, usually on a transaction-by-transaction basis. However, Salesforce allows developers to access their services on a trial basis for free (the "Developer Edition"). Before you can use Logi Info with Salesforce, you need to sign up at www.Salesforce.com and get your security credentials (see below).
- An account and credentials for Salesforce. Developers who want to use their free test service can get credentials at: http://developer.force.com/gettingstarted.
Note that Connection.Salesforce element uses the Salesforce API, which is not available for all versions of Salesforce. For example, it is not enabled for the Professional version. Ensure that your organization is using a version of Salesforce that makes the API available before proceeding.
Your credentials will include a "security token", which is concatenated with your Salesforce password, when configuring the Connection.Salesforce element. This token can be generated by logging into Salesforce, clicking Setup, and then going to the My Personal Information section. Click Reset Security Token to have a new token emailed to you. This token is machine address-dependent and may need to be reset if you move your application to another machine.
- When your finished application runs, it obviously needs to be able to access the Internet in order to interact with the web service. There may be firewall and security implications for you to consider.
- Logi reports built using Salesforce data can be exported to other formats, such as PDF, Excel, and Word.
Salesforce.com has discontinued support for communications using the TLS 1.0 protocol. If you're using Connection.Salesforce, or REST or SOAP API connections to Salesforce, you need to upgrade to Logi Info v12.2-SP4 or later, which supports the TLS 1.1 and 1.2 protocols, in order to continue to connect when your Salesforce instance is changed. In addition, Java applications
must use Oracle JDK 8+ or OpenJDK 8+ to use the appropriate protocol
Using data retrieved from Salesforce is very easy and straightforward. In the following example, we'll create a simple report that displays data from the sample Contact database on Salesforce:
- In the Definition Editor, in the _Settings definition, add a Connection.Salesforce element, as shown above.
- Set its attributes as shown, providing your Salesforce account credentials as required. Your Salesforce password and Salesforce security token are entered as a concatenated string. For example, if XXXXX is your password and
FiSg46Rkrp3FmbVzl is your token, the Password attribute value is: XXXXXFiSg46Rkrp3FmbVzl.
- In your report definition, add a Data Table and DataLayer.SQL element, as shown above. Set their attributes as shown.
- Set the DataLayer element's Source attribute as follows:
SELECT AccountId, FirstName, LastName, Salutation, Phone, Email, Title FROM Contact
WHERE AccountId <> '' and LastName <> 'Unknown'
Salesforce uses a special variant of SQL, the Salesforce Object Query Language (SOQL), which has some important differences from standard SQL (for example, it doesn't include DISTINCT). More information about it can be found in the SOQL Language Reference at the Salesforce website.
Because SOQL is sufficiently different from standard SQL, Logi Studio's SQL Query Builder tool does not support it.
- Set the Connection ID attribute to the ID of the Connection.Salesforce element from Step 1, as shown.
- In this example, we'll save a few keystrokes by adding an Auto Columns element instead of all of the necessary Data Table Column and Label elements. In other report definitions that don't use Auto Columns, the Salesforce data in the datalayer is available, as usual, using @Data tokens.
- Preview the report.
The preview should look something like the example shown above.
How do you determine what objects, specifically tables and columns, Salesforce makes available?
To get a list of tables, you use a special SOQL query using the command LIST TABLES. This query will return a list of table names; these names, in turn, can be subsequently used with LIST to retrieve the columns for each table. Here are some examples of the code to put this in action:
As shown above, a simple data table and DataLayer.SQL element are used to retrieve a list of tables from Salesforce. The special SQL query syntax "LIST TABLES" is used in the datalayer's Source attribute.
The results returned from Salesforce can be accessed using the special Data token @Data.rdSalesforceTable~, as shown above,
and the output will look something like the example above.
Once the table names have been acquired, they can be used in a query to retrieve a list of the columns for a particular table.
In the example above, a second data table is used to list the columns associated with one of the Salesforce tables. The SOQL query syntax is LIST <table name>, and in the example, a table name has been hard-coded into the query. A more sophisticated solution might use a table name selected from the displayed results of the previous (LIST TABLES) query to drive the second query, using a request token: LIST @Request.TableName~, for example.
And finally, as shown above, another special Data token @Data.rdSalesforceField~ is used to display the Salesforce table column information the query retrieved.
The output would be a list of columns, as shown above.
By combining the techniques shown here, developers can access data stored on Salesforce and incorporate it in their Logi applications.
Beginning with Logi Info v12.2 SP5, it's possible to use Salesforce's REST API to "write back" (Insert, Update, Delete) data to your Salesforce instance. This is a complex development task, however, and requires in-depth knowledge of both Logi Info and Salesforce.
The following discussion of operations using the Salesforce REST API assumes a security model in which a single Salesforce account (the "service" account) will be used to access Salesforce on behalf of all users of your Logi application. This account will require a standard Salesforce User License and should be restricted to limit its interactions to just the Salesforce objects to be modified. Do not use an Administrator account for this. You'll need to know the account's Salesforce password and security token value. Salesforce uses OAuth 2 security, which includes a Username-Password authentication flow mode, which will be used in this example.
The general steps for developing a Logi application that uses the API are:
- Register your Logi application as a "Connected App" in Salesforce, and get its "Consumer Key" and "Consumer Secret" values. See this Salesforce document for more information.
- You may need to create a Salesforce Custom Profile and Permission Set for the service account, in order to ensure access to the Salesforce objects you want your Logi application to interact with.
- As shown above, a Connection.REST element is used in the _Settings definition to connect to Salesforce in order to login. Note that no login credentials are configured in this element.
- In your application's Default report definition, add a Local Data element and DataLayer.REST to perform the login. Set the Local Data element's Condition attribute to "@Session.SF_AccessToken~" = "" to ensure that the access token will only be requested once per session (it has a default 4-hour lifetime).
An Http Body element is used to pass parameters during the login. The Http Body Params name-value pairs that need to be passed are shown above. The client_id and client_secret parameter values are, respectively, the Consumer Key and Consumer Secret values you noted when you created the Salesforce Connected App. The grant_type value must be password. The password parameter value is the Salesforce service account's password, concatenated with its security token. The username is the account's Salesforce login ID.
- Finally a Flattener element, with no attributes set, is used to make the returned values usable as @Local tokens.
- Next, add a Set Session Variables element and configure its parameters as shown above. This makes the values returned from a successful login available for later use as session variables.
- Back in the _Settings definition, add another Connection.REST element, to be used when making requests using the Salesforce REST API. Configure is as shown above, with one of the session variables created in the previous step.
Add a Request Header element beneath the Connection element, as shown above, and configure it as shown, using the second session variable.
Making Salesforce API Queries
Now we're ready to make a call to the Salesforce REST API, using DataLayer.REST, as shown below:
The Accept Type value is application/json once again, and the Http Method this time is GET. The Url Path attribute contains the Salesforce Object Query Language (SOQL) query and here's an example:
/services/data/v38.0/query?q=SELECT Id, FirstName, LastName, Email, SuperUser FROM SelfServiceUser WHERE ContactId = '@Request.someID~'
You'll recall that, at execution, the datalayer's Url Path attribute value will be appended to the Connection element's Url Host value to make a complete URL.
Once again, we'll need a Flattener element to shape the returned JSON data. In the case of SOQL queries, most of the time we need to tell the Flattener where to begin, by using its Top Level XPath attribute:
And in most cases, the records node, as shown above, is the correct one.
Writing to Salesforce via the API
Writing to Salesforce is somewhat similar to making queries, in that the same Connection.REST element is used and important information is placed in the URL Path attribute. However, there are some significant differences. The suggested approach is to use Process tasks, and you might think to use the Procedure.REST element to call the API.
However, when you make an API call you may want to use data it returns, but the Procedure.REST element is not a parent of the Flattener element, so you can't get the returned data into the proper format. The solution to this is to use a Procedure.Run DataLayer Rows element with a child DataLayer.REST element.
As shown above, the configured datalayer's attribute values are similar to those provided in the earlier Query example, except the Http Method is now POST. The Url Path is not a SOQL query but a direct reference to a Salesforce object endpoint, in this example, the Case object. This is the way the API expects to receive a command to insert that object.
In addition, an Http Body child element, configured as shown above, is used to pass the values to be inserted. Here's an example of the JSON data array in the Http Body Content attribute:
"AccountId" : "@Request.Acct_ID~",
"Description" : "@Request!Json.hdnDesc~",
"Product__c" : "Logi Info",
"Product_Version__c" : "@Request.inpVersion~",
"Service_Pack_Version__c" : "@Request.inpSPNo~",
"Subject" : "@Request!Json.inpSubject~"
Note the use of literal and @Request token values. Also, notice the use of the highlighted !Json token modifier. It's used when the data is free-form text and it encodes any special characters, such as double-quotes and line-feeds, so that Salesforce won't reject the Json data stream as invalid.
Once again, a Flattener is used to shape the returned data in the example but, in this case, no Top Level XPath attribute needs to be specified.
Please remember that the Salesforce API may not enforce referential integrity and therefore it may be up to you to ensure that all related tables are updated and other appropriate actions are taken for each operation, in order to ensure a complete transaction.