Clarity PPM1

Expand all | Collapse all

Mapping a dynamic lookup?

Jump to Best Answer
  • 1.  Mapping a dynamic lookup?

    Posted 06-14-2016 08:02 AM

    I have created a dynamic lookup that shows some of project status depending on an object attribut.

    is there any way to map my lookup result in which to:

    instead of storing the lookup result into the base table, store the values mapped on my lookup result.

    For example:

    2016-06-14_115420.png

    I need to store (1,2,3) instead of "En cours","Suspendu","Non débuté"

    This is my query:

     

      
    
    select @SELECT:aa.aaa:date_mep@ from(
    SELECT   ccn.name,date_mep_pro_reel dater,
    
    case  when odfp.date_mep_pro_reel is null and ccn.NAME='Abondonné' then  'Abondonné'
    when odfp.date_mep_pro_reel is  null and ccn.NAME='Terminé' then  'Terminé'
    when odfp.date_mep_pro_reel  is null and ccn.NAME='En cours' then  'En cours'
    when odfp.date_mep_pro_reel is  null and ccn.NAME='Non débuté' then  'Non débuté'
    when odfp.date_mep_pro_reel is  null and ccn.NAME='Suspendu' then  'Suspendu'
    when odfp.date_mep_pro_reel is not null and ccn.NAME='En cours' then  'En cours'
    when odfp.date_mep_pro_reel is not null and ccn.NAME='Non débuté' then  'Non débuté'
    when odfp.date_mep_pro_reel is not null and ccn.NAME='Suspendu' then  'Suspendu'
    
    end as aaa
       FROM CMN_LOOKUPS CL, CMN_LOOKUP_TYPES CLT, CMN_CAPTIONS_NLS CCN,odf_ca_project odfp,INV_INVESTMENTS inv
     WHERE CL.LOOKUP_TYPE = CLT.LOOKUP_TYPE
     and inv.id = odfp.id
    and odfp.ID=@where:param:xml:string:/data/id/@value@
       AND CL.ID = CCN.PK_ID
       AND CCN.LANGUAGE_CODE = 'fr'
       AND upper(CLT.LOOKUP_TYPE) = 'PAC_RPT_PROJSTATUS'
       AND CCN.TABLE_NAME = 'CMN_LOOKUPS'
    )aa WHERE aaa is not null and @filter@
    

     

    Regards



  • 2.  Re: Mapping a dynamic lookup?
    Best Answer

    Posted 06-14-2016 08:28 AM

    In the NSQL that defines your lookup, you just return more than one 'column' (your NSQL currently only returns one 'column' ; date_mep ) - the lookup definition will then give you the options of what 'Hidden Key' you want to use (the value actually stored on the database) and what 'Display Attribute' (the value that is displayed to the user).