Help Center>Foundation Help

Applies to:

Working with external storage

On this page

The following information applies to workflow administrators and system architects.

Some organizations use workflow to process very large data tables. Eventually this data is headed for SAP, and SAP is designed to handle large volumes of data indefinitely. However, storing all the same data in SharePoint is not a sustainable model, but there is a way to move that data out of the SharePoint content database.

The basic idea is to “clip” branches from the FormXML that contain large tables, and then store those branches somewhere else. The main body of the FormXML will remain and continues to be stored in the SharePoint list item, but the larger branches will be stored somewhere else. When the FormXML is needed, the code retrieves the branches from external storage and re-attaches them back into the main FormXML in its original location.

In addition to external storage, the new feature includes the support for concurrent editing. This enables different people to edit different columns in a table at the same time, and only the cells that the user actually changed are merged into the final table.

How to configure external storage

Back to top

In the root node of a table or repeating section, you need to maintain a couple of attributes:

  • key='<SomeColumnName>'
  • ext ='0'

If the 'key' attribute is set then the code performs a merge each time the table data gets updated. Only those fields that were actually changed by the user will be updated. The value of the key attribute should be the name of one of the table columns. The requirement for merging is that each row has a unique key and the value of the attribute indicates which column contains the key (for example, my:partnumber).

If the 'ext' attribute is set then the code stores the table in an external location. Currently there are four different implementations of "stores". When ext='0' is found, the code will get a "PayloadStoreType" config value that indicates which one of the four stores to use (see Types of database stores, below).

After the first time the table is stored, the type of store (1,2,3, or 4) is maintained in the 'ext' attribute, and it will be used whenever the solution designer chooses to store data externally.

Configuring Repeating Tables or Groups to use external storage

Back to top

In Composer, there are 2 new Advanced Properties for Repeating Groups and Repeating Tables to enable the use of external storage:

  • Key Column for Merging: This dropdown will contain a list of all fields contained in the Repeating Table/Group. Setting this 'key' will cause the form to perform a merge each time the table data gets updated, but only fields that are actually changed by the user are updated.

    The value of the key should be the name of one of the table columns . Each row must have a unique key value. The key value indicates which column contains the key (for example, my:partnumber).

  • Payload Store Type: Where to store external data. If you are working in an environment where an external data store has been configured to offload part of your form data, the values in this dropdown let you specify which 'store' to use. (This would need to be configured by your Winshuttle Workflow Administrator.)

Types of database stores

Back to top

The four types of database stores and how to configure them are described below.

Set the value of the global config value ‘PayloadStoreType’ to indicate which type of store you want to use.

You can also append _<ListName> to create a list specific setting; for example ‘PayloadStoreType_MyTestForm’

1 - Attachment Store

Back to top

This is the default store if PayloadStoreType is not specified. The external tables are simply stored as attachments on the list item.

2 - File Store

Back to top

The external tables are stored on a network file system. Use the global config value ‘FileSystemPayloadStoreFolder’ to provide a UNC path to the file system. You also must provide credentials for a user that has read/write access to this folder using the 'FileSystemPayloadDomain', 'FileSystemPayloadUsername', and ‘FileSystemPayloadPassword' config values. You can also append _<ListName> to any of these config keys create a list specific setting (ie ‘FileSystemPayloadStoreFolder_MyTestForm’).

3 - Database Blob Store

Back to top

The external tables are as blobs in an SQL database. Use the following script to create the necessary table and indices:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Payloads](

[PayLoadID] [uniqueidentifier] NOT NULL,

[WebGuid] [uniqueidentifier] NOT NULL,

[ListGuid] [uniqueidentifier] NOT NULL,

[ItemID] [int] NOT NULL,

[Binding] [nvarchar](50) NOT NULL,

[Payload] [nvarchar](max) NOT NULL,

[LastUpdate] [datetime] NOT NULL,

CONSTRAINT [PK_Payloads] PRIMARY KEY CLUSTERED

(

[PayLoadID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Payloads] ADD CONSTRAINT [DF_Payloads_PayLoadID] DEFAULT (newid()) FOR [PayLoadID]

GO

ALTER TABLE [dbo].[Payloads] ADD CONSTRAINT [DF_Payloads_LastUpdate] DEFAULT (getdate()) FOR [LastUpdate]

GO

CREATE NONCLUSTERED INDEX [IX_Payloads] ON [dbo].[Payloads]

(

[WebGuid] ASC,

[ListGuid] ASC,

[ItemID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

The table can be added to the Workflow Database or to some other database. In the latter case you can specify a connection string with the global config value ‘SqlConnectionPayloadStore’. You can also append _<ListName> to create a list specific setting.

4 - Database Table Store

Back to top

The external tables are disassembled and stored as individual records in an SQL database. Use the following script to create the necessary table and indices:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[PayloadTables](

[Id] [uniqueidentifier] NOT NULL,

[ParentId] [uniqueidentifier] NOT NULL,

[Ordinal] [int] NOT NULL,

[Binding] [nvarchar](50) NULL,

[Value] [nvarchar](100) NULL,

[PayloadLockID] [int] NULL,

CONSTRAINT [PK_PayloadTables] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_PayloadTables] ON [dbo].[PayloadTables]

(

[ParentId] ASC,

[Ordinal] ASC,

[Binding] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

The table can be added to the Workflow Database or to some other database. In the latter case you can specify a connection string with the global config value ‘SqlConnectionFormXmlDB’. You can also append _<ListName> to create a list specific setting.