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

0

Hi Christian,

you could check out this post

https://daniels-notes.de/posts/2023/updating-to-bps-2023

Alternatively you could also look at:
https://community.webcon.com/forum/thread/3063
https://community.webcon.com/forum/thread/3065
;)

Off topic
Maybe you can upvote this user voice
https://community.webcon.com/forum/thread/5519/15

I still hope that it may get implemented. :)



Best regards,
Daniel

Hi Daniel,

no talk for a while ;)

Thanks for your input as always! Your topics are not exactly about what i have experienced in the last days, though.

Although I had the same issues numerous times, and I feel what is said in the replies to your posts also. Stuff works in Test, then you sync to prod and some details you didn't think of produce a pile of trash or errors only in certain constellations. And you have to solve this trash by moving workflows around after you fixed the underlying problems. This time it wasn't syncing to Prod - it was the update itself that changed query results due to a change in how clearwfelemID function returns it's result.

In our case i am including an ID in the where clause which refers to a choose field value in wfelements table in a different workflow.
The query expects to get back an empty string from the ID object of a choose field, if the field is empty. This was the result until Version 2022.
With the switch to 2023 the behaviour of this ID object changed, and for empty choose fields it gives back NULL now, which differs from empty string, thus negating my entire where clause. Which results in the exact opposite behaviour i received from the identical statement that worked before, because the whereclause excludes now, what has been included in the previous version. This is a small detail with possibly big impact.
It would have been better if the calculated ID fields would have their entire result covered by ISNULL(result,'') for compatibility and same behaviour as in 2022. I would not have the problems I have now if it would have been done that way. And if I was aware this would happen I would have covered these parts of my queries myself, I just wasn't. I will cover this with ISNULL(result,'') now myself in every query this occurs in.

As I said, this only refers to choose fields from wfelements. The clearwfelemID from wfelementsdetails/item lists behaves as before and returns empty strings when the field has no value. At least I think so. Because it doesn't query the calculated ID field, because there are none in wfelementdetails table.

I am using the queries to distinguish which path the workflow should move along during our invoices approvals. Until we figured out there is a problem the mess was already there, making backup useless, and no other way to fix the result but manually, as until it hits the first decision workflows are already there for 1-2 days. Workflows went the exact opposite path as intended, missing necessary approvals, and requiring manual fixing now in numerous instances, including sending them back to previous involved people. With the number of documents we shoot through the system i will have to correct a few hundred mislead workflows now from the past few days, which is nothing I really enjoy - i still do it as I prefer my colleagues to be happy.

I agree on all the things in your topics, I ran into failing queries when i not put IDs in '' also, but this here is different, as it doesnt even throw an error, it just does the exact opposite than in the previous version - and the first people seeing the results are users after a few days.

Again, this is not supposed to be moaning, I just intended to warn people to keep them from falling for this like we did.
I just repeatedly got the impression that the only thing that is for sure with updates is that you have to pray for stuff like that not happening, and you really really really need to test every single case in every single constellation. I must admit that I am quite traumtized from previous update experiences, and try to avoid them whenever possible. This time we had to, and it was by far the worst of all.

Another thing that hit my desk just now: I just got notified that actions deleting attachments caused trouble as well, one of our processes generates an approval report. In order to do so it creates a word file as temporary document to generate the pdf from, that has "TEMP" as category when being created.
When the PDF is done next action is to delete all documents from TEMP category, which should only be the docx temp file. The delete action identifies which attachments to delete by category "TEMP" in the action. Instead of deleting the docx it deletes the original invoice - i honestly dont know why, but need to do a workaround now, under pressure.
Again, this has worked for years, and all of a sudden it doesn't. I think I remember there was a forum topic by Janni that described that behaviour earlier. But even if you happen to read this you still need to be aware that you yourself are using this scenario, which gets harder with the increase of complexity in your environment.

BR, back to fixing :(
Chris