Home > Forum > Database > Extracting [Comments] field (column WFD_Description from WF_Elements) to external report

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

Hi Guys,
Has Webcon any function or script to extract the [Comments] field in the "human eadible" way? Lets say DATE | AUTHOR | COMMENT TEXT. Selecting from WFD_Description for particular ID gives me output like below. It is becoming more complicated if I have two or more comments, divided by ','.

SELECT
WFD_ID
,WFD_Description
FROM [BPS_Content].[dbo].[WFElements]
where wfd_id=xxxxx

output:
[{"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"}]

MVP

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

In reply to: Daniel Krüger (Cosmo Consult)

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

MVP
In reply to: Patryk Kornak

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

MVP
In reply to: Patryk Kornak

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