posts - 43 , comments - 2 , trackbacks - 0

Tuesday, June 30, 2015

Connect to Salesforce Data as a Linked Server

Use the TDS Remoting feature of the ODBC Driver to set up a linked server for Salesforce data.

You can use the TDS Remoting feature to set up a linked server for Salesforce data. After you have started the daemon, you can use the UI in SQL Server Management Studio or call stored procedures to create the linked server. You can then work with Salesforce data just as you would a a linked SQL Server instance.

Configure the DSN

If you have not already done so, specify connection properties in a DSN (data source name). You can use the Microsoft ODBC Data Source Administrator to create and configure ODBC DSNs. This is the last step of the driver installation. See the "Getting Started" chapter in the help documentation for a guide to setting the required properties in the Microsoft ODBC Data Source Administrator.

Configure the TDS Daemon

The TDS Remoting feature of the ODBC driver enables you to create a linked server for Salesforce. The ODBC driver runs a daemon as a service that listens for TDS requests from clients. The daemon can be configured in a configuration settings file and through the CLI (command-line interface). Follow the steps below to use the configuration settings file to configure the DSN, SSL, access control, and other settings:

  1. Open the CData.ODBC.Salesforce.Remoting.ini file, located in the remoting subfolder in the installation directory.
  2. In the tdsd section, configure the settings for the TDS server:

    [tdsd]
    port = 1434
    maxc = 20
    session-timeout = 20
    logfile = SalesforceRemotingLog.txt
    verbosity = 2
    ssl-cert = "CData.ODBC.Salesforce.Remoting.pfx"
    ssl-subject = "*"
    ssl-password = "test"

    Note: By default, the daemon runs on port 1433, the default SQL Server port. If you already have SQL Server running on port 1433, change the default value for the port.

  3. In the databases section, define the catalog name and set it to the DSN:

    [databases]
    ;The database settings, default to installed system DSN name, odbc connection string is acceptable also.
    CDataSalesforce = DSN=CData Salesforce Source
  4. In the acl section, add users that are allowed to connect to the linked server:

    [acl]
    CDataSalesforce = admin
  5. In the users section, define passwords for authorized users. Below are the default values:

    [users]
    ;Passwords
    admin = test
  6. Start the service the daemon is running under. You can start the service from the Services Snap-In: Click Start -> Run and enter services.msc. Right-click the CData Salesforce TDS Remoting service and click Start.

Create a Linked Server for Salesforce Data

After you have configured and started the daemon, create the linked server and connect. You can use the UI in SQL Server Management Studio or call stored procedures.

Create a Linked Server from the UI

Follow the steps below to create a linked server from the Object Explorer.

  1. Open SQL Server Management Studio and connect to an instance of SQL Server.
  2. In the Object Explorer, expand the node for the SQL Server database. In the Server Objects node, right-click Linked Servers and click New Linked Server. The New Linked Server dialog is displayed.
  3. In the General section, click the Other Data Source option and enter the following information after naming the linked server:
    • Provider: Select SQL Server Native Client 10.0 in the menu.
    • Product Name: Enter a name for the data source.
    • Data Source: Enter the host and port the daemon is running on.
    • Provider String: Enter the following connection string:
      Network Library=DBMSSOCN;
    • Catalog: Enter the catalog you defined in the databases section of the configuration settings file.
  4. In the Security section, select the Be Made Using this Security Context option and enter the username and password of a user you authorized in the acl section of the configuration settings file.

Create a Linked Server Programmatically

In addition to using the SQL Server Management Studio UI to create a linked server, you can use stored procedures:

  1. Call sp_addlinkedserver to create the linked server:

    EXEC sp_addlinkedserver @server='Salesforce',
    @srvproduct='CData.Salesforce.ODBC.Driver',
    @provider='SQLNCLI10',
    @datasrc='localhost,1434',
    @provstr='Network Library=DBMSSOCN;',
    @catalog='CDataSalesforce';
    GO
  2. Call sp_droplinkedsvrlogin to remove the default mappings created by sp_addlinkedserver. By default, sp_addlinkedserver maps all local logins to the linked server.

    EXEC sp_droplinkedsrvlogin @rmtsrvname='Salesforce',
    @locallogin=NULL;
    GO
  3. Call the sp_addlinkedsrvlogin stored procedure to allow SQL Server users to connect with the credentials of an authorized user of the daemon. Note that the credentials you use to connect to the daemon must exist in the daemon's configuration settings file.

    EXEC sp_addlinkedsrvlogin @rmtsrvname='Salesforce',
    @rmtuser='admin',
    @rmtpassword='test',
    @useself='FALSE',
    @locallogin='YOUR-DOMAIN\your-user';
    GO

