Migrating field values
Overview
While working with WEBCON BPS you may come in a situation where it’s required to change the underlying type of a field. This could be the case when you want to introduce a global field, for example a ‘Title’ field or when you stored some workflow id in an integer field but want to change it to a picker so that you can display the related workflow and provide a link to it. This post will provide two SQL templates which can help you in those cases.
Remark: Both templates will be executed against the database and will modify the date. This is not supported and if you break something you are on your own. If you still want to use those, make at least backup and test the restore before executing them.
Copy values to a global field
Migrating a value from a ‘local’ field, a field created on process level, to a global field, is a simple copy, if the data type doesn’t change. This needs to be executed for the current element and the history.
In the template below you can simply change the document type, verify the correct columns and you are good to go.
Update WFElements
set WFD_AttText1Glob = WFD_AttText1
where WFD_DTYPEID
in (
select DTYPE_ID
from WFDocTypes
where DTYPE_Guid in ('129e5a2b-5c44-42d8-99b6-6588d8ff0cf4')
)
and WFD_AttText1Glob is null
Update WFHistoryElements
set WFH_AttText1Glob = WFH_AttText1
where WFH_DTYPEID
in (
select DTYPE_ID
from WFDocTypes
where DTYPE_Guid in ('129e5a2b-5c44-42d8-99b6-6588d8ff0cf4')
)
and WFH_AttText1Glob is null
Info: Why would you want to do something like this? The values of global fields are visible in the archive. Another reason would be to have one field in each process which contains its title, which proofs useful for generic queries like generating a breadcrumb or the SQL template in the next chapter.
Migrating to a picker value
In the beginning I stored a grandparent workflow instance id in a technical integer field. This helped solve all kinds of problems but in the meantime, I realized, that it would be even better to store these values in a picker field, which uses a BPS Internal view
. Then I could show the workflow and directly provide a link to it for the user. Combined with a data row the user can see the most important information.
The problem here was that I needed to migrate values from an integer field to a picker field. This on a whole other level than just copying the value and a lot more can go wrong. Therefore, I wanted an SQL statement which I could execute and verify what would happen. Afterwards I would execute it again.
The below script will fill a table variable @newValues
with the old value of the target field and the new value. While @dryRun
is true, the @newValues
entries will be displayed. As you see, in the screenshot below, it’s really helpful to have a global title field. :)
If you switch the value to false (0), the update sections will be executed.
Remark: The provided script is a template/starting point, which needs to be modified to match your needs. This means, that you need to modify the select statement which populates the @newValues
table and a little change to the update
statement, so that the correct column will be updated. This needs to be repeated for the history.
/*** Get document types ids for this database ***/
/*** Get the integer document types of the workflow elements which should be updated and store them in a table. The GUIDs of the document types are used, so that the script can be executed against all databases.***/
declare @choiceFieldSourceDocumentType varchar(40) = (select DTYPE_ID from WFDocTypes where DTYPE_Guid = '129e5a2b-5c44-42d8-99b6-6588d8ff0cf4')
declare @documentTypesToUpdate table (ids int)
set nocount on
insert into @documentTypesToUpdate
select DTYPE_ID
from WFDocTypes /* Form type guids */
where DTYPE_Guid in ('f13323eb-b477-4f01-901d-8b3a0cbb387e','cddcd993-f55b-4536-a14e-6e42e5cb952b','4bbaf253-7379-4235-b18c-947ab617bf6f')
set nocount off
--select ids from @documentTypeToUpdate
declare @newValues table (wfdId int, docType int, title varchar(255),wfdVersion int,currentValue varchar(1000), newValue varchar(1000),otherInformation varchar(max))
declare @dryRun tinyint = 1
begin -- Update measures
/*** Define migration / update for the current version ***/
print ' Workflow instance update current element: Transfering WFD_AttInt1 to the new field WFD_AttChoose5 '
print ' Populating temp table with the new values '
delete from @newValues
insert into @newValues
select workflowsToUpdate.WFD_ID,workflowsToUpdate.WFD_DTYPEID, workflowsToUpdate.WFD_AttText1Glob,null
/** old value , new value **/
,workflowsToUpdate.WFD_AttChoose5, cast(choiceFieldSource.WFD_ID as varchar(10))+'#'+choiceFieldSource.WFD_AttText1Glob as newValue
/** other information **/
, 'Value of field used for joining '+cast(workflowsToUpdate.WFD_AttInt1 as varchar(20))
from WFElements as workflowsToUpdate join WFElements as choiceFieldSource
on
workflowsToUpdate.WFD_AttInt1 = choiceFieldSource.WFD_ID
and choiceFieldSource.WFD_DTYPEID = @choiceFieldSourceDocumentType
where workflowsToUpdate.WFD_DTYPEID in (select ids from @documentTypesToUpdate)
/*** in case we want to transfer an old value into a new field we should check wether the new field hasn't a value yet ***/
and workflowsToUpdate.WFD_AttChoose5 is null
if (@dryRun = 1) begin
print ' Show results, it''s a dryrun'
select Top 1000 * from @newValues
end else begin
print ' Updating data'
update WFElements
set WFD_AttChoose5 = newValueTable.newValue
from @newValues as newValueTable
where WFD_ID = newValueTable.wfdId
end
/*** Define migration / update for the history version ***/
print ' Workflow instance history update: Transfering WFD_AttInt1 to the new field WFD_AttChoose5 '
print ' Populating temp table with the new values '
delete from @newValues
insert into @newValues
select workflowsToUpdate.WFH_ID,workflowsToUpdate.WFH_DTYPEID, workflowsToUpdate.WFH_AttText1Glob,workflowsToUpdate.WFH_Version,
/** old value , new value **/
workflowsToUpdate.WFH_AttChoose5, cast(choiceFieldSource.WFD_ID as varchar(10))+'#'+choiceFieldSource.WFD_AttText1Glob as newValue
/** other information **/
, 'Value of field used for joining '+cast(workflowsToUpdate.WFH_AttInt1 as varchar(20))
from WFHistoryElements as workflowsToUpdate
-- There's no way to join a specific history of a sub workflow with a specific version of the parent workflow
-- we will just use the value from the current versoin
join WFElements as choiceFieldSource
on
workflowsToUpdate.WFH_AttInt1 = choiceFieldSource.WFD_ID
and choiceFieldSource.WFD_DTYPEID = @choiceFieldSourceDocumentType
where workflowsToUpdate.WFH_DTYPEID in (select ids from @documentTypesToUpdate)
/*** in case we want to transfer an old value into a new field we should check wether the new field hasn't a value yet ***/
and workflowsToUpdate.WFH_AttChoose5 is null
if (@dryRun = 1) begin
print ' Show results, it''s a dryrun'
select Top 1000 * from @newValues
end else begin
print ' Updating data'
update WFHistoryElements
set WFH_AttChoose5 = newValueTable.newValue
from @newValues as newValueTable
where WFH_ID = newValueTable.wfdId
end
end
Comments