Mapping a dynamic lookup?

[Legacy User]

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:

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

[David Morton]

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).