Home > Forum > Tips&Tricks > SQL-Query for short Workflow History Data-Source

SQL-Query for short Workflow History Data-Source
3

Hi all,

i thought it might be a good idea to share useful queries that should be reusable for any of us as they are.

Although I personally find the built in Workflow-History very useful and really like it for admin-purposes, it might be some kind of an detail-overkill for common users.

In order to give users a more simple look of what happened so far in the workflow, I created a datasource that should work for all workflows and translations. Values are shown depending on the users browser language.

It simply shows which step was left with which path, by which user, at what time.

I present this in an SQL-grid in the forms where I think it will be helpful to the users. It can be very helpful even in the preview form in reports.

Attached 2 screenshots with german and english browser. Please excuse the bad formatting - I hope you enjoy anyways!

-------------------------------------------------------------------------------------------------------------
SELECT WFH_STPID,
WFH_OrgID,
WFH_COMID,
dbo.clearwfelem(WFH_LeftBy) as finished_by,
isnull((select trans_name from Translates where dbo.ClearWFElemID(WFH_LeaveCurrentStepPath) = TRANS_ELEMID and TRANS_OBJID = 9 and trans_lanid = (select TOP 1 LAN_ID from TranslateLanguages where substring(LAN_Name,0,2) = substring('{USERLAN}',0,2) )),'') as path,
CASE WHEN WFH_LeaveCurrentStepDate is null then '' Else convert(varchar(20),WFH_LeaveCurrentStepDate,113) END as Left_Step_at, TRans_name, STP_TypeId, WFH_LeaveCurrentStepDate, 1 as SortCategory
FROM WFHistoryElements left outer join
WFSteps on WFH_STPID = STP_ID
left outer join
Translates on STP_ID = TRANS_ELEMID and TRANS_OBJID = 5 and trans_lanid = (select TOP 1 LAN_ID from TranslateLanguages where substring(LAN_Name,0,2) = substring('{USERLAN}',0,2))
where WFH_OrgID = '{WFD_ID}' and STP_typeid <> 7 and WFH_IsLastVersionInCurrentStep = 1 union
SELECT WFD_STPID,
WFD_ID,
WFD_COMID,
'' as finished_by,
'' as path,
'Current task' as Left_Step_at, TRans_name, STP_TypeId, NULL as WFH_LeaveCurrentStepDate, 2 as SortCategory
FROM WFElements left outer join
WFSteps on WFD_STPID = STP_ID
left outer join
translates on STP_ID = TRANS_ELEMID and TRANS_OBJID = 5 and trans_lanid = (select TOP 1 LAN_ID from TranslateLanguages where substring(LAN_Name,0,2) = substring('{USERLAN}',0,2))
where WFD_ID = '{WFD_ID}' and STP_typeid <> 7 order by SortCategory, WFH_LeaveCurrentStepDate

-----------------------------------------------------------------------------------------------------------------------------------------

MVP

Hi Christian,

thanks for sharing. It may come in handy.

I'm intending to do the same, once I've gotten the Ok to share a few things I did during my working time.
I have suggestion, if there is no translation you could fall back to the original name, instead of ''.
I've done it here, search for userlan if you don't want to read all :)
https://daniels-notes.de/posts/2021/series-expert-guide-part-5#retrieving-translations-of-webcon-bps-elements
You would have to replace the table and column namens but you can retrieve these using the query which is found if you search for 'Translation of Column' on the page.

And I should use the substring function you are using instead the whole userlan value. :)

Best regards,
Daniel