To get user data in Multi User Request Form

[meghna_alias]

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

[PremMahadikar]

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

[meghna_alias]

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

[PremMahadikar]

@meghna_alias ,

Is it single select value and query is also configured in default?

[meghna_alias]

yes

[Raghu · Accepted solution]

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

[meghna_alias]

Hi ,

The group_concat returns a list of string thus cannot use in function on the result.
is there a way to achieve it ? 

[Raghu]

Actually dynamic attibutes not scope for multi user request Saviynt confimed to us.

[rushikeshvartak]

• Please share logs
• Share data analyzer output 
• Also untick solution if accepted solution is not working

[meghna_alias]

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)

[meghna_alias]

@Raghu 

It would be really helpful if you could suggest me the query.

[rushikeshvartak]

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)

[meghna_alias]

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.

[rushikeshvartak]

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)

[meghna_alias]

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.

[rushikeshvartak]

username is dynamic attribute ? Can you share request form screenshot ?

[meghna_alias]

username is not a DA.

[rushikeshvartak]

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'

[meghna_alias]

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]

[rushikeshvartak]

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'