Update from 2022 to 2023 - check your SQL Command based business Rules using ID fields

0

Hi all,

first of all: this is not supposed to be a moaning complaint, just a heads up so other people dont fall for the issue that hit us today.

I wanted to share some info regarding the update from 2022 to 2023, as we ran into some very unsatisfying issues with SQL COMMAND business rules that are querying field IDs. It hit us on some neuralgic spots in our main workflow, causing quite some pain right now.

If you still have to update to 2023 make sure you test every SQL COMMAND based rule (and datasource) that is using the Field-ID object, the behaviour of the result of clearwfelemID has changed in 2023.

In 2022 result of the DB function clearwfelemID (and also the Field-ID-Object in designer) was returning an empty string for fields having no value, so when evaluating a result from a query using this, and the function gave back an empty string in 2022 the behaviour changed to giving back NULL value in 2023. At least that is my impression.
This might be connected to the seperate ID columns and a change in the underlying DB function. I think that it even behaves differently when trying to get ID from wfelementdetails and wfelements tables. (specific ID field in wfelements, no specific ID field in wfelementdetails - you can see calculated columns in table wfelements, none in wfelementdetails, because there are no specific colums for choose fields in item lists).

Example:

I am using the following statement to find out if a check in our invoice approvals is necessary, depending on a template workflow that defines the running order, using an item list to define parameters to jump into steps or not.

Query is:
if exists (select 1 from wfelements WF_E , wfelementdetails WF_D where WF_E.WFD_ID = WF_D.DET_WFDID and DET_WFCONID = {WFCON:1614} and {DCNCOL_ID:121} = {ST:153} and WF_E.WFD_COMID = {COM_ID} and ({WFCONCOL_ID:1559} = '{I:1559}' or {WFCONCOL_ID:1559} = '') and '{I:68}' = {SYSCOL:WFD_ID}) select 1 else select 0

This part: "or {WFCONCOL_ID:1559} = ''" being the problem, as cases in which {WFCONCOL_ID:1559} would gave back an empty string in 2022, now, in 2023 give back NULL, excluding the cases that I was aiming for with that ["or {WFCONCOL_ID:1559} = ''"] part of the whereclause.

So whereever you have such scenario you need to change {WFCONCOL_ID:1559} = ''" to "{WFCONCOL_ID:1559} is null" to get proper results. I am not sure if this just didn't occur to me when I was reading the changelogs, but of course I fell for it in several occurences in the system. We went through all data sources when testing, fixed issues that were there, and failed to check the rules.

If anyone has questions and concerns, or didn't understand the issue from my textchaos: i will try to explain with examples and screenshots :)


Cheers, Chris