Clarity

Hello,

I want to create a lookup where it can show the all tasks/milestones under one project. The lookup type i set is "dynamic Niku Query", but i don't know who to fill in the "Query" tab under the lookup. Is there any sample codes to be shared?

Thank you!

Have a look at the existing system lookup BROWSE_PROJECT_TASKS

(that lookup would be used on a project page, to list all tasks belonging to that project ; it is picking the project id up from the URL of the page and then just displaying all the tasks for that project id. If this isn't what you need, it should at least give you a starting idea about what you need to code?)

Thanks David.

Indeed, i created an new attribute to "change request" object with data type as lookup-BROWSE_PROJECT_TASKS.

But when i go to the project's change request list, open the change request, then click the attribute i added, but no any content shown even if i did "show all".  don't know why. thx.

I was really just suggesting you look at that lookup as an example to see the sort of dynamic query you might need to use.

But looking at that NSQL, it seems the lookup is actually trying to display subprojects not tasks - suggest you try with a copy of that but with the NSQL with the last line;

task.pristask = 1

instead.

Also, I seem to recall problems with that sort of lookup and using browse lookups ; try swapping it to a pulldown?

--

However, I would probably use a parameterised dynamic-lookup myself for this sort of problem  (passing in the object id into the NSQL to drive the query, rather than referencing the URL) - so sorry for the slightly misleading advice initially, but I was having to GUESS a bit at your use-case

In that case the NSQL would look like this;

SELECT @SELECT:task.prid:prid@,
@SELECT:task.PRUID:UNIQUE_CODE@,
@SELECT:task.prexternalid:prexternalid@,
@SELECT:task.prname:prname@
FROM prtask task
WHERE @FILTER@
AND task.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:project_id@
AND task.pristask = 1

and where you create the attribute in the CHANGE REQUEST object you need to map the project_id to pk_id in the 'Lookup Parameter Mappings' section that you should see.

Sorry David, i am a new guy on this domain.

Here is the lookup i set:

Finally, it still show nothing.

Thank you so much!

OK your setup looks right ; are you sure that that project has tasks?

(and if you are really looking for milestones then prismilestone = 1 would be a better thing to filter for)

Hi, David

Yes, there are bunches of tasks inside this project.

That makes me confused.

I am not sure if the SQL looked for the right table or not. Is there any simple way that i can check the table and data inside? Thank you!

Have the ERD for the reference https://docops.ca.com/ca-ppm/15-2/en/reference/ca-ppm-entity-relationship-diagrams-erd 

Weird then - everything looks OK. 

If you have access to the database;

SELECT * from PRTASK 

WHERE prprojectid = the internal id for the project you should be able to see this in the URL of the project page

If you don't have direct access to the database, you can post that ^ SQL into a new lookup and "preview" the lookup - this is a sneaky trick to run SQL 'select' statements from within the application

Thx, David! Looks one more step moved.

If i use the simple SQL like

SELECT * from PRTASK

WHERE prprojectid = 5046009

Then i can get the task list as i want.

So don't know what issues about  the following sql, thank you!

SELECT @SELECT:task.prid:prid@,
@SELECT:task.PRUID:UNIQUE_CODE@,
@SELECT:task.prexternalid:prexternalid@,
@SELECT:task.prname:prname@
FROM prtask task
WHERE @FILTER@
AND task.prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:project_id@
AND task.pristask = 1

I have no idea why the

SELECT * from PRTASK

WHERE prprojectid = @WHERE:PARAM:USER_DEF:INTEGER:project_id@

would not work.

Anyway, thank you David! Now i got the tips and can run the SQL in preview mode inside CA PPM system. :-)

Try the below lookup query -

SELECT @SELECT:task.prid:prid@,
         @SELECT:task.PRUID:UNIQUE_CODE@,
         @SELECT:task.prexternalid:prexternalid@,
   @SELECT:task.prname:prname@,
         @SELECT:task.LAST_UPDATED_DATE:LAST_UPDATED_DATE@
 FROM prtask task
inner join inv_investments i on i.id=task.prprojectid

 WHERE @FILTER@
 AND (@WHERE:PARAM:USER_DEF:STRING:refid@ is null or i.code = @WHERE:PARAM:USER_DEF:STRING:refid@)

In the change request object attribute map the refid to pk_unique_name

Hope this works!

Hello Gayathri, it is working perfectly as you describe, that's exact what i want!

Thank you team all!