Applies to version 2017.1.x; Author: Tomasz Pytlak
SharePoint lists are a convenient method of data storage to which choice fields can use as data sources and refer to its values. However after exceeding a certain amount of data, using them as a form fields’ data source significantly slows down the system. In this case, creating a Dictionary workflow is a good alternative.
Dictionary workflow is a typical workflow that stores data in its form fields, there are essentially two ways to represent an individual ‘record’ or row of data, which gives us two general types of dictionary workflows:
- Workflow in which a single instance represents the entire dictionary, but individual records are created via rows on an items list.
- Workflow in which every single instance represents one record or ‘row’ of data.
To present the possibilities of Dictionary workflow three workflows were created. “Products” and “Product type” are made for data sharing while “Requisitions” is only for referencing them.
Specific forms and connections between them are presented below.
“Product type” workflow:
Here is the first type of dictionary workflow. This workflow has only one instance that represents the entire dictionary, Item list rows act as its records. Of course, in a real scenario, the list would be much more extensive.
This dictionary workflow is then connected back through WEBCON BPS Designer Studio as an MSSQL source (since WEBCON BPS databases are SQL).
The query obtains data from the dictionary workflow instance:
Even though the idea of using the workflow as a data source remains the same, it can be done in a different way.
The workflow below consists of 3 steps of the following scheme: Draft -> Active -> Archive. Every workflow instance on the second step (“Active products”) counts as a single record that can be chosen by an end user in the “Requisition” workflow. It is easy to imagine that a power user can add ‘records’ to this dictionary workflow by creating new workflow instances and moving them to the Active step, or they can retire unwanted records by moving them to the Archive step.
To add records to the data source, the power user chooses the product type from the values provided in the items list found in the “Product types” workflow (the first dictionary workflow). The remaining fields are then filled manually. After moving through “Add product” path, the workflow instance – which represents an item of equipment – becomes available for other users in the “Requisition” workflow. To “delete” a record, go through the Archive path button. To save it, move through the Update path button.
Additionally, to make this scenario more realistic, the “Add product” path has some validation set in place. It will not allow to add a product of a value of over 10 000 by a person who is not a part of the BPS_TP group.
Setting up workflow and its instances as a source of data can be done in an analogous way as for Product Types – by referring to WFElements table and narrowing down to workflow type and step (in our case we want workflow instances of the ‘Products’ workflow as long as they are in the ‘Active’ step). However, an alternative is possible – we will use the data source: “BPS internal view”. This method allows choosing data from existing workflows in the same manner as during SWE Web Part configuration on the website. You can, of course, refer to data from a workflow instance via SQL query, as seen above or in THIS article, but configuring a BPS internal view source allows you to use workflow instances inside your content database as a data source without wrtining any SQL queries.
Requisition workflow uses data provided by two dictionary workflows.
“Product types” workflow allows the user to choose “Product type” and then system maps corresponding field “Maximum requisition size”.
Wares from “Products” workflow are chosen on the items list by the user. Additionally, they are narrowed down so that “Type” = “Product type”. Beside that “Unit value” is mapped as well and validation checks if “Real requisition size” < “Maximum requisition size”.
This sample website is enhanced with Web Parts which allow to start new workflows and manage the existing ones. Unfortunately, SWE report cannot present individual rows of an item list. Because of that, for the “Product types” workflow only the ID with workflow link is available.
Below you can see what our “Product types” and “Products” dictionaries would like in the form of SharePoint lists. It’s worth noting that they do not provide the option to use advanced validations and the language used to narrow them down is CAML.
Pros and cons of dictionary workflows:
Dictionary workflows show their true potential when used with big data clusters. Their speed exceeds the speed possible to achieve with SharePoint lists as the information is downloaded straight to the WEBCON BPS database.
The administrator doesn’t have to worry about data loss when changing the dictionary’s version because he has access to workflow history at all times.
This method is very elastic. In fact, every workflow can be used as a source of data if there’s a need to refer to its form field data. Also, it is very easy to narrow the dictionary down to a specific form or step types or to a single value of a form field.
Dictionary workflows can use different validation methods which prevent from entering incorrect data. However, data used in other workflows doesn’t necessarily needs to be entered directly by the user, but for example, be a result of certain procedures done according to business logic.
The biggest drawback of both types of dictionary workflows is their edition speed. In type 1 – based on the items list – a problem with form performance occurs when the number of lines exceeds 100 and with more than 5 editable data columns. In type 2, the user has to find each instance independently, open it, change the value and move along the path. While modifying big data clusters, the lack of an option to edit all of them on a single page wastes a lot of time.
Weak points of dictionary workflows are SharePoints’ list advantages. Lists are very easy to edit. Values can be copied directly from the Excel file or imported. They can be sorted or grouped without the need for further configuration. They are also paged which helps with the edition performance.
On the other hand they are processed much slower by WEBCON BPS, and in order to merge them with SQL tables, it’s required to synchronize them to SQL.