Need suggestion to setup Multiselect dynamic attributes

[pborah]

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:

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.

Can you plz suggest.

[sk]

Try below

r.customproperty20 in (${environment})

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

[pborah]

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?

[rushikeshvartak]

This is known issue of multiselect

[pborah]

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

[rushikeshvartak]

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

[pborah]

Hi,

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

[rushikeshvartak]

share logs

[pborah]

Hope this helps.

[rushikeshvartak]

do same thing with customproperty7

[sk]

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

[pborah]

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

[rushikeshvartak]

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(",","','")})

[sk]

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

[pborah]

Thank you both.

However both the queries are not working for me.

[pborah]

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]

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)

[rushikeshvartak]

Your in values are not quoted with single quotes 

[sk]

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

[pborah]

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]

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.

[rushikeshvartak]

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

[pborah]

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

[rushikeshvartak]

Paste logs when selected one & two values

[pborah]

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

[rushikeshvartak]

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)

[pborah]

Got the below error

[rushikeshvartak]

Run query from data analyzer

[pborah]

Can you plz suggest the query I should test?

[rushikeshvartak]

Copy query from logs , make required changes and run