Connect from SQL Server Management Studio

SQL Server Management Studio uses the SQL Server Client OLE DB provider which requires the ODBC driver to be used inprocess. You must enable the 'Allow inprocess' option for the SQL Server Client Provider in Management Studio to query the linked server from SQL Server Management Studio. To do this, open the properties for the provider you are using under Server Objects -> Linked Servers -> Providers. Check the 'allow inprocess' option and save the changes.

Execute Queries

You can now execute queries to the Salesforce linked server from any tool that can connect to SQL Server. Set the table name accordingly:

SELECT * FROM [linked server name].[CDataSalesforce].[Salesforce].[Account]

Posted On Tuesday, June 30, 2015 2:15 AM | Comments (0) | Filed Under [ salesforce cdata ODBC Linked Server ]

Friday, June 26, 2015

How to Access Data from a SharePoint List Based on a Custom View

The CData ADO.NET Providers for SharePoint you to integrate live SharePoint data with other applications. For example, Visual Studio provides built-in support for ADO.NET data sources. This article shows how to use Server Explorer in Visual Studio to access the data screened by a SharePoint custom view.

Implementing Access Control with a Database Query

The CData ADO.NET provider's data model exposes each SharePoint list as a separate table; all custom views are available by querying the Views table. You can implement the settings for the custom view that you defined in SharePoint by selecting data with the ID of the custom view:

  1. If you have not already done so, establish a connection to SharePoint: In Server Explorer, right-click the Data Sources node and click Add Connection.

    See the "Getting Started" guide chapter in the help documentation for a guide to the required connection properties and how to set them in Server Explorer.

  2. Retrieve the unique identifier for the custom view: Query the Views table and specify the custom list for the view. For example, the query below retrieves all custom views for the custom list MyCustomList:
    SELECT * FROM Views WHERE List='MyCustomList'
  3. Query the custom list table to retrieve the custom view. Specify the ViewId in the WHERE clause. For example, to retrieve a custom view from MyCustomList, use the following statement:
    SELECT * FROM MyCustomList WHERE ViewId='your-ViewId'

Posted On Friday, June 26, 2015 3:21 AM | Comments (0) | Filed Under [ ado.net sharepoint cdata ]

Connect to Nonstandard OData Services

The CData ADO.NET Provider for OData enables you to expose Web services as a fully managed ADO.NET data source. It allows you to access almost any OData service from native ADO.NET tools. You can follow the procedure below to access OData sources that do not conform exactly to the OData protocol. This article will add support for the Microsoft Research service, which does not implement some common functionality, including support for retrieving metadata.

Define a Custom Schema File

The CData providers allow you to write custom schema files that define the metadata for the tables in your data source. This is useful when accessing OData services that do not implement the "$metadata" service metadata document. Additionally, storing metadata locally increases performance because it does not need to be retrieved from the data source each time.

Follow the steps below to create a schema file for the Downloads table in the Microsoft Research service.

  1. Use an existing schema as a template: Navigate to the db folder in the installation directory and make a copy of the sys_data.rsd schema file. Name the new file the same name as the table you want to connect to.
  2. In the new file (Downloads.rsd), delete all the rows between the rsb:info tags. On the rsb:info node, change the Title attribute to match the name of the file (without the .rsd extension).
  3. Get the listing of columns by visiting the URL for the table in your browser, for example, http://odata.research.microsoft.com/odata.svc/Downloads. There should be an XML document returned with all the tables we are ultimately interested in retrieving with the OData data provider. You might need to view the page source here to see the actual XML in your browser. Look for the m:properties node under one of the entries returned. Each of the child elements here can be a field in the schema.
  4. Define columns for the fields that you want to have access to: Define an attr entry that (at a minimum) has the name and xs:type attributes. Here's a basic example for the Downloads table:
    <attr name="ID"          xs:type="integer"  key="true" readonly="true" description="The primary key for the Downloads table." ></attr>
    <attr name="Name"        xs:type="string" ></attr>
    <attr name="Downloads"   xs:type="long" ></attr>

    The preceding example uses the following optional attributes:

    • key: This attribute marks the field as the primary key. It is not necessary to have a primary key if the OData service is read-only.
    • readonly: If set to "true", this attribute disallows updates for this field.
    • description: This attribute provides a description.

    To specify complex data types, such as arrays, you can add the following attributes:

    • other:int_ColumnName: To denote child elements, set this attribute to the path of the field. Use periods instead of slashes.
    • other:datasourcedatatype: This attribute specifies the type of the root element followed by the EDM type of the child element.

    For example, on the Northwind Suppliers table, the other:int_ColumnName of "Address_Street" is "Address.Street" and the other:datasourcedatatype is "ODataDemo.Address.Edm.String".

  5. Add the following line exactly as is in order to support paging.
  6. <input name="rows@next" desc="A system column used for paging. Do not change.">

