04.03.2021
20:22
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
-----------------------------------------------------------------------------------------------------------------------------------------
