Adding a SharePoint data connection to Winshuttle Composer v12.0 and above

Adding data connections

SharePoint data connections should be used when the source of data should be easily accessible for introducing changes without requiring SQL knowledge, when the number of records being viewed from the data source is less than 5000, and/or when you do not need data returned at the cutting-edge speed (a drop down requires 1 second to load when clicked rather than .5 seconds).

The power of a SharePoint list or library is that they can be opened in the browser by anyone with access, and modified to fit business requirements.

For example, you may need to have a list used to assign tasks based on Functional Project Managers. Because projects are frequently created and dynamic, the list of managers may change daily. If this list was created in SQL, someone with T-SQL knowledge would be required to make the change; this may introduce delays (because those resources with T-SQL or database access may be managing a queue of similar requests).

However, if you create the list in SharePoint, the list can be updated through SharePoint’s browser interface without requiring specific training. In addition, the way the data is organized in the SharePoint list can be modified easily and at any time.

Creating a SharePoint data connection

1. While on the solution tab, either click the Add Data Connection button in the ribbon or right-click the Data Connections solution tree node
and choose Add Data Connection.

2. Leave the name blank; the name is automatically generated from the list name.

3. Select the SharePoint Library or List type.

4. Enter the URL of the SharePoint site containing the list.

Composer will automatically provide selections based on previously connected forms sites. If your site is not listed, enter it manually.
In the URL http://mywinshuttle/myforms/mydatalist/default.aspx, you will only need http://mywinshuttle/myforms.

5. Select the list or library.

6. A list of columns in the target list or library will be displayed. Select only the columns you will need.

7. Add one or more fields to sort on.

Ascending means row 1 starts at the smallest value and each additional row increases.
Descending means row 1 starts at the largest value and each additional row decreases.
For date times, ascending starts at the earliest day and time, and descending starts at the most recent day and time.

8. Click OK.

Below is an example of a completed data connection to a SharePoint List (the Sort By option is not included here):

Considerations

SharePoint list data sources operate best when the number of records is fewer than 5,000. This does not mean that the total number of records in the list is less than 5,000, but that after filtering, the data set returned contains fewer than 5,000 records.

With proper indexing on the list and filters, this is easily achievable, but there may be business cases in which 5,000 must be exceeded. If this is a regular occurrence, consider switching the data source to SQL.
SharePoint lists, while fast, are not as fast as SQL queries. SharePoint lists are optimized for human viewing in the browser, while SQL queries are optimized for simply returning the data. The difference will not be noticeable unless the number of records to be returned exceeds 5,000.
Recreating lists in SharePoint is possible without specialized knowledge (export to Excel, and create a new list from that Excel spreadsheet). However, moving tables in SQL will generally require a DBA or someone with access to the database and knowledge of SQL Server Management Studio or T-SQL.
To use the SharePoint list, the user must be able to read the list. Generally, by simply having membership to the forms site, the user will have access to data source lists. However, if lists need to be shared between forms sites, you can opt to house all data source lists in a separate SharePoint site and manage access through that site.

Loading types

Data connections have two types of loading indicated by the checkbox Automatically retrieve data when the form is opened. When checked, the loading type is set to synchronous; when unchecked, the loading type is set to asynchronous.
Asynchronous (async) loading allows the form to load faster in the browser because the data connection is not invoked until a drop down or another element that relies on the data connection is interacted with by a user. However, async requires the full data set to be loaded and therefore does not work with elements that filter on the data connection.
Synchronous (sync) loading loads the data connections data set as part of the form loading into the browser, which may delay the launch of the form. However, properly indexed SQL tables and filtered SharePoint lists can reduce the loading delay to a negligible amount of time.
If you know the entire data source will be used unfiltered, uncheck the Automatically load data when the form is opened checkbox.

Sorting

SharePoint data connections can be set to use Composer sorting. If Composer sorting is not used, the data connection will fall back to use either the SharePoint default view sorting and filtering or SharePoint sorting by row ID (sorts in the order in which items are entered into the list).

When creating the data connection, you can select columns to sort on. The sort priority starts at the top and works down.

For example, if you have a sales region list and you add Region, Account Manager, and Account Value to the sorting pane in that order, then when you view the data in a drop down or query, Composer will sort in the following order:

by Region
within Region
by Account Manager
within each Account Manager
by Account Value

Note: If an unsortable column such as multi-line text is selected to sort on, Composer will fail-over to using the SharePoint list view settings for sync loading and the row ID for async loading.

Async loading

If Composer sort fields are defined, they will be used. If they are not defined, the SharePoint list will be sorted by row ID.

Sync loading

If Composer sort fields are defined, then they will be used. If they are not defined, the data connection will use the SharePoint list’s default view sorting and filtering settings.

Editing a data connection

Until an element is added to the form or workflow that uses a data connection, the data connection is not “referenced;" that is, it is not used.

Referenced data connections cannot be modified. This protects the form and workflow from unintended consequences - consequences which may break elements or corrupt data.

In order to edit an existing data connection, you must remove any references. Do this by right-clicking the data connection in the solution tree and choosing "Where Used…." This will list all elements referencing the data connection.

Once there are no further references, you are free to edit the data connection (edits such as removing columns or changing the sorting).

Sorting in solutions upgraded from previous versions

Because sorting is new in version 12, data connections in previously upgraded solutions are referenced; they cannot be edited to make use of sorting until they are de-referenced.

Impact on promoting solutions

When the solution is ready to be moved from one environment to another, any SharePoint data connections will need those lists created in the target environment prior to the first deployment. The matching list in the higher environment must have identical columns; if these columns are not identical, elements in Composer may fail (attempts to sort on Region, but the column was created as NA Region, for example).

Normally, this can be done by exporting the list from the lower environment to Excel, which is an option in the lists ribbon. Then on the next environment, go to Add an App and choose the Import for Spreadsheet option. There are known issues with this method in that the conversion from SharePoint to Excel and back again may cause date, time, and number columns to import incorrectly. If this occurs, you may need to manually format columns in Excel prior to import. In addition, calculated columns are not exported: they will need to be manually recreated after the list itself is recreated.

If you have access to the SharePoint servers or remote SharePoint PowerShell access, you can use Export-SPWeb and Import-SPWeb to move lists, which will recreate with 100% fidelity. This is the best method but requires PowerShell knowledge and special access. If you contain all SharePoint lists in a single site, these commands can be used to export and import the entire site (all data source lists, all at once).

Note: When you look at a SharePoint list column, SharePoint is displaying the friendly name of the column, but connections to this list use what is called an “internal” name for the column. This allows the column to be renamed visually, without affecting the data structure (please try, though, to avoid renaming columns).

The impact of this friendly name – internal name relationship is that you should avoid the use of Quick view to create columns. This creates a random internal name which can cause problems.

If you use a Winshuttle query script to output to a SharePoint list, you can schedule this on the next environment and the list it creates will be identical to the lower environment (barring calculated columns).