You can find the complete script below.

<rsb:script xmlns:rsb="http://www.cdata.com/ns/rsbscript/2">
<rsb:info title="Downloads" description="This is an example table showing how build a custom schema file to connect to an OData source that does not conform exactly to the OData protocol.">
<attr name="ID"               xs:type="integer"  key="true" />
<attr name="Name"             xs:type="string"  ></attr>
<attr name="Downloads"        xs:type="long"  ></attr>
<attr name="FileName"         xs:type="string"  ></attr>
<attr name="FileSize"         xs:type="integer"  ></attr>
<attr name="Description"      xs:type="string"  ></attr>
<attr name="Version"          xs:type="string"  ></attr>
<attr name="Picture"          xs:type="string"  ></attr>
<attr name="ResearchAreas"    xs:type="string"  ></attr>
<attr name="Tags"             xs:type="string"  ></attr>
<attr name="URL"              xs:type="string"  ></attr>
<attr name="Eula"             xs:type="string"  ></attr>
<attr name="DateUpdated "     xs:type="datetime"  ></attr>
<attr name="DateCreated"      xs:type="datetime"  ></attr>
 
<input name="rows@next" description="A system column used for paging. Do not change." />
</rsb:info>
 
<rsb:script method="GET">
<rsb:call op="odataadoExecuteSearch" in="_input">
<rsb:push />
</rsb:call>
</rsb:script>
 
<rsb:script method="MERGE">
<rsb:call op="odataadoExecuteUpdate" input="_input">
<rsb:push />
</rsb:call>
</rsb:script>
 
<rsb:script method="POST">
<rsb:call op="odataadoExecuteInsert" input="_input">
<rsb:push />
</rsb:call>
</rsb:script>
 
<rsb:script method="DELETE">
<rsb:call op="odataadoExecuteDelete" input="_input">
<rsb:push />
</rsb:call
</rsb:script>
</rsb:script>

Query the Table

After adding columns, you can now use them in SELECT queries. If you do not need INSERT, UPDATES, or DELETEs, you can remove the rsb:script elements for POST, MERGE, and DELETE respectively.

To use the schema file with any CData Data Provider, set the Location connection property to the folder containing this file.

Note that the default Id will be a URL of the OData item. If you have an Id field defined in the m:properties element for the service that you would rather use, you can set the "Use Id URL" connection string property to False.

Add INSERT, UPDATE, and DELETE Support

The Microsoft service in the example does not support INSERT, UPDATE, or DELETE because it does not provide a category node. For data sources that assign category elements to entries, you can get this support by setting the _input.entityname and _input.schemanamespace inputs. These values will be hard-coded for the particular table. Set these inputs to the following values:

  • _input.entityname: Set this input to the name of the table.
  • _input.schemanamespace: Set this input to the category element for an entry from the table you are interested in. If the Microsoft service supported data manipulation queries, you could get the schema namespace by searching for a category node at the following URL: http://odata.research.microsoft.com/odata.svc/Downloads. The value for the Microsoft Download table would be "OData.Models.Download".

Once you have these values, set the following two lines directly after the closing tag but before the rsb:script method="GET" tag:

<rsb:set attr="_input.entityname" value="Downloads" />
<rsb:set attr="_input.schemanamespace" value="OData.Models.Download" />

Posted On Friday, June 26, 2015 3:18 AM | Comments (0) | Filed Under [ ado.net odata cdata OData Services ]

Monday, June 15, 2015

Design a Custom SAP Function Module to Circumvent RFC_READ_TABLE Limitations

While the SAP SDK provides the RFC_READ_TABLE for accessing data from SAP tables, using it to select data is limited to 512 bytes at a time so you may not be able to retrieve all columns. You can resolve this limitation by defining a custom function module that will allow you to select any result set you need.

Posted On Monday, June 15, 2015 2:01 AM | Filed Under [ cdata jdbc ODBC ssis SAP netweaver ]

LINQ to QuickBooks Data

LINQ provides general-purpose query facilities in .NET Framework 3.0 and above and provides one easy way to programmatically access data through from CData Data Providers for ADO.NET. This example uses LINQ to access information from the QuickBooks Data Provider.

Posted On Monday, June 15, 2015 2:16 AM | Filed Under [ .net quickbooks cdata LINQ .Net Framework ]

Friday, June 12, 2015

Access QuickBooks Online Data in SharePoint External Lists

Provide your QuickBooks Online data to SharePoint users as an external list.

