posts - 88 , comments - 3 , trackbacks - 0

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.

Print | posted on Friday, June 12, 2015 7:03 AM | Filed Under [ sharepoint cdata quickbooks online ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 

Powered by: