Clarity

Hello,

I have created a custom grid portlet. The param_obs is assigned to lookup: obs projects filter browse.

I want to make this single select obs to multi select. Is there any OOTB lookups that gives multi select OBS or do i need to create a custom lookup for this.

This should help:

Learn PPM with Rego: Custom Object's Sequence Table, Jaspersoft "Loading," Vendor Not Valid, First Day of Work Week, Delete Budget Plan, and Multi-Select OBS Filter

NJ

Do you have the NSQL query for the custom lookup. All i have created endup not displaying the multi select option.

I don't think the problem is the lookup in the first place.

If your NSQL is something like

@where:PARAM:USER_DEF:INTEGER:OBS_ID@  IS NULL or @where:PARAM:USER_DEF:INTEGER:OBS_ID@=some_ID_from_the_query)

and maybe supplemented with the association mode the structure is such that it will automatically default to single select as it cannot handle more than one value. No lookup of any kind will change that.

I should use the type of OBS unit filter (which is not supposed to work).

What you would have to have is the display field to be Unit name and the hidden field the unit id which has to be in the NSQL query as well.

I am not sure if the association mode work with that.

Hi Puneeth - Did urmas's response help answer your question? If so please mark as Correct Answer. Thanks!

Hello,

I have tried this multi obs and struck in one small thing. I am filtering the portlet using the project code. When i am passing the project code in the filter i am getting multiple rows due to the project is associated in the prj_obs_assocations table multiple times. 

I have also tried the distinct keyword which is not working here. And when i am filtering the projects using obs filter the portlet works fine. Can you please let me know how to optimize the query.

select
ROW_NUMBER() OVER (ORDER BY i.id) as row_number,
obsa.UNIT_ID unitid ,obsa.RECORD_ID recordid from niku.inv_investments i
inner join niku.PRJ_OBS_ASSOCIATIONS obsa
on i.ID=obsa.RECORD_ID

where i.CODE='Project code'

When you provide an OBS you are restricting the rows in PRJ_OBS_ASSOCIATIONS, when you don't provide the OBS you are not ; therefore you get multiple results (if your project has multiple possible OBS relationships).

You could recode the SQL so it is not just a join against PRJ_OBS_ASSOCIATIONS - if you code it as a "WHERE EXISTS" sub-select against PRJ_OBS_ASSOCIATIONS using a user-parameter-OBS field then that would work but would then not allow you to have any multi-select against the OBS (your original question).

Thanks dave for your quick response.

If we go for sub query field the multiple obs filter is not working. 

( units.ID is null or @where:param:user_def:integer:units.ID:obs@)

Multiple OBS filter will not work inside the subquery. So we cannot use " WHERE EXISTS".

^ yes I know, that is why I warned that it would not work.