You can use the CData Cloud Driver for QuickBooks Online to give your users the capabilities to access and update QuickBooks Online data in SharePoint. This article shows how to create an external list of QuickBooks Online data that is always up to date. You will use an external content type as a template to create the external list. The external content type enables connectivity over Web services, the protocol that the cloud driver uses to provide access to live QuickBooks Online data.

Creating an external list with connectivity to QuickBooks Online data consists of three basic steps:

  1. Create the External Content Type
  2. Import the External Content Type
  3. Create the External List

This article also covers how to accomplish the following tasks:

Create the External Content Type

The external content type is a schema that will provide the core connectivity to QuickBooks Online data from any SharePoint app. You can create a schema for any collection of QuickBooks Online entities. You can pass in the required options with the $sharepoint query string parameter. Below is an example request, which will return the schema in an .ect file:

https://my-server:8032/api.rsc/Customers?$filter=FullyQualifiedName eq 'Cook, Brian'&$sharepoint=AuthMode:Passthrough&@authtoken=my-authtoken

Import the External Content Type

After you have created the .ect, you can follow the steps below to import it into SharePoint Online or an on-premise SharePoint installation.

SharePoint Online

In the SharePoint admin center, click bcs from the quick launch menu and then click Manage BDC Models and External Content Types. On the resulting page, click Import.

SharePoint 2013

Navigate to the SharePoint central administration portal and click the link to manage service applications. In the resulting page, click Business Data Connectivity Service. Then click Import -> Choose File to select the .ect file in the dialog.

Create the External List

You can now create SharePoint apps that can access and modify QuickBooks Online data. Navigate to your SharePoint site and choose Site Contents -> Add an App -> External List. Then, click the Select External Content Type icon and choose the external content type that you created in the previous section. Click Create.

The external list. (Salesforce with SharePoint 2013 is shown.)

Limit Results

SharePoint has limits on how much data can be retrieved from external lists of OData sources. External lists display results in pages of 30 items by default. To modify the paging size, you can set the Item Limit property in the settings for the default view. Alternatively, you can build a custom Web part to view the data from the external list.

In SharePoint 2013, requests to external data sources are limited by bandwidth throttling controls, which can be changed using the Set-SPBusinessDataCatalogThrottleConfig command. In SharePoint Online, requests to external data sources are limited by your Server Resource Quota and by bandwidth throttling controls. To work around this, you can apply filters in the request for data.

The cloud driver sets the default limit for the number of returned rows to be 500. You can disable this limit by adding the limit option to the $sharepoint query string parameter and setting its value to 0.

Configure Pass-Through Authentication

To use pass-through authentication for accessing your external content type, set the AuthMode option to Passthrough.

If your users use Kerberos authentication to connect to SharePoint, you need to add these users to the cloud driver.

If you are not using Kerberos authentication, you are likely using another form of Windows authentication such as NTLM. When using pass-through authentication in the Business Connectivity Service (BCS) in SharePoint and users are authenticating via NTLM authentication, SharePoint connects to the external Web service using the default IIS account, which is often the NT AUTHORITY\IUSR account, so you need to give the IUSR user access to the QuickBooks Online Cloud Driver.

See the help documentation for a guide to enabling Windows authentication for the cloud driver.

Configure Credentials Authentication

In the following sections, you will first create a secure store target application that authenticates SharePoint users to the cloud driver with the credentials for a user who has been added to the cloud driver. Next, you will create the external content type and configure it to authenticate with the credentials in the secure store.

SharePoint Online

  1. In the SharePoint Online administration center, click secure store from the quick launch bar and then click New.
  2. In the Target Application Settings section, enter the target application Id, display name, and contact email.
  3. In the Credential Fields section, add the credentials for a user added in the cloud driver.
  4. In the Target Application Administrators section, choose an administrator who can access the connection settings for the external content type. The account for the SharePoint Online administrator is usually specified here.
  5. In the Members section, enter the users in SharePoint who are authorized to access QuickBooks Online data.
    The configuration page for the new target application in SharePoint Online. (Salesforce is shown.)

After you create the target application, save the credentials of a cloud driver user into the secure store:

  1. In the quick launch bar, click secure store.
  2. Click Set Credentials in the menu for the target application.
  3. Enter the username and password to store the credentials of a user added in the connector.

Next, create a new connection settings object:

  1. Click bcs from the quick launch bar and then choose the option to manage connections to online services.
  2. Click Add. Name the connection and enter the URL of the OData endpoint, https://my-server/api.rsc.
  3. Select the option to use credentials stored in SharePoint. Enter the application Id for the target application you created.

