Home > Forum > Actions > Add substitution for more than one person

Add substitution for more than one person
0

Hi,
In my "Leave of absence" process I allow users to select more than one person who will be the substitute during the leave. Action "Add substitution" (not sure it's name is exactly like this, I have Polish version) in current version, provided with the form field containing more than one person, adds the substitution only for the first one. It seems to have been working some versions ago. Is it normal now, does anyone have a workaround?

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
```

In reply to: Daniel Töpel

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...'