Excel Spreadsheet Automation with the QUERY Formula

The CData Excel Add-In for Sugar CRM provides formulas that can edit, save, and delete Sugar CRM data. The following three steps show how you can automate the following task: Search Sugar CRM data for a user-specified value and then organize the results into an Excel spreadsheet.

The syntax of the CDATAQUERY formula is the following: =CDATAQUERY(Query, [Connection], [Parameters], [ResultLocation]);

This formula requires three inputs:

• Query: The declaration of the Sugar CRM data records you want to retrieve or the modifications to be made, written in standard SQL.

• Connection: Either the connection name, such as SugarCRMConnection1, or a connection string. The connection string consists of the required properties for connecting to Sugar CRM data, separated by semicolons.

  The User and Password properties, under the Authentication section, must be set to valid SugarCRM user credentials. This will use the default OAuth token created to allow client logins. OAuthClientId and OAuthClientSecret are required if you do not wish to use the default OAuth token.

  You can generate a new OAuth consumer key and consumer secret in Admin -> OAuth Keys. Set the OAuthClientId to the OAuth consumer key. Set the OAuthClientSecret to the consumer secret.

  Additionally, specify the URL to the SugarCRM account.

  Note that retrieving SugarCRM metadata can be expensive. It is advised that you store the metadata locally as described in the "Caching Metadata" chapter of the help documentation.

• ResultLocation: The cell that the output of results should start from.

Pass Spreadsheet Cells as Inputs to the Query

The procedure below results in a spreadsheet that organizes all the formula inputs in the first column.

1. Define cells for the formula inputs. In addition to the connection inputs, add another input to define a criterion for a filter to be used to search Sugar CRM data, such as Name.
2. In another cell, write the formula, referencing the cell values from the user input cells defined above. Single quotes are used to enclose values such as addresses that may contain spaces.
=CDATAQUERY("SELECT * FROM Accounts WHERE Name = '"&B5&"'","User="&B1&";Password="&B2&";URL="&B3&";CacheMetadata="&B4&";Provider=SugarCRM",B6)
3. Change the filter to change the data.