Clarity

Hi all.
I desire to have a Multi Select filter for a Multi Value Lookup in an NSQL grid portlet. The Multi Select rules out parameters, and of course I do not want to bring back a unique row per lookup. Essentially I'm looking for an "OR" out of the multi select and simply want to know if any of those values are "IN" the Multi Value for the portlet's unique item. I do not wish to bring the values to the portlet's grid UI, I merely want to filter to show who has hits for the users to drill into.

It's late and I've been pounding my head against this for a couple hours so I hope I'm making sense.

I'm scheming up sub selects and creating new views, so I thought I'd just toss this out here and walk away for a bit before I get myself into trouble.

Thanks!

In the query, join your "base object" against the mutli-valued-attribute table (ODF_MULTI_VALUED_LOOKUPS) - so that you will potentially get multiple records per "base object instance" in the query results.

In the portlet, put the lookup-attribute (VALUE from that ODF table I think) into the Filter section and implement as a multi-valued lookup. In the list section, do not include the VALUE attribute in the list-grid.

Thats it?

Problem will be that where your "base object instance" has (say) 2 values in its multi-valued lookup and you search for both values in the portlet; then the base object instance will get reported back twice (so maybe it IS a good idea to include that attribute in the portlet list after all).

--

Otherwise I think you are in the territory of implementing multiple (parameter) fields and 'manually' coding a big "OR" or "IN" clause in the NSQL - but that just is a really ugly solution I think.

??

Try this approach.

Sample Query..

SELECT @SELECT:DIM:USER_DEF:IMPLIED:inv:code:code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:inv:name:name@


FROM
(SELECT inv.code, inv.name
FROM inv_investments inv
WHERE id IN (SELECT pk_id FROM ODF_MULTI_VALUED_LOOKUPS
WHERE attribute = 'jdi_business_area'
AND (value = @where:param:user_def:string:area1@
OR nvl(@where:param:user_def:string:area1@, '0') = '0')))

WHERE @FILTER@
HAVING @HAVING_FILTER@



This query will select investments for the parameter passed . If it is not passed then, it will select all. It will only show investments once.

The only issue with this is that it will not allow multiselect filter.

Still working this one.
Dave, yes, you're understanding of what I'm trying is correct, but yes, the problem is the multiple returns. The desired functionality is to not duplicate entries, so this approach is a show stopper.

Atul, you're approach works, but the problem I'm seeing now is that the multi-value lookup is not required. The IN statement approach cannot handle NULLS.

I think, in order for this to work cleanly and easily for us Studio Developers, I'm in enhancement request territory.

Since we're just now rolling this sub object out, if I can make my 3 MVLs required, in time if we build out a bed of data where every value is used at least once, then I can roll in Atul's solution. Option #2 here is make 'em required and seed at least one with all selected while the data builds out...

Any other ideas are appreciated, but I think I'm getting ready to walk away from this one and try the "required field" approach and maybe seed one Lesson Learned with all values selected...

No fair Rob you are changing the goalposts!! :tongue

You started out with a Q about multi-select filter fields, and now you are on about NULLs? :grin:

.... for sure you can not get multi-select attributes mandatory (a real pain) - but if you are "coding" the where clause (like in Atul's response) you can cope with NULLs (in fact Atul's code is trying to cope with them already)

--

Where I have had to get around the "can't make multi-select lookups mandatory" problem I have put in a "Primary Lookup" (a normal lookup so I can make it mandatory) and then a "Other Lookup(s)" being multi-select (whith teh same lookup associated to both attributes). But then the NSQL that READS that data in any portlets has to look in 2 places for it. I still can't implement a multi-select as a portlet FILTER field though (apart from how I describe it in my earlier post where I am restricted to it being part of the returned values as well).

:-(

Apologies Dave on moving the goalposts - I'm just trying to come up with a solution that'll work for my end users even if it requires some compromise. I now see that you can't make an MVL required. Atul's code handles null from the filter parameter, but the attribute constraint in the where clause (WHERE attribute = '<your attribute here>') eliminates all rows that do not have a value for this attribute. Even with no filter selection, this clips off data.

I think the answer is "we won't be filtering on those attributes right now".

I'm just kind of frustrated Studio will let you create something (attributes with MVLs) that has such downstream limitations when you try to leverage it in portlets.

Thanks all for your help. Unless I misunderstand this, I think I'm done for now with "we can't do that".