Project and Portfolio Management Practitioners Forum
cancel

Multis select on a custom portlet

SOLVED
Go to solution
Highlighted
patrick-sa
Super Contributor.

Multis select on a custom portlet

Hi,

 

I’ve created a custom portlet and my problem is filtering by the project manager. The actual problem is that when you run this query:

select p.prj_project_manager_user_id from kcrt_fg_pfm_project p

where p.prj_project_manager_user_id = 1

 

The db returns the error

ORA-01722: invalid number

01722. 00000 -  "invalid number"

 

When you run this query using “like” instead of “=” then it runs well.

 

select p.prj_project_manager_user_id from kcrt_fg_pfm_project p

where p.prj_project_manager_user_id like 1

 

My problem is how to use “like” using “IN” so that I can do multiselect so that I can return projects assigned to different project managers. Any help will be appreciated.

4 REPLIES
Jason Nichols K
Acclaimed Contributor.

Re: Multis select on a custom portlet

The project manager column in the database is not a number field so that it can be a multi-select field.  If you look at a project with multiple project managers, that field would have 1#@#30345#@#130345 or something very similar based on the user IDs of the project managers.  When trying to filter on multi-select fields, I generally use something like this

 

AND instr('#@#' || p.prj_project_manager_user_id || '#@#', '#@#[P.FILTER_TOKEN]#@#') > 0

 

The requires that your filter is a single select filter field but also guarantees that if you had users with user IDs that were a subset of another (130345 and 30345) would only get the one you really wanted.

jsalass
Honored Contributor.

Re: Multis select on a custom portlet

Hey Patrick,

 

You can follow Jason recommendation or use a sql like this:

 

select replace(p.prj_project_manager_user_id,'#@#',',') from kcrt_fg_pfm_project p
where p.prj_project_manager_user_id in (replace(p.prj_project_manager_user_id,'#@#',','))

 

Also, you can wirte your own fuction to return the value you need but I think that the sql above would help you.

 

Hope this helps!

 

Regards,

Jonathan

patrick-sa
Super Contributor.
Solution

Re: Multis select on a custom portlet

thanks guys.i struggled to get it working with both your suggestions.I've found another way which is using trustee ids.The query is shown below and its working well.

 

 

and pfm.prj_project_id IN (  SELECT P.PROJECT_ID
                                  FROM  PM_PROJECTS P,
                                         ITG_TRUSTEE T,
                                      ITG_GROUP_PARTICIPANT_USERS PU,
                                      KNTA_USERS U
                                WHERE T.TRUSTEE_TYPE_CODE = 2
                                 AND T.PROJECT_TYPE_ID = P.PROJECT_TYPE_ID
                                 AND PU.TRUSTEE_ID = T.TRUSTEE_ID
                                 AND U.USER_ID = PU.USER_ID
                                 AND U.USER_ID IN ([P.PRJ_MNG]))

jsalass
Honored Contributor.

Re: Multis select on a custom portlet

Nice Patrick!!!

 

Please don't forget to mark the tread as solved :)

 

Regards,

Jonathan