Finally, create the external content type, import it into SharePoint Online, and create the external list:

  1. Generate the external content type by making a request for data and specifying the required options in the $sharepoint query string parameter. Set the AuthMode option to Credentials. Specify the target application Id and the Id of the connection settings object. For example:

    http://my-server/api.rsc/Customers?@authtoken=my-authtoken&$filter=Industry eq 'Floppy Disks'&$sharepoint=AuthMode:Credentials,TargetApplicationId:my-target-application-Id,ODataConnectionConnSettingsId:MyODataConnectionSettingsId
  2. Import the resulting .ect file: Open bcs from the quick launch bar and click the link to manage BDC models and external content types. Click Import.
  3. You can now create the external list.

SharePoint 2013

  1. Browse to the central administration area for your SharePoint site and click the link to manage service applications.
  2. Click Secure Store Service and then click New.
  3. Enter the application Id, display name, and contact email to configure the new secure store target application. Set the target application type to Group.
    Creating a new target application in SharePoint 2013. (Salesforce is shown.)
  4. On the next page, add the field names and field types to display when the user enters their username and password. Select the username and password field types.
    The type of authentication used for the cloud driver.
  5. Next, in the Target Application Administrators section, choose administrators who are authorized to access connection settings for the external content type. In the Members section, enter the users in SharePoint who are authorized to connect with the credentials of the cloud driver user.
    SharePoint users who are authorized to use the cloud driver.

After you create the new secure store target application, follow the procedure below to set the credentials that users in SharePoint will provide when accessing the cloud driver:

  1. On the secure store service applications page, click the link to the target application and click Set Credentials from the menu.
    The first step to save credentials to the cloud driver in the secure store. (Salesforce is shown.)
  2. In the resulting menu enter the username and password (authtoken).
    The credentials to the cloud driver.
  3. Open an instance of the SharePoint management shell and initialize a new SharePoint connection object. The connection object is site-specific: Set the ServiceContext parameter to the site you want. Set the ServiceAddressURL parameter to the URL of the OData endpoint. Set the AuthenticationMode to Credentials. Enter the application Id for the target application you created.

    New-SPODataConnectionSetting -AuthenticationMode Credentials -ServiceAddressURL http://my-server/api.rsc -ServiceContext http://myspsite/ -Name MyODataConnectionSettingsId -SecureStoreTargetApplicationId my-target-application-Id

    The output below shows that the command has executed successfully:

    The initialized connection object.

The final steps involve creating the external content type for the table you want to expose in SharePoint, configuring it to authenticate with credentials from the secure store, and then importing it into SharePoint.

  1. Generate the external content type by making a request for data and specifying the required options in the $sharepoint query string parameter. Set the AuthMode option to Credentials. Specify the target application Id and the Id of the connection settings object. For example Below is an example request:

    http://my-server/api.rsc/Customers?@authtoken=my-authtoken&$filter=Industry eq 'Floppy Disks'&$sharepoint=AuthMode:Credentials,TargetApplicationId:my-target-application-Id,ODataConnectionConnSettingsId:MyODataConnectionSettingsId
  2. To import the external content type into SharePoint, point your browser to the SharePoint central administration portal and click the link to manage service applications.
  3. Click Business Data Connectivity Service in the resulting page. Click Import and select the .ect file in the dialog.
  4. You can now create the external list. On the Site Contents page on your SharePoint site, click the button to add an app and then click the icon to choose the external content type.

Posted On Friday, June 12, 2015 7:03 AM | Comments (0) | Filed Under [ sharepoint cdata quickbooks online ]

Lightning Connect to SQLite External Objects

Use the CData Cloud Driver for SQLite to securely provide OData feeds of SQLite data to smart devices and cloud-based applications. Use the cloud driver with Salesforce Lightning Connect to create SQLite objects that you can access from apps and the dashboard.

The CData Cloud Driver for SQLite enables you to access SQLite data from cloud-based applications like the Salesforce console and mobile applications like the Salesforce1 Mobile App. In this article, you will use the cloud driver and Salesforce Lightning Connect to access SQLite external objects alongside standard Salesforce objects.

Set Up the Cloud Driver

If you have not already connected successfully from the cloud driver administration console, see the "Getting Started" chapter in the help documentation for a guide.

Connect to SQLite Data as an OData Data Source

