The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
The community will be in read-only from Tuesday 11:59pm (PST) to Wednesday 7:30am (PST)
Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Portlet Filter FIield query for multi-select

SOLVED
Go to solution
Highlighted
Raj Ghimire
Occasional Visitor

Portlet Filter FIield query for multi-select

I am getting a little confued on how I can configure the sql query for where clause for multi-select enabled Fitler field so that I can list more than one values in the filter and get results which will contain either one of the values listed or Both.
I am using query like below but when I put a;b in the filter , this is giving me results with values a;b but doesn't give me any with either just a or just b.

krd.parameter46 IN ('[P.SERVICES_IMPACTED]')

Any suggestions on what I might need to do here ?

-Raj
14 REPLIES
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

Will REPLACE('string',';',',') function somehow help here ? I have been trying this without much success though.
shygirl
Regular Collector

Re: Portlet Filter FIield query for multi-select

Hi,

Try this...

AND INSTR('P.SERVICES_IMPACTED]',krd.parameter46)>0
Erik Cole_1
Esteemed Contributor

Re: Portlet Filter FIield query for multi-select

Change to VP and add TO_STRING to the token, leaving out the quotes:

AND krd.parameter46 IN ([VP.SERVICES_IMPACTED.TO_STRING])
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

Thank You both for suggestions.
But doesn't look like its working.

Eric, when I use:
AND krd.visible_parameter45 IN ([VP.SERVICES_TO_CHANGE.TO_STRING])
it kind of results in similar output for me.
When I put a;b in the filter, it only gives me output where either the value is a or b. But some requests which may have both a and b(a;b) get left out.

The IN works wonderfully when the filter field in Request Type Doesn't have multi-select enabled though. I am having issues with those which are multi-select enabled in RT as well as in Portlet filter.

I am sure I might be missing something here but not quite sure what.
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

I have attached the data soruce for filter.

Not sure if â #@#â somehow comes into picture here.
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

-------------
AND (
(krd.parameter46 = replace('[P.SERVICES_IMPACTED]', ',', '#@#')) or (krd.visible_parameter46 IN ([VP.SERVICES_IMPACTED.TO_STRING]))
)
---------------

Ophs !
Eric, you are correct !
Looks like the above filter query doesn't pull the result if the order is different(b;a) as they are pulled in as char.
Erik Cole_1
Esteemed Contributor

Re: Portlet Filter FIield query for multi-select

That will get

Web Applications (Public); Web Applications (Internal)

but not

Web Applications (Internal); Web Applications (Public)
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

But even when I just use
krd.visible_parameter46 IN ([VP.SERVICES_IMPACTED.TO_STRING])

it only gives me values for just a or just b.
Any additional suggestions for me guys ?
-Raj
Erik Cole_1
Esteemed Contributor

Re: Portlet Filter FIield query for multi-select

Have to say, you've got me stumped given the way PPM passes down the selected items. Let me think about it over the weekend... ;)
B Swanson
Regular Collector
Solution

Re: Portlet Filter FIield query for multi-select

Not sure if this helps, but I use the following statement all the time when I have a multiselect field on my form as well as a multiselect filter:

AND PM_UTILS.is_project_manager_id_in_list(v.PM_ID,replace('[P.FLT_PM]',',','#@#')) = 'Y'

This works great for Proj Manager custom portlet filters, but also for any auto-complete field.
v.pm_id = the P value list for the field
[P.FLT_PM] = the P value list for the filter
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

Looks like your package is utilizing PM_UTILS package function. I am not sure I can use that for my Custom field. Can I ?
Erik Cole_1
Esteemed Contributor

Re: Portlet Filter FIield query for multi-select

Oh yeah, that's a great idea...that function is just a generic list parser, you could certainly do it that way. Might not be as fast as a straight SQL solution, but it would get the job done for any order the list is in.

Great catch!
B Swanson
Regular Collector

Re: Portlet Filter FIield query for multi-select

Raj - yes, I use it all the time for custom fields. Basically whenever you have a multi-select field of any type. Not sure if extremely long list valuse would cause an issue, but I haven't run into one yet.
Raj Ghimire
Occasional Visitor

Re: Portlet Filter FIield query for multi-select

Wonderful!!
Thank You Guys !!!!
//Add this to "OnDomLoad" event