Clarity

 View Only
  • 1.  Using Project Stage As Filter For Custom Query Based Portlet

    Posted Mar 26, 2019 08:22 AM

    Hello,

     

    I am trying to use project Stage as a lookup filter for a query based portlet, but when I try to set the stage attribute as a lookup on the query's Attributes tab, I can't find the Investment Type (INV_TYPE) lookup in the list.

    Am I doing something wrong?

     

    Thanks!

     

    Marius



  • 2.  Re: Using Project Stage As Filter For Custom Query Based Portlet
    Best Answer

    Posted Mar 26, 2019 09:03 PM

    The Investment Type lookup is a Static Dependent List, and I don't think you can use it on a Query where I assume you are pulling back stage_code, and then attempting to define this with a lookup = Investment Type.

     

    I assume that you have also decoded stage_code as a string, so potentially you could be using the string in the filter, where users can then search for *iden for Idenitfy (as an example).

     

    If you do need the filter attribute to be a lookup, you may have to consider creating a Dynamic Lookup, based on:

    select name, code from cmn_lookups_v2 where lookup_type = '***' and landguage_code = 'en'

    where *** maps to the Stages for your environment that you have defined in Investment Type look-up.

    This new filter should then be able to be used in your Query.



  • 3.  Re: Using Project Stage As Filter For Custom Query Based Portlet

    Posted Mar 27, 2019 09:53 AM

    Thank you, Roland, I will give that a try.



  • 4.  Re: Using Project Stage As Filter For Custom Query Based Portlet

    Posted Mar 29, 2019 10:21 AM

    It worked.

    Thanks a lot, Roland!

    This is the code I used, in case anyone is interested:

     

    SELECT @SELECT:LK.ID:ID@,

           @SELECT:LK.LOOKUP_CODE:LOOKUP_CODE@,

           @SELECT:LK.PARENT_LOOKUP_CODE:PARENT_LOOKUP_CODE@,

           @SELECT:LK.PARENT_LOOKUP_CODE || '\\' || LK.NAME:PATH@,

           @SELECT:LK.LOOKUP_LEVEL:LOOKUP_LEVEL@,

           @SELECT:LK.NAME:NAME@,

           @SELECT:LK.DESCRIPTION:DESCRIPTION@,

           @SELECT:LK.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,

           @SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,

           @SELECT:LANG.ID:LANGUAGE_ID@

    FROM

           CMN_LOOKUPS_V LK, CMN_LANGUAGES LANG

    WHERE 

           LK.language_code = @WHERE:PARAM:LANGUAGE@

           AND LK.LOOKUP_TYPE='INV_STAGE_TYPE'

           AND LK.PARENT_LOOKUP_CODE = 'project'

           AND LK.IS_ACTIVE=1

           AND LANG.LANGUAGE_CODE=LK.LANGUAGE_CODE

           AND @FILTER@