Clarity

Hi - I have created my Portlet and everything seems to be working fine. However, I'd like for some of the fields on my filter section to be switched from text to a look-up of all existing values to avoid any type of mis-typing by the end-users.

Is there an easy way to do this? I thought I'd be able to change the Data Type or Display Type but that does not appear to be an option.

Thanks!

-Greg

If this is a NSQL based portlet, then in the query you need to return the "hidden key" of the lookup (as well as the "display name"), then in the "attributes" section of the query you associate that hidden-key value with the lookup. Then you should be able to include that "hidden key" attribute in the filter section of the portlet and select popup or browse as how it is displayed.

So in order to have a drop down or browse in a filter field you have associate the field in the query with a lookup.

As Dave points out lookups have  typically a display key which have the values as plain English and a hidden key which is a code or ID. Then display key is for the user to pick the desired value while the code or id is shorter for processing.

That means that you may need two field in you query. One for the plain English values for the query results and the second one for the filter field to pass the code or id. If you do not have the second filter field the filter may display the list OK and let you select, but the query will not return any results.

You can get away with just one field if you create a lookup that has the same field as hidden and display key. That works when your values are fairly short.

Thank you both for responding, I'm rather new to this so please bare with me. In order to do this I am pulling in two tables into the filter (ODF_CA_C_CLIENT / ODF_CA_INV) and now I need to bring the hidden keys in from those two tables into my query? Would they just be the ID fields? I already pulled in one of those ID fields, and when I look to add the look-up attribute I am able to pull back the values in the portlet, but the dataset is incomplete, which leads me to believe I'm looking up in the wrong location.

Here is my current query:

SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:I.ID:UniqueID@,
       @SELECt:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.C_OPPORTUNITY_ID:Opportunity_ID@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.C_SLOTTED_DATE:Slotted_Date@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:I.C_CONTRACT_SIGN:Contract_Signed@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:INV.Name:PROJECT_NAME@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:PROJECT:C.CODE:Mnemonic@,
       @SELECT:METRIC:USER_DEF:IMPLIED:I.C_SLOTTED_DATE - I.C_CONTRACT_SIGN:Days_To_Start@
FROM ODF_CA_INV I
     JOIN INV_INVESTMENTS INV
     ON I.ID = INV.ID
     JOIN ODF_CA_C_CLIENT C
     ON C.CODE = I.C_CLIENT
WHERE @FILTER@
GROUP BY
       I.C_OPPORTUNITY_ID,
       I.C_CONTRACT_SIGN,
       I.C_SLOTTED_DATE,
       INV.Name,
       I.ID,
       C.CODE

What would be the best way to Identify which lookup location is the correct one?

Thanks for your help.

-Greg

1. Which query field it is you want to have the drop down/browse filter.

2. I should search the existing lookups for the name of the field as displayed in the object properties to see if there already is a lookup that can be used.

Hey Urmas - I'm trying to have Mnemonic (C.Code) / Project Name (INV.Name) / Opportunity_ID (I.C_Opportunity_ID) as filter options.

Appreciate the assistance!

-Greg

To make the code easier to read (and perform slightly better), remove your GROUP clause.  You are not doing any SUM, MIN, MAX etc which requires grouping.

In relation to:

Mnemonic (C.Code) / Project Name (INV.Name) / Opportunity_ID (I.C_Opportunity_ID) as filter options.

C.Code - Clarity would have created a default look-up for this master table (I am assuming this is a master table, based on your join back to inv_investments and the table starting with odf_ca.  Have a look at that Lookup, as it will contain sample code which you can use to create your own custom lookup.

Project Name (INV.name) - Your look-up filter would be based on Inv.id - personally I like having the ability to do search for *ProjectName, instead of having to pull-up a list of project and selecting the required one

I.C_Opportunity_ID - you are going to have to give us more information on what this is, as it is a custom attribute.  Potentially it is a Lookup Custom Attribute and if so, just use that look-up.

Have a look at the OOTB query Missing Status Report, where several of the returns attributes in the Query are defined as 'Look-ups'.  Every one of those look-up attributes can be used in the Portlet Filter as Look-ups.

To me it looks that you already have the required ID field there>

SELECT @SELECT:DIM:USER_DEF:IMPLIED:PROJECT:I.ID:UniqueID@,

Associate lookup Project browse (SCH_BROWSE_PROJECT) with it.

That lookup displays both project name and Id so it could do for both fields

If you need to change the name of the browse window or either of the field labels you should create a copy and modify that.

Thanks Urmas - Ill give it a try. The code field that you have listed above is different though than the one I'm bringing in (two different tables). The code level is at the client level rather than at the project level.

Would it be best for me to create my own lookup to achieve this?

The other aspect is that when I try to add the Project Browse look-up into my Query Attribute, nothing pops up. Does this need to be called out somewhere else?

If the code is for the client then you need a different lookup. If it is a custom field then you have to create a new lookup.

No OOTB lookup can have custom fields.

What I see you try to associate the Project name with the Project browse lookup.

That does not work as Project name is a string field and would require a string lookup as you can see.

The project browse is a numeric lookup. Associate that with the unique ID and put that  the filter layout )and not the project name)

Just label it Project.

Thank you to everyone (urmas / RolandP / Dave_3.0) for helping out here, I was able to get this figured out.

-Greg