Follow the steps below to connect to the cloud driver's SQLite feed.

  1. Log into Salesforce and click Setup -> Develop -> External Data Sources.
  2. Click New External Data Source.
  3. Enter values for the following properties:
    • Label: Enter a label to be used in list views and reports.
    • Name: Enter a unique identifier.
    • Type: Select the option "Lightning Connect: OData 2.0".
    • Server URL: Enter the URL to the cloud driver's OData endpoint. The format of the OData URL is https://your-server:8032/api.rsc.
  4. In the Authentication section, set the following properties:
    • Identity Type: If all members of your organization will use the same credentials to access the cloud driver, select "Named Principal". If the members of your organization will connect with their own credentials, select "Per User".
    • Authentication Protocol: Select Password Authentication to use basic authentication.
    • Certificate: Enter or browse to the certificate to be used to encrypt and authenticate communications from Salesforce to your server.
    • Username: Enter the username for a user known to the cloud driver.
    • Password: Enter the user's authtoken.
Configuration settings for the data source. (QuickBooks is shown.)

Create Objects Synchronized with SQLite Data

After you have created the external data source, follow the steps below to create SQLite external objects that reflect any changes in the data source. You will synchronize the definitions for the SQLite external objects with the definitions for SQLite tables.

  1. Click the link for the external data source you created.
  2. Click Validate and Sync.
  3. Select the SQLite tables you want to work with as external objects.
The list of tables to synchronize in Salesforce. (QuickBooks is shown.)

Access SQLite Data as Salesforce Objects

After adding SQLite data as an external data source and syncing SQLite tables with SQLite external objects, you can use the external objects just as you would standard Salesforce objects.

  • Create a new tab with a filter list view:

    A filtered list view shown on a custom tab. (QuickBooks Invoices are shown.)
  • Display related lists of SQLite external objects alongside standard Salesforce objects:

    A related list that shows an indirect lookup relationship, which links a child external object to a parent standard object. (Salesforce accounts and associated QuickBooks invoices are shown.)

Posted On Friday, June 12, 2015 6:47 AM | Comments (0) | Filed Under [ salesforce odata cdata SQLite Cloud Driver ]

Monday, February 3, 2014

Import Salesforce Data into Google Spreadsheets

The Salesforce Cloud Driver enables applications to connect to Salesforce data via Web services. In Google Spreadsheets, you can consume Web services in the CSV format by calling the ImportData function. You can follow the three steps below to use the ImportData function to consume Salesforce data in Google Spreadsheets.

  1. If you have not already connected successfully in the cloud driver administration console, see the "Getting Started" chapter in the help documentation for a guide.
  2. Authenticate your query with the authtoken of a user authorized to access the OData endpoint of the cloud driver. Append the authtoken to the URL. OData queries, such filters, order-bys, etc. can be specified in the URL. See the APIs page in the cloud driver administration console for some example queries.
  3. In a new Google sheet, use the =ImportData() formula to request the CSV file from the exposed data source. This example requests the entire Account table. Google will periodically update the results of the formula, ensuring that the sheet contains up-to-date data. You can use a formula like the one below:

    =ImportData("https://your-server/api.rsc/Account?@csv&@authtoken=your-authtoken")
Consuming Web services in Google Spreadsheets. (Salesforce is shown.)

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) | Filed Under [ salesforce odata google google docs integration cdata ]

Tutorial: Access Salesforce Leads in SharePoint through an External List

You can use the CData Cloud Driver for Salesforce to give your users the capabilities to access and update Salesforce data in SharePoint. This article shows how to create an external list of Salesforce data that is always up to date. You will use an external content type as a template for the external list. The external content type enables connectivity over Web services, the protocol that cloud driver uses to provide access to live Salesforce data.

Note that this tutorial applies to any of our data sources, not just Salesforce.

Contents

  1. Create the External Content Type
  2. Import the External Content Type
  3. Create the External List
  4. Limit and Filter Results
  5. Credentials Authentication

If you have not already connected successfully in the cloud driver administration console, see the "Getting Started" chapter in the help documentation for a guide.

Create the External Content Type

The external content type is a schema that will provide the core connectivity to Salesforce data from any SharePoint app. Call the GetSharePointSchema action to create it. Copy and paste the response into an .ect file; Account.ect, for example. In the API section of the cloud driver administration console, you can find example requests and more information on the available inputs.

Import the External Content Type

After you have created the .ect with the GetSharePointSchema action, you can follow the steps below to import it into an on-premise SharePoint installation or SharePoint Online.

SharePoint 2013

Navigate to the SharePoint central administration portal and click the link to manage service applications. In the resulting page, click Business Data Connectivity Service. Then click Import -> Choose File to select the .ect file in the dialog.

SharePoint Online

In the SharePoint admin center, click bcs from the quick launch menu and then click Manage BDC Models and External Content Types. On the resulting page, click Import.

Create the External List

You can now create SharePoint apps that can access and modify Salesforce data. Navigate to your SharePoint site and choose Site Contents -> Add an App -> External List. Then, click the Select External Content Type icon and choose the external content type that you created in the previous section. Click Create.

