We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Need suggestion to setup Multiselect dynamic attributes

pborah
Regular Contributor
Regular Contributor

Hi there,

We have a requirement to setup multiselect values from the dropdown of a dynamic attribute named environment. Following are the configurations we have done:

pborah_0-1674047429569.png

Query used: select distinct customproperty20 as id from roles where customproperty19 = ${role} and customproperty20 is not null and endpointkey=XXX

Inside request query added following

r.customproperty20 like '%${environment.replace("','","%' or r.customproperty20 like '%")}%'

However when we select one value from the dropdown or nothing is selected it shows the roles but when multiple values selected nothing shows up.

pborah_3-1674047793828.png

Can you plz suggest.

 

 

 

 

29 REPLIES 29

sk
All-Star
All-Star

Try below

r.customproperty20 in (${environment})

And I assume ${environment} is storing values in comma separated value when you selected multiple values. 


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

pborah
Regular Contributor
Regular Contributor

Hi,

If I use r.customproperty20 in (${environment}), I can have the values displayed for multiple selection from the dropdown (first screenshot) but if I select one value from the dropdown it is not showing any result (2nd screenshot).

I want to have both the scenarios. Is it possible somehow?

pborah_0-1674112914816.png

pborah_2-1674113007870.png

 

 

This is known issue of multiselect


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

Thank you for the response. Is there any alternate option to setup this?

r.customproperty20 IN ('${environment.toString().trim().replace("'","").replace(",","','")}')


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

pborah
Regular Contributor
Regular Contributor

Hi,

This is not working for me. However can you plz tell me what we are trying to do out of this?

rushikeshvartak
All-Star
All-Star

share logs


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

pborah_0-1674140625581.png

Hope this helps.

do same thing with customproperty7


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

sk
All-Star
All-Star

I see something wrong with query syntax. Can you please share the whole query you have used inside request query?


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

pborah
Regular Contributor
Regular Contributor

r.customproperty18 = '${company}' and r.customproperty7 in (${entity}) and r.customproperty19 = '${role}' and r.customproperty20 IN (${environment.toString().trim().replace("'","").replace(",","','")})

r.customproperty18 = '${company}' and r.customproperty7 in (${entity.toString().trim().replace("'","").replace(",","','")})
and r.customproperty19 = '${role}' and r.customproperty20 IN (${environment.toString().trim().replace("'","").replace(",","','")})

 


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

sk
All-Star
All-Star

If rushi statement didn't work try below as well

r.customproperty18 = '${company}' and (r.customproperty7 in (${entity}) or r.customproperty7='${entity}') and r.customproperty19 = '${role}' and (r.customproperty20 in (${environment}) or r.customproperty20='${environment}')


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

pborah
Regular Contributor
Regular Contributor

Thank you both.

However both the queries are not working for me.

 

pborah
Regular Contributor
Regular Contributor

Error logs

2023-01-20/06:32:28.960 [{}] [https-jsse-nio-443-exec-50] DEBUG workflow.WorkflowmanagementController - listQry:select r from Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc
2023-01-20/06:32:28.969 [{}] [https-jsse-nio-443-exec-3] DEBUG workflow.WorkflowmanagementController - listQry:select r from Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id in(0)
2023-01-20/06:32:28.973 [{}] [https-jsse-nio-443-exec-50] ERROR hql.PARSER - <AST>:0:0: unexpected end of subtree
2023-01-20/06:32:28.974 [{}] [https-jsse-nio-443-exec-50] ERROR hql.PARSER - <AST>:0:0: unexpected end of subtree
2023-01-20/06:32:28.978 [{}] [https-jsse-nio-443-exec-50] ERROR errors.GrailsExceptionResolver - QuerySyntaxException occurred when processing request: [POST] /ECM/workflowmanagement/applicationRoleMapjson
unexpected end of subtree [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc]. Stacktrace follows:
org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc]
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure344.doCall(WorkflowmanagementController.groovy:20554)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:145)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2023-01-20/06:32:28.981 [{}] [https-jsse-nio-443-exec-50] ERROR error.ErrorController - Exception
org.codehaus.groovy.grails.web.errors.GrailsWrappedRuntimeException: unexpected end of subtree [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc]
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:145)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected end of subtree [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc]
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure344.doCall(WorkflowmanagementController.groovy:20554)
... 9 more

pborah
Regular Contributor
Regular Contributor

Some more logs

