Leveraging Intelligent Recommendations for Operational Transformation. AMS Partners click HERE | EMEA/APJ Partners click HERE |
05/16/2024 03:34 AM
We have a requirement where we have to limit the view of a user to those entitlements that belongs to his company.In case of self and request for other case User , dynamic attribute to fetch user query works .But the same query fails for multiuser case.
Sample Query :
select CUSTOMPROPERTY10 as id from users u where CUSTOMPROPERTY11 = 'External' and u.email = '${user.email}'
Solved! Go to Solution.
05/16/2024 06:34 AM
Hi @meghna_alias ,
The below query is working:
select CUSTOMPROPERTY10 as id from users where CUSTOMPROPERTY11 = 'External' and userkey in (${requestee})
Please try!
If this answers your solution, please consider selecting Accept As Solution and hit Kudos
05/16/2024 06:41 AM
I have tried this , but the query doesnt return results when we request for multi user case.Thus DA attribute is empty and entitlements are not loaded
05/16/2024 06:55 AM - edited 05/16/2024 06:57 AM
05/16/2024 07:51 AM
yes
05/16/2024 07:37 AM
@meghna_alias try like below for multiuser
select (group_concat(distinct u.CUSTOMPROPERTY10)) as ID from users u where u.CUSTOMPROPERTY11 = 'External' and u.userkey in (${requestee})
05/16/2024 12:41 PM
06/10/2024 04:08 AM
Hi ,
The group_concat returns a list of string thus cannot use in function on the result.
is there a way to achieve it ?
06/10/2024 04:38 AM
Actually dynamic attibutes not scope for multi user request Saviynt confimed to us.
06/10/2024 07:10 PM
05/16/2024 12:43 PM
Just view the attached queries . First 2 queries are working as you have suggested.
I want the third one to work in case of multiuser , based on the values of first (dUserType) and second (dCompany)
05/19/2024 10:24 PM
It would be really helpful if you could suggest me the query.
05/20/2024 07:01 PM
Please share request form screenshot to see data and current sample output of query 3
select (group_concat(CASE WHEN u.CUSTOMPROPERTY11 = 'External' THEN 'True' ELSE 'False' END)) as id from users u where u.userkey in (${requestee})
select (group_concat(distinct u.CUSTOMPROPERTY10)) as ID from users u where u.CUSTOMPROPERTY11 = 'External' and u.userkey in (${requestee})
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where CUSTOMPROPERTY12="OriginalOperator" AND ENTITLEMENTTYPEKEY= 5 AND status='1' AND CUSTOMPROPERTY14=${dApplication} AND (CASE WHEN ${dUserType} = 'True' THEN CUSTOMPROPERTY11 = (${dCompany}) ELSE 1 END)
05/20/2024 08:41 PM
If we select users 1,2,3 in multi user form , query 3 should return ABC
If we select users 4,5,6 ,it should return XYZ
If we select 7 both ABC and XYZ should be returned.
05/20/2024 09:26 PM
What is output of below query
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where CUSTOMPROPERTY12="OriginalOperator" AND ENTITLEMENTTYPEKEY= 5 AND status='1' AND CUSTOMPROPERTY14=${dApplication} AND (CASE WHEN ${dUserType} = 'True' THEN CUSTOMPROPERTY11 = (${dCompany}) ELSE 1 END)
05/20/2024 09:38 PM
It doesnt return anything now as ${dUserType} and ${dCompany} is a list of string.
Expect the result to be SQL ENUM , companies matching users company.
05/20/2024 09:59 PM
username is dynamic attribute ? Can you share request form screenshot ?
05/20/2024 10:11 PM
username is not a DA.
05/20/2024 10:17 PM
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where CUSTOMPROPERTY12="OriginalOperator" AND ENTITLEMENTTYPEKEY= 5 AND status='1' AND CUSTOMPROPERTY14=${dApplication} AND (CASE WHEN ${dUserType} = 'True' THEN CUSTOMPROPERTY11 = (${dCompany}) ELSE 1 END) and ${dCompany}!='Default' UNION
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where ENTITLEMENTTYPEKEY= 5 AND status='1' and ${dCompany}='Default'
05/20/2024 10:23 PM
Hi Rushikesh ,
Thank you for your prompt reply.
But my case is like
dUserType = ['True','True']
dCompany = ['ABC','XYZ']
I want the query to execute in loop for [True ,ABC ] and [True ,XYZ] and return [ABC ,XYZ]
05/20/2024 10:25 PM
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where CUSTOMPROPERTY12="OriginalOperator" AND ENTITLEMENTTYPEKEY= 5 AND status='1' AND CUSTOMPROPERTY14=${dApplication} AND (CASE WHEN ${dUserType} = 'True' THEN CUSTOMPROPERTY11 = (${dCompany}) ELSE 1 END) and ${dCompany}!='Default' UNION
select DISTINCT REPLACE(CUSTOMPROPERTY11,',','') as ID from entitlement_values where ENTITLEMENTTYPEKEY= 5 AND status='1' and (${dCompany}='ABC' OR ${dCompany}='XYZ') and ${dUserType}='TRUE'