The first step to adding an external list in SharePoint Online: Select the external content type. (Salesforce is shown.)

Limit and Filter Results

SharePoint has limits on how much data can be retrieved from external lists of OData sources. The cloud driver sets the default limit for the number of returned rows to be 500. You can disable this limit by adding the Limit parameter and setting its value to 0. Additionally, external lists display results in pages of 30 items by default. To modify the paging size, you will need to set the Item Limit property in the settings for the default view or build a custom Web part to view the data from the external list.

In SharePoint 2013, requests to external data sources are limited by bandwidth throttling controls, which can be changed using the Set-SPBusinessDataCatalogThrottleConfig command. In SharePoint Online, requests to external data sources are limited by your Server Resource Quota and by bandwidth throttling controls; to work around this, you can apply filters in the request for data.

You can use the GetSharePointSchema Web service to apply filters when generating the schema: Add the ComparisonColumn, ComparisonOperation, and ComparisonValue parameters to the request when calling the aforementioned service. Changing the schema will redefine the data available to SharePoint users.

Credentials Authentication

The cloud driver supports a credentials-based authentication scheme using authtokens.

SharePoint Online

In this procedure, you will first create a secure store target application that will authenticate SharePoint users by providing the credentials for a cloud driver user. Next, you will create the external content type and configure it to authenticate with the credentials in the secure store. SharePoint apps patterned from the external content type can then select, insert, update, and delete data.

  1. Click secure store from the quick launch bar in the SharePoint Online administration center and then click New.
  2. In the Target Application Settings section, enter the target application Id, display name, and contact email.
  3. In the Credential Fields section, add the credentials for a user in the cloud driver. SharePoint Online will use these credentials to authenticate requests.
  4. In the Target Application Administrators section, choose an administrator who can access the connection settings for the external content type. The account for the SharePoint Online administrator is usually specified here.
  5. In the Members section, enter the users in SharePoint who are authorized to connect with the credentials of the specified user in the cloud driver.

  1. The configuration page for the new target application in SharePoint Online. (Salesforce is shown.)
  2. Store the credentials for the user in the cloud driver: Return to the list of target applications by clicking secure store from the quick launch bar. Click the target application and then click Set Credentials from the menu. Enter the username and password for a cloud driver user.
  3. Create a new connection settings object: Click bcs from the quick launch bar and then choose the option to manage connections to online services. Click Add. Name the connection and enter the URL of the OData endpoint, https://your-server/api.rsc. Select the option to use credentials stored in SharePoint. Enter the application Id for the target application you created.
  4. Call the GetSharePointSchema action to generate the external content type. In addition to the TableName parameter, add the following parameters: Add the AuthMode parameter and set the value to Credentials. Add the TargetApplicationId parameter and set the value to the Id of the secure store target application. Add the ODataConnectionSettingsId parameter and set this value to the Id of the connection settings object. Save the output from this Web service as an .ect file.

  5. Import the .ect file: Open bcs from the quick launch bar and click the link to manage BDC models and external content types. Click Import.

SharePoint 2013

In this procedure, you will first create a secure store target application that will authenticate SharePoint users by providing the credentials for a cloud driver user. Next, you will create the external content type and configure it to authenticate with the credentials in the secure store. SharePoint apps patterned from the external content type can then select, insert, update, and delete data.

  1. Browse to the central administration area for your SharePoint site and click the link to manage service applications.
  2. Click Secure Store Service and then click New.
  3. Enter the application Id, display name, and contact email to configure the new secure store target application. For SharePoint 2013, the target application type should be set to Group.
  4. On the next page, add the field names and field types to display when the user enters their username and password. To use credentials authentication, select the username and password field types.
  5. Next, in the Target Application Administrators section, choose administrators who are authorized to access connection settings for the external content type. In the Members section, enter the users in SharePoint who are authorized to connect with the credentials of the user in the cloud driver.

After you create the new secure store target application, follow the procedure below to set the credentials that users in SharePoint will provide when accessing the cloud driver:

  1. On the secure store service applications page, click the link to the target application and click Set Credentials from the menu.
  2. In the resulting menu enter the username and password.
  3. Open an instance of the SharePoint management shell and initialize a new SharePoint connection object. The connection object is site-specific: Set the ServiceContext parameter to the site you want and set the ServiceAddressURL parameter to the URL of the OData endpoint. Set the AuthenticationMode to Credentials. Enter the application Id for the target application you created.

    New-SPODataConnectionSetting -AuthenticationMode Credentials -ServiceAddressURL http://your-server/api.rsc -ServiceContext http://myspsite/ -Name MyODataConnectionId -SecureStoreTargetApplicationId your-target-application-Id

    The output below shows that the command has executed successfully:

