Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

To get user data in Multi User Request Form

meghna_alias
New Contributor
New Contributor

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

20 REPLIES 20

PremMahadikar
Valued Contributor
Valued Contributor

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
New Contributor
New Contributor

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

@meghna_alias ,

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

 

yes

 

Raghu
Honored Contributor
Honored Contributor

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

 

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
Honored Contributor
Honored Contributor

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

Raghu_0-1718019494224.png

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

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

Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

meghna_alias
New Contributor
New Contributor

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
New Contributor
New Contributor

@Raghu 

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

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)


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

meghna_alias_0-1716262802809.pngmeghna_alias_1-1716262829612.png

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.

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)


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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.

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

meghna_alias_0-1716268248025.png

username is not a DA.

 

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'


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

meghna_alias
New Contributor
New Contributor

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]

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'


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.