This week I have been supporting business users on a User Acceptance Test (UAT) of one of the most complex processes I have ever worked on. The process is being developed by a team of 5 developers and has 47 tasks, 26 decision gateways with 85 routing conditions, 76 dynaforms, 675 variables, 50 triggers and complemented by a number of custom plugins.

Making changes to such a complex process requires careful thought, as the wrong move might introduce new bugs into the process while trying to resolve another. A common change often required during the UAT is to change the name, property or data type of a variable. Before making such a change, it can be very helpful to check that the variable is not being used in other dynaforms, triggers or conditions in decision gateways or steps, as this can cause unexpected behaviour in the process.

The following query can help locate all instances where the variable name is being used. It searches for the variable name in the Dynaforms, Triggers, Routing Conditions, Step Conditions and Trigger Conditions in the process.

Note: Replace the {PROCESS_UID} with the UID of the process and {NAME_OF_VARIABLE} with the name of the variable. This should only be done in development environments as the queries can have performance implications in a production environment.

SELECT CONCAT('DYNAFORM - Used in ', `DYN_TITLE`, ' dynaform') as "Location"
FROM `DYNAFORM` 
WHERE `PRO_UID` = '{PROCESS_UID}' AND `DYN_CONTENT` LIKE '%{NAME_OF_VARIABLE}%'
UNION
SELECT CONCAT('TRIGGER - Used in ', `TRI_TITLE`, ' trigger') as "Location" 
FROM `TRIGGERS` WHERE `PRO_UID` = '{PROCESS_UID}' AND `TRI_WEBBOT` like '%{NAME_OF_VARIABLE}%'
UNION
SELECT CONCAT('ROUTING CONDITION - Used in decision gateway after ', `TASK`.`TAS_TITLE`, ' task') as "Location"  
FROM `ROUTE`, `TASK` 
WHERE `ROUTE`.`PRO_UID` = '{PROCESS_UID}' AND `ROUTE`.`ROU_CONDITION` like '%{NAME_OF_VARIABLE}%'
AND `ROUTE`.`TAS_UID` = `TASK`.`TAS_UID`
UNION
SELECT CONCAT('STEP - Used in position ', `STEP_TRIGGER`.`ST_POSITION`, ' (', `STEP_TRIGGER`.`ST_TYPE`, ') in ', `TRIGGERS`.`TRI_TITLE`, ' trigger of ', `TASK`.`TAS_TITLE`, ' Task') as "Location"
FROM `STEP_TRIGGER`, `TASK`, `TRIGGERS` 
WHERE `STEP_TRIGGER`.`TAS_UID` IN (SELECT `TAS_UID` FROM `TASK` WHERE `PRO_UID` = '{PROCESS_UID}')
AND `ST_CONDITION` like '%{NAME_OF_VARIABLE}%'
AND `STEP_TRIGGER`.`TAS_UID` = `TASK`.`TAS_UID`
AND `STEP_TRIGGER`.`TRI_UID` = `TRIGGERS`.`TRI_UID`
UNION
SELECT CONCAT('STEP TRIGGER - Used in position ', `STEP_TRIGGER`.`ST_POSITION`, ' (', `STEP_TRIGGER`.`ST_TYPE`, ') in ', `TRIGGERS`.`TRI_TITLE`, ' trigger of ', `TASK`.`TAS_TITLE`, ' Task') as "Location"
FROM `STEP_TRIGGER`, `TASK`, `TRIGGERS` 
WHERE `STEP_TRIGGER`.`TAS_UID` IN (SELECT `TAS_UID` FROM `TASK` WHERE `PRO_UID` = '{PROCESS_UID}')
AND `ST_CONDITION` like '%{NAME_OF_VARIABLE}%'
AND `STEP_TRIGGER`.`TAS_UID` = `TASK`.`TAS_UID`
AND `STEP_TRIGGER`.`TRI_UID` = `TRIGGERS`.`TRI_UID`

Here’s a sample of what the output would look like.

Sample output of query result

As you can see from the screenshot above, you can easily see all the instances where the variable is being used in the process. You can modify it to meet your needs and I hope you find this helpful.

Photo by Eugene Chystiakov on Unsplash