ProjectID parameter

[Legacy User]

It seems that is a dummy question, I'm new at Clarity PPM Plateforme,

I have a Lookup Query to make and i need to change the project id dynamically in this query like this:

 

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 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:USER_DEF:INTEGER:PROJECTID@
   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@

 

 

and "odfp.ID=@WHERE:PARAM:USER_DEF:INTEGER:PROJECTID@"  Doesn't look like it works, any help?

 

Regards,

Amine

[Sridhar Bandaru]

When you associate an attribute to this lookup(parameterized), you will see a mapping section asking you to map your parameter(as you defined in NSQL) to an attribute of Object. In this case, I think you should map it to Object ID.

[Anon Anon]

Hi,

 

I have same challenge with a lookup which needs to be driven by the project. My lookup is used in a portlet filter (on the project pages) to select an existing baseline of that particular project.

 

The query for the lookup is:

SELECT @SELECT:ID:ID@,

       @SELECT:NAME:NAME@,

       @SELECT:CODE:CODE@,

       @SELECT:DESCRIPTION:DESCRIPTION@,

       @SELECT:IS_CURRENT:IS_CURRENT@,

       @SELECT:PROJECT_ID:PROJECT_ID@

FROM prj_baselines

WHERE object_type = 'PROJECT'

AND project_id = @where:param:xml:integer:/data/id/@value@

AND @FILTER@

 

I don't know where the mapping is done. I don't see that option either in the lookup or in the portly filter.

Anyone?

 

BR/Tonny

[David Morton]

This line;

 

AND project_id = @where:param:xml:integer:/data/id/@value@

 

attempts to picks up the "id" from the URL of the page that you are on - that would work OK if the NSQL was driving a query/portlet, but you have to be careful using it for NSQL lookups ; I'm not sure if the lookup can pick up the URL value at all (it might be able to for drop-down lookups not browse ones)

 

( Parametrised lookups aren't any use when they are used in portlets, only when they are used against objects.)

[sankhadeep.dhar.2]

Hello Calemirou,

 

I am assuming that you want to use the lookup in a project attribute.

What you can do is create a parametrized lookup. The idea is that the you will pass the project's internal id to the lookup's parameter. Since the project's internal ID is not readily available as an attribute, create a custom investment attribute which will be updated to the internal id when a project is created (use workflows). For existing projects, do it as one-time data update.

 

Now, create the attribute and associate the lookup to the attribute. Since you are using a parameterized lookup, you will be able to map the custom attribute (holding the project internal id) to the lookup's parameter.

 

-Sankhadeep

[David Morton]

sankhadeep.dhar.2 wrote:

 

Since the project's internal ID is not readily available as an attribute, create a custom investment attribute which will be updated to the internal id when a project is created (use workflows). For existing projects, do it as one-time data update.

 

Ummmmm you don't need to do any of that, just use the 'Object ID' in the mapping like Sridhar stated.

[sankhadeep.dhar.2]

Ahh, you are right