SalesForce Connector

Salesforce.com 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?
• Requirements
• Use Salesforce Data in a Report
• Get a List of Available Salesforce Objects
• Wrie Back to Salesforce

Prior to v11.0.416, the Connection.Salesforce element was only available to Logi .NET applications. With the release of v11.0.416, it became available to Logi Java applications as well.

What's a Web Service?

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 elements make the process of connecting to, and communicating with, the web service veryeasy.

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 with Salesforce.com and get your security credentials (see below).

Requirements

In order to use Salesforce.com you will need:

1. A licensed copy of Logi Info. The Connection.Salesforce element is not available for Logi Report.
2. An account and credentials for Salesforce.com. 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.com, 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.

3. 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.
4. Logi reports built using Salesforce.com data can be exported to other formats, such as PDF, Excel, and Word.
   

Use Salesforce Data in a Report

Using data retrieved from Salesforce.com 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.com:

1. In the Definition Editor, in the _Settings definition, add a Connection.Salesforce element, as shown above.
2. 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.

3. In your report definition, add a Data Table and DataLayer.SQL element, as shown above. Set their attributes as shown.
4. 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.com uses a special variant of SQL, which they call "SOQL" (Salesforce Object Query Language), which has some important differences from standard SQL (for exapmple, 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 Query Builder tool does not support it.
    
5. Set the Connection ID attribute to the ID of the Connection.Salesforce element from Step 1, as shown.
6. 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.
7. Preview the report.

The preview should look something like the example shown above.

Getting a List of Available Salesforce Objects

How do you determine what objects, specifically tables and columns, Salesforce.com 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.com. The special SQL query syntax "LIST TABLES" is used in the datalayer's Source attribute.
 

The results returned from Salesforce.com 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.com and incorporate it in their Logi applications.

Write Back to Salesforce

Logi Info applications that use Connection.Salesforce and SOQL are not capable of performing "write back" (INSERT, UPDATE, DELETE) operations. Salesforce offers a variety of APIs that can be used with its Data Manipulation Language, which includes statements for adding and modifying data. These operations depend on security schemes, such as OAuth 2.0, which are not directly supported using Logi Info elements.

We can suggest that developing a plug-in for your Logi application is the best approach. The plug-in would provide a web service connection, using a Salesforce API, for authentication purposes and would create a session variable, based on the returned security token, for use in subsequent DML operations.

Please remember that the Salesforce API may not enforce referential integrity and therefore it may be up to the developer to ensure that all related tables are updated and other appropriate actions taken for each DML operation, in order to ensure a complete transaction.