Request Roles Query is not working with {currentUser} and {requestor}

[yogarajk]

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

[PremMahadikar]

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

[yogarajk]

Hi @PremMahadikar ,

The query is not working for me. Below is the error log.

Thanks & Regards,

Yogaraj K

[rushikeshvartak]

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})
)

[Raghu]

@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}))

[PremMahadikar]

@yogarajk ,

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})

[yogarajk]

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

[rushikeshvartak]

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}) )

[yogarajk]

Query is not working in V23.5. Is there any limitation or bug in V23.5 for Role Request Query?

Thanks & Regards,

Yogaraj K

[rushikeshvartak]

Check audit log.

[yogarajk]

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

[rushikeshvartak]

Validate same with 24.x once upgraded