Add substitution for more than one person

0

Hi,

you can use an itemlist and into the action-template-config you have to choose "based on sql query".
Hope you can understand my pictures...

best regards
Daniel

---

Vertreter = substitute

sql example for copy & paste
```sql
SELECT dbo.[ClearWFElemID](WFD_AttChoose3) Person,
dbo.[ClearWFElemID](WFD_AttChoose2) Acting,
CASE WHEN dbo.ClearWFElemIDAdv(WFD_AttChoose5) LIKE 'datum' THEN WFD_AttDateTime3 ELSE WFD_AttDateTime2 END DateFrom,
CASE WHEN dbo.ClearWFElemIDAdv(WFD_AttChoose5) LIKE 'datum' THEN DATEADD(day, 1, WFD_AttDateTime4) ELSE WFD_AttDateTime1 END DateTo,
0 Type,
NULL ProcessId,
Cast(1 AS INT) CompanyId
FROM wfelements
WHERE wfd_id = 10263 AND WFD_AttChoose2 IS NOT NULL AND WFD_AttChoose2 NOT LIKE ''

UNION ALL

SELECT dbo.[ClearWFElemID](WFD_AttChoose3) Person,
dbo.[ClearWFElemID](DET_Att2) Acting,
CASE WHEN dbo.ClearWFElemIDAdv(WFD_AttChoose5) LIKE 'datum' THEN WFD_AttDateTime3 ELSE WFD_AttDateTime2 END DateFrom,
CASE WHEN dbo.ClearWFElemIDAdv(WFD_AttChoose5) LIKE 'datum' THEN DATEADD(day, 1, WFD_AttDateTime4) ELSE WFD_AttDateTime1 END DateTo,
0 Type,
dbo.ClearWFElemIDAdv(DET_Att1) ProcessId,
1 CompanyId
FROM wfelementdetails det
JOIN wfelements e ON e.wfd_id = det.det_wfdid
WHERE det_wfdid = 10263
```

Thanks Daniel,
Even though your solution will not work for me (it would require some changes in the form), your reply guided me in the right direction. I now use the sql based action and the key to success is to have the query return one substitutor per row. Works well.

My query is:

SELECT '[Worker login from the form]' as Person,
cos1.COS_Login as Acting,
CAST ('[start datetime from the form]' AS datetime) as DateFrom,
CAST ('[end datetime from the form]' AS datetime) as DateTo,
0 as Type
FROM CacheOrganizationStructure cos1
JOIN dbo.splittotable('XXX', ';') spl ON spl.item=CONCAT(cos1.COS_Login,'#',cos1.COS_AD_Displayname)

*where XXX is the output of the Substituting Person/User Select box in format 'UserLogin1#UserName1;UserILogin#UserName2;etc...'