Please vote for this user voice, as it already have more votes, and is exactly the same ;)
https://community.webcon.com/forum/thread/4134?messageid=4134
Hello,
when working with custom columns in reports I'm always trying to avoid using regular ID when comparing forms, workflows, processes etc. I'm almost always joining with specific tables to get GUID, so reports won't break between environments.
Inside custom columns we can access wfelems, which as far as i know is based on V_WFElements - if we could get DTYPE_GUID, DEF_GUID, WF_GUID there it would make that process easier - instead of subquery per column, we could get those just from what's already there :)
Example query inside custom column:
IIF((SELECT DTYPE_GUID FROM WFDocTypes dt WHERE dt.DTYPE_ID = wfelems.DTYPE_ID) = '51a1dfbc-de3b-4a59-92c5-3342435b0088' /*document guid*/, 'Tak', (
SELECT
CASE WHEN (ISNULL(WFD_AttBool7, 0) = 0 AND ISNULL(WFD_AttBool1, 0) = 0 AND ISNULL(WFD_AttBool2, 0) = 0 AND ISNULL(WFD_AttChoose45_ID, '0') = '0' AND ISNULL(WFD_AttBool8, 0) = 1) THEN 'Tak' ELSE 'Nie' END
FROM
WFElements JOIN
WFSteps ON WFD_STPID = STP_ID JOIN
WorkFlows ON WF_ID = STP_WFID
WHERE
WF_GUID = 'af556142-7209-44e1-9aa2-550c2cd361cf' AND /* GUID of the workflow */
WFD_ID = wfelems.WFD_AttChoose4_ID /* relation to parent workflow */
)
)
Would shorten down to:
IIF(DTYPE_GUID = '51a1dfbc-de3b-4a59-92c5-3342435b0088' /*document guid*/, 'Tak', (
SELECT
CASE WHEN (ISNULL(WFD_AttBool7, 0) = 0 AND ISNULL(WFD_AttBool1, 0) = 0 AND ISNULL(WFD_AttBool2, 0) = 0 AND ISNULL(WFD_AttChoose45_ID, '0') = '0' AND ISNULL(WFD_AttBool8, 0) = 1) THEN 'Tak' ELSE 'Nie' END
FROM
V_WFElements
WHERE
WF_GUID = 'af556142-7209-44e1-9aa2-550c2cd361cf' AND /* GUID of the workflow */
WFD_ID = wfelems.WFD_AttChoose4_ID /* relation to parent workflow */
)
)
Less joins, and no subquery :)
25.06.2024
13:32
