Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
06/11/2024 10:48 PM - edited 06/11/2024 11:28 PM
Hi Team,
We have created 4 different enterprise roles. The roles have to be visible in ARS page based on employee type and requestor's SAV roles.
We have tried the below query for employee type:
and (substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser} ) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser})))
Below query for SAV role:
and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor})
Both the queries are working if we configured separately. but the same query is not working combined of both queries.
and ((substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser} ) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser}))) and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor}))
Error screenshot below:
Log message: {currentUser} binding variable is calculating in logs but {requestor} binding variable is not calculating.
Please assist us on the same.
Thanks,
Yogaraj K
06/12/2024 02:23 AM
Hi @yogarajk ,
Please try the below, its working for me.
and (substring_index (rl.customproperty1,',',1) = (select users1.employeeType from Users users1 where users1.id={currentUser}) or substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users2.employeeType from Users users2 where users2.id={currentUser})) and rl.customproperty3 = (select users3.customproperty36 from Users users3 where users3.id={requestor})
If this answers your question, please consider selecting Accept As Solution and hit Kudos
06/12/2024 05:34 AM
Hi @PremMahadikar ,
The query is not working for me. Below is the error log.
Thanks & Regards,
Yogaraj K
06/12/2024 05:46 AM
and (
(
substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser})
or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser})
)
and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor})
)
06/12/2024 08:05 AM
@yogarajk use instead of '=' use 'in' and try
and ((
substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser})
or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser})
)
and rl.customproperty3 = (select users.customproperty36 from Users users where users.id in {requestor}))
06/12/2024 06:01 AM
substring_index had a space. Corrected!
and (substring_index(rl.customproperty1,',',1) = (select users1.employeeType from Users users1 where users1.id={currentUser}) or substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users2.employeeType from Users users2 where users2.id={currentUser})) and rl.customproperty3 = (select users3.customproperty36 from Users users3 where users3.id={requestor})
06/12/2024 10:20 PM
Hi @Raghu @PremMahadikar @rushikeshvartak
I tried with the above queries, but no luck. Still getting the same error message in ARS.
Thanks & Regards,
Yogaraj K
06/12/2024 10:41 PM
Query is working as expected in v24.5
and ( ( substring_index(rl.customproperty1, ',', 1) = (select users.employeeType from Users users where users.id={currentUser}) or substring_index(substring_index(rl.customproperty1, ',', 2), ',', -1) = (select users.employeeType from Users users where users.id={currentUser}) ) and rl.customproperty3 = (select users.customproperty36 from Users users where users.id={requestor}) )
06/12/2024 10:46 PM
Query is not working in V23.5. Is there any limitation or bug in V23.5 for Role Request Query?
Thanks & Regards,
Yogaraj K
06/12/2024 10:49 PM
Check audit log.
06/13/2024 11:51 PM
Hi @rushikeshvartak ,
I tried with the below query, but no luck.
and ((substring_index (rl.customproperty1,',',1) = (select users.employeeType from Users users where users.id={currentUser}) or (substring_index(substring_index(rl.customproperty1,',',2),',',-1)=(select users.employeeType from Users users where users.id={currentUser}))) and (substring_index (rl.customproperty3,',',1) = (select users.customproperty36 from Users users where users.id={requestor}) or (substring_index(substring_index(rl.customproperty3,',',2),',',-1)=(select users.customproperty36 from Users users where users.id={requestor}))))
Error message:
Thanks & Regards,
Yogaraj K
06/14/2024 11:58 AM
Validate same with 24.x once upgraded