Including comments in pdf generation

0

Hi Mark,

I'm not aware of this kind of option, but you could create a data table field which lists the comments.

Here are different examples on how to retrieve the comments. Your best bet is the first one at least if you are running SQL Server 2016 and can change to database compatibility to it.
https://community.webcon.com/forum/thread/1478?messageid=1478

Best regards,
Daniel

Hi,

thanks for your answer.
What are the possible consequences of changing the compatibility level?

If I don't want to go upgrading the database, I need to create a new SQL data source in designer studio and write a complex query that i then use as a data source for a data table?

How can I then add a data table onto a html template? The create pdf action doesn't seem to interpret the{SEL_SQLGrid} tag.

Thanks!

Hi Mark,

I'm only aware of this topic, but it's related to upgrading the database compatibility level to 2019.
https://community.webcon.com/posts/post/webcon-bps-databases-in-the-sql-2019-compatibility-level/320

So it should be fine to update to 2016, at least I hope so, as I've just done it.

Regarding the HTML template,
You could search in the online help of designer studio for "Variables" select the topic "Variables" and scroll to the bottom to got to:
HTML and PDF print tags

There's an explanation.

If you can wait, I will create a small blog post about this in the next days.

Best regards,
Daniel

Hi Mark,

you can find an example for:
- Getting the comments with OPENJSON, available as of SQL Server 2016 and without
- A sample for using a data table in an HTML template to generate a pdf

https://daniels-notes.de/posts/2023/comments-to-data-table-and-pdf

Best regards,
Daniel

Hi Daniel,

Thanks for the blog post. Was trying to implement it when I realized it's probably written for modern forms, where comments are stored as JSON, not for classic forms. Do you have anything similar for classic forms?

Thanks

Hi Marek,

I don’t have an example for the classic form. Could you post a sample value? I assume it’s still stored in the WFD_Description column?

Best regards,
Daniel

Hi,

yes, it's in the WFD_Description column. The structure of comments for an element is:

[Date and Time] User Display Name|#Comment |;Next comment with same structure|;

For example:

[03/16/2023 07:05:23] Katarina Pisek|#The new product looks great! |;[03/16/2023 07:45:16] Andrej Susek|#Thanks! |;[03/17/2023 08:23:16] Katarina Pisek|#You're welcome.|;

Thanks!

Hi Mark,

using your example, the below could be an option

declare @comment varchar(max) = '[03/16/2023 07:05:23] Katarina Pisek|#The new product looks great! |;[03/16/2023 07:45:16] Andrej Susek|#Thanks! |;[03/17/2023 08:23:16] Katarina Pisek|#You''re welcome.|;'

For some reason I can't copy the SQL statement, it causes a 400 on the server

Best regards,
Daniel

Hi Daniel,

Thanks for your effort! I needed to make some modifications since I need the query to work dynamically in the workflow:

*in attachment, I also couldn't paste here.

It works in SQL management studio, however Webcon designer studio throws an error:

"Incorrect syntax near the keyword 'declare'. Incorrect syntax near ')'."

Any idea?
Thanks again!

Best regards,
Mark

Hi Mark,

executing a SQL statement with declare doesn't work in the Designer studio, but it does work anyway.

But you don't need it in this case, you can return the WFD_Description from a query inside the dbo.SplitToTable()
At least if you want to do it for a single workflow.

select *
from dbo.SplitToTable(
( select Top 1 WFD_Description from WFElements where WFD_Description is not null),',')

If you have multiple workflows you could use

select Top 100 WFD_ID, Comments.*
from WFElements cross apply dbo.SplitToTable(WFD_Description,',') as Comments
where WFD_Description is not null

Best regards,
Daniel

Hi,

I finally managed to make it work.
I actually found out that the process I used for testing was not using the JSON format for comments, but the actual production process was using the JSON format, so I actually ended up using the sql for JSON in production. Apparently, the format of comments isn't based on classic/modern forms, but the Webcon version the process was made in.

Even so, when I wanted to make it dynamic, so the process would read comments from the current instance, I couldn't simply add a WHERE WFD_ID = Instance ID, as this generated an error, probably because of all the replace statements. I defined a variable at the beginning of the query which I used for saving the current instance ID and then used the variable in the WHERE clause. This is annoying even though it works in production, since Webcon Designer Studio throws errors because of the variables. You need to save the configuration without variables and then add the variables and save it again. It's really clumsy.

Webcon should really improve on this. Using variables should work in Designer Studio. And adding comments to output should be easier, I don't think this is such a niche requirement.

Thanks for your help Daniel, really appreciate it!