Extracting [Comments] field (column WFD_Description from WF_Elements) to external report

0

Hi Patryk,


this can be very easy, at least if you are running SQL Server 2016 and a willing /able to test updating the database compatibility level to 13. Using this compatibility level you can make use of OPENJSON
https://docs.microsoft.com/en-us/sql/t-sql/functions/openjson-transact-sql?view=sql-server-ver15

select *
from OPENJSON(

'[{"d":"2022-02-21T14:40:40.086895","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 1"},{"d":"2022-02-22T07:38:42.0101194","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 2"}]'
)
with (
Date DATETIME2 '$.d',
account varchar(200) '$.l',
displayname varchar(200) '$.a',
comment nvarchar(max) '$.c'
)

Unfortunately, the default compatibility level of BPS databases is 10. I haven't tested updating it and I got no reply to my question regarding the update here:
https://community.webcon.com/forum/thread/172?messageid=172


If this isn't an option, you could create a Custom SDK business rule or make use of an HTML field to display process the returned JSON.

Best regards,
Daniel

Hi Daniel,
thanks for your very detailed answer. I rather will not update the compatibility level on my own :), as working on enterprise Webcon environment, then using JSON would not be possible in my case too.
The thing is that I need to integrate the comment data with comments from the obsolete system database (union them) and display in the Data Table field. Will look further for the solution.
Cheers,
Patryk

Hi Patryk,

ok, if you need to display the data in a Data Table we have to rely on the build in features.

This is not an ideal solution but in our cases it will work, but I don't have any idea how this performance in a real world scenario. :)

declare @jsonText nvarchar(max) = '[{"d":"2022-02-21T14:40:40.086895","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 1"},{"d":"2022-02-22T07:38:42.0101194","l":"username@domain.com","a":"Name Surname","c":"Text of the comment number 2"}]'

select dbo.ClearWFElemId(item) as [Date],
dbo.ClearWFElemId(dbo.ClearWFElem(item)) as Account,
dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(item))) as DisplayName,
dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(dbo.ClearWFElem(item)))) as Comment
from dbo.SplitToTable(Replace(Replace(Replace(Replace(Replace(Replace(@jsonText,'","c":"','#'),'","a":"','#'),'","l":"','#'),'"d":"',''),'[{',''),'"}]',''),'"},{')

Best regards,
Daniel

Hi Daniel,
it works fine in SQL Developer, but... I would like to have it in Webcon as data source for field type Data Table, it is not possible though, tried in two ways as on the photo attached.

Hi Patryk,

I just tested this as a SQL query of a database source and it worked. The problem are the {} which are used to identify variables so they needed to be replaced with CHAR.

But I'm not sure that the performance will be any good. I don't remember if the filters on a data source are applied before or after the query is executed.

Best regards,
Daniel

-- select
-- WFD_ID,
-- WFD_Description,
-- dbo.ClearWFElemId(item) as [Date],
-- dbo.ClearWFElemId(dbo.ClearWFElem(item)) as Account,
-- dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(item))) as DisplayName,
-- dbo.ClearWFElemId(dbo.ClearWFElem(dbo.ClearWFElem(dbo.ClearWFElem(item)))) as Comment
-- from WFElements
-- cross apply
-- dbo.SplitToTable(
-- Replace(
-- Replace(
-- Replace(
-- Replace(
-- Replace(
-- Replace(convert(nvarchar(max),WFD_Description),'","c":"','#')
-- ,'","a":"','#')
-- ,'","l":"','#')
-- ,'"d":"','')
-- ,'[{','')
-- ,'"'+Char(125)+']','')
-- ,'"'+Char(125)+','+Char(123))
-- -- Curly right bracket= Char 125,Curly left bracket = Char 125
-- where WFD_Description is not null

Thank you so much Daniel,

Works as a charm! Had to union these data with external data source from legacy application.
BTW: Webcon should implement some kind of way to extract these data...

Good Luck!