2023-01-20/06:32:41.185 [{}] [https-jsse-nio-443-exec-37] DEBUG workflow.WorkflowmanagementController - listQry:select r from Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc
2023-01-20/06:32:41.185 [{}] [https-jsse-nio-443-exec-37] ERROR hql.PARSER - line 2:76: unexpected token: ','
2023-01-20/06:32:41.202 [{}] [https-jsse-nio-443-exec-37] ERROR errors.GrailsExceptionResolver - QuerySyntaxException occurred when processing request: [POST] /ECM/workflowmanagement/applicationRoleMapjson
unexpected token: ',' near line 2, column 76 [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc]. Stacktrace follows:
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ',' near line 2, column 76 [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc]
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure344.doCall(WorkflowmanagementController.groovy:20554)
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:145)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
2023-01-20/06:32:41.205 [{}] [https-jsse-nio-443-exec-37] ERROR error.ErrorController - Exception
org.codehaus.groovy.grails.web.errors.GrailsWrappedRuntimeException: unexpected token: ',' near line 2, column 76 [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc]
at grails.plugin.springsecurity.web.filter.GrailsAnonymousAuthenticationFilter.doFilter(GrailsAnonymousAuthenticationFilter.java:53)
at com.saviynt.webservice.SaviyntRestAuthenticationFilter.doFilter(SaviyntRestAuthenticationFilter.groovy:145)
at grails.plugin.springsecurity.web.authentication.logout.MutableLogoutFilter.doFilter(MutableLogoutFilter.java:62)
at grails.plugin.springsecurity.web.SecurityRequestHolderFilter.doFilter(SecurityRequestHolderFilter.java:59)
at com.mrhaki.grails.plugin.xframeoptions.web.XFrameOptionsFilter.doFilterInternal(XFrameOptionsFilter.java:69)
at com.brandseye.cors.CorsFilter.doFilter(CorsFilter.java:82)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ',' near line 2, column 76 [select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc]
at com.saviynt.ecm.workflow.WorkflowmanagementController$_closure344.doCall(WorkflowmanagementController.groovy:20554)

Your in values are not quoted with single quotes 


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

In the first set of logs i see below query

select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in ()
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN ()) order by r.role_name asc

and second set of logs 

select r from com.saviynt.ecm.identitywarehouse.domain.Roles r where r.status=1 and r.requestable=true and r.roletype=4 and r.endpointkey=366 and r.id not in(0) and r.id not in(-1) and (r.customproperty18 = 'PMI' and r.customproperty7 in (Argentina (Merlo) - 75)
and r.customproperty19 = 'NC/DP Coordinator' and r.customproperty20 IN (DEV','QAS)) order by r.role_name asc

Do you know the data and query used in both these trails. First set of logs I don't see variables used capturing the respective values. whereas second set of logs I see variables used captured the respective values but didn't paste the values surrounded with quotes.

If we know the queries used for these we can try to modify them to include quotes single quotes so that it can work


Regards,
Saathvik
If this reply answered your question, please Accept As Solution and give Kudos to help others facing similar issue.

pborah
Regular Contributor
Regular Contributor

Hi,

When we use below query

r.customproperty18 = '${company}' and r.customproperty7 in (${entity}) and r.customproperty19 = '${role}' and r.customproperty20 in (${environment})

and select multvalues from the dropdown the it works fine and the vales comes inside the single quote as: ('DEV','QAS').

However when single value is selected from the dropdown the value is passed as (DEV) and without quote. So system gives an error and it does not return any role.

pborah
Regular Contributor
Regular Contributor

As of now only below is working

r.customproperty18 = '${company}' and r.customproperty7='${entity}' and r.customproperty19 = '${role}' and r.customproperty20 in (${environment})

and this is displaying the roles if multiple vales are being selected. If single value is selected this does not work.

r.customproperty18 = '${company}' and r.customproperty7 in (${entity.toString().trim().replace(",","','")})
and r.customproperty19 = '${role}' and r.customproperty20 IN (${environment.toString().trim().replace(",","','")})


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

The above query does not return results while selecting single as well as multiple value from the dropdown. Below is the error log:

pborah_0-1674456178744.png

 

 

Paste logs when selected one & two values


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

PFA log details when selected one and multiple values. Meanwhile let me try the below new query

r.customproperty18 = '${company}' and r.customproperty7 in (case when ${entity} like '%,%' then ${entity} else ${"".concat("'".concat(entity).concat("'"))} end) and r.customproperty19 = '${role}' and r.customproperty20 IN (case when ${environment} like '%,%' then ${environment} else ${"".concat("'".concat(environment).concat("'"))} end)


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

Got the below error

 

pborah_2-1674571459334.png

 

 

Run query from data analyzer


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

pborah
Regular Contributor
Regular Contributor

Can you plz suggest the query I should test?

 

Copy query from logs , make required changes and run


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