The final steps in using credentials authentication involves creating the external content type for the table you want to expose in SharePoint, configuring it to authenticate with credentials from the secure store, and then importing it into SharePoint.

  1. Call the GetSharePointSchema Web service and then enter the table name. In addition to the TableName parameter, add the following parameters: Add the AuthMode parameter and set the value to Credentials. Add the TargetApplicationId parameter and set the value to the Id of the secure store target application. Add the ODataConnectionSettingsId parameter and set this value to the Id of the connection settings object.
  2. To import the external content type into SharePoint, point your browser to the SharePoint central administration portal and click the link to manage service applications.
  3. Click Business Data Connectivity Service in the resulting page. Click Import and select the .ect file in the dialog.
  4. You can now create the external list. On the Site Contents page on your SharePoint site, click the button to add an app and then click the icon to choose the external content type.

Back to Top

 

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) | Filed Under [ salesforce .net sharepoint cdata ]

Creating Master-Detail Screens in LightSwitch

LightSwitch provides support for using relationships between tables in a data source. However, QuickBooks does not natively expose relationships that can be used in LightSwitch. This article will walk you through the steps to set up a LightSwitch master-detail screen using simple queries.

Connect to QuickBooks

If you are connecting to QuickBooks Desktop edition, use the included Remote Connector application. You can use the Remote Connector to connect to local and remote instances of QuickBooks. The Remote Connector must be installed and running on the host QuickBooks machine before beginning this tutorial. See the help documentation for guides to setting up the Remote Connector.

If you are connecting to QuickBooks Online, you will need to obtain the OAuth authentication values. See the "Getting Started" chapter of the help documentation for a guide.

Configure the QuickBooks ADO.NET Data Source

Follow the steps below to use the Data Source Configuration Wizard to configure connection properties and import QuickBooks tables.

  1. In a new LightSwitch project, click "Attach to data source" to add a new QuickBooks data source.
  2. In the resulting wizard, select the Database data source type and in the Choose Data Source step, select CData QuickBooks Data Source.
  3. Enter the required connection properties.

    To connect to QuickBooks Desktop, enter the User, Password, and the URL to the Remote Connector.

    To connect to QuickBooks Online, enter the CompanyId and the OAuth values.

    See the help documentation for more information.

  4. Select two tables that have a master-detail relationship. This demo uses Invoices and InvoiceLineItems but any transaction and line items tables will work.

Databind QuickBooks Data to a Master-Detail Screen

Follow the steps below to use the designer to create the master-detail screen and provide QuickBooks data to it. In this example, you will make a simple screen that displays the Invoices grid on top with the line items grid, Get Invoice Line Items, on the bottom.

  1. In Solution Explorer, right-click the InvoiceLineItems table under Data Sources and click Add Query.
  2. Add a parameter of type String to serve as a placeholder for the InvoiceId field.
  3. Add a filter condition to this query. In this example, you will search for all line items with a certain invoice Id. The parameter will serve as a placeholder for this value.
  4. In Solution Explorer, right-click the Screens folder and click Add Screen. In the resulting wizard, select the Editable Grid screen as the template. In the Screen Data list, select the Invoices table.
  5. In the designer for the new screen, click the Add Data Item. In the Add Data Item dialog, select the query item and then select the query you created in step 3.
  6. In the list of data items, scroll to the bottom of the query until you see the Query Parameters node. Open the properties for the parameter you defined in step 3 and enter "Invoices.SelectedItem.Id" in the Parameter Binding box. This will bind the parameter to the Id of the currently selected invoice on the screen.
  7. Drag the query from the data item list onto the Screen Content Tree. However, you can use layout items to customize the look of the screen however you need.
Now all you need to do is run the application and the grids should be populated with the data from Invoices. Every time you select an invoice, the grid will be populated with the line items for that invoice.

Troubleshooting

If an error occurs when trying to load the data from these tables, you may see a red "X" where the data should appear. To troubleshoot the issue and see a more descriptive error message, follow these steps:

  1. Open your project properties and change the Application Type to Web.
  2. In Solution Explorer, select your project node and change from Logical View to File View.
  3. Expand the Server node and open the Web.config. Change the Mircosoft.LightSwitch.Trace.Enabled key to "true".
  4. Run your application again to the point that you receive the error. Change the URL to http://URL:port number/trace.axd
  5. Check the trace for requests that have a status code other than 200. Errors with a 500 status code can correspond to an error coming from the QuickBooks ADO.NET driver.

Posted On Monday, February 3, 2014 5:44 AM | Comments (0) | Filed Under [ ado.net .net lightswitch quickbooks ]

Powered by: