Calculated Column - SQL Query from another workflow

0

Dear Patryk,
if I understand your question correctly, you can use wfelems (alias) in query.

Here is an example of the query I use to get value from another (related) workflow (in this case it's Signature) in calculated column (I use type text):

(
select WFD_Signature as Signature
from WFElements
where wfelems.WFD_WFDID = WFD_ID and WFD_DTYPEID = '<id of the form type>''
)

I also specify WFD_DTYPEID "filter" results for specific workflow.

Hope this helps you solve your case.

Thanks, it mostly works. The problem is that I can't filter by value of another column from the report.
So I have this report where I have employee column (WFD_Attchoose1) and for this employee I need to do the select. So in each row I need to reference this employee and I don't know how to do it, how to pass this value into the script.

(
SELECT SUM(WFD_AttDecimal1) AS TotalWorkingDays
FROM WFElements
WHERE WFD_AttChoose4 LIKE --- This I need to reference from report column so put per row value of AttChoose1 ---
AND WFD_STPID = 252
AND YEAR(WFD_AttDateTime2) = YEAR(GETDATE())
)

Patryk try this

(
SELECT SUM(WFD_AttDecimal1) AS TotalWorkingDays
FROM WFElements
WHERE WFD_AttChoose4 = wfelems.WFD_AttChoose1
AND WFD_STPID = 252
AND YEAR(WFD_AttDateTime2) = YEAR(GETDATE())
)

Hi Patryk,
And just remember that WFD_STPID will be different for DEV/TEST/PROD environments (if you want to indicate a specific step you should use the GUID).

I'd say don't ever use ID in calculated columns - always go for GUID if you don't want to have broken views after every export/import to prod.

Here is updated query based on Krystians answer:

(
SELECT SUM(WFD_AttDecimal1) AS TotalWorkingDays
FROM WFElements
LEFT OUTER JOIN WFSteps ON STP_ID = WFD_STPID
WHERE WFD_AttChoose4 = wfelems.WFD_AttChoose1
AND STP_GUID = 'HERE THE GUID'
AND YEAR(WFD_AttDateTime2) = YEAR(GETDATE())
)