One issue we consistently encounter when creating a DB mapping is the translation of the ‘text’data type. It has become clear that although a full ‘text’ field is desired in Workflow, it is mapped to the database to be an nvarchar(255); namely, of finite length. This becomes an issue when requiring fields in our data type which may contain ‘description’ or ‘comments’ or ‘details’ that could easily extend beyond this character limit.
One solution we found is to manually edit the data type of the columns in the database to be nvarchar(MAX) after the database is created. However in some circumstances, usually when accessing a profile of the data type or encountering a mapping error, Workflow will automatically create a new column with type nvarchar(255) (which subsequent ORM data instances will use), and alter the old column heading to be “
It is also important to realize the context in which the ORM data type is used best: In ServiceDesk, where a single data instance (such as an incident) is related to a single task. Typically, you can simply add a new data element of the ORM type, map data to it, then follow up with a Save External Data component and Workflow will automatically write and sync changes to the database.
If you attempt to maintain and update multiple instances in a collection of an ORM data type, Workflow cannot guarantee the persistence of those entries, because Workflow can reference only a single instance to sync and update with. Consider this simple example with two instances of an ORM data type, abstractly labeled “A” and “B”. If changes are made to instance A, Workflow cannot ascertain if the intent of the user is to update the data in instance A or overwrite the data in instance B. This usually results in unstable behavior, which causes a breakdown of data integrity.
As previously mentioned, the ORM data type works best when a single instance can be tied to a single task, such as in a Dialog Workflow component. While inside of the event configuration of a dialog, all changes to the instance are saved automatically (without the need of database writes or the Save External Data component). Be aware that bad external references to the database, which can occur if some of the above practices are conducted, are cleaned up at the conclusion of the dialog component.
Finally, it is important to ascertain the purpose and function of using an ORM data type. As previously mentioned, using an ORM data type can easily tie data from your Workflow processes directly into Process Manager to be used in reports and task data, which is accomplished through a process profile. Once the profile is created, it is easy to create custom process view pages that can display this data to be readily accessible by the user. However, if the user will sparingly be using the process view page or the Process Manager web portal, it is not always practical to set up your process with an the ORM data type.
In summary, the ORM data type is a powerful tool in Workflow that can easily expose data to be accessible in Process Manager. However, be aware that trying to implement large text fields in the data type causes the database structure to become unstable. Additionally, using a single instance of your data type in a process is ideal; a collection of instances can result in errant process behavior and a breakdown of data integrity. This is why it is important to understand the context of your Workflow process and use the ORM data type if your users will be accessing process view pages. In conclusion, following these advisement's will help to ensure your Workflow processes use the ORM data type properly and to its maximum benefit.
If you have any questions please feel free to comment below, fill out our info request form, or reach out to me directly.
Thanks for reading,
Justin Searles
justins@wevogroup.com
