AnsweredAssumed Answered

NSQL lookup browse filter on subobject returning too many rows

Question asked by sam_s on Jun 21, 2012
Latest reply on Jun 22, 2012 by sam_s
Hi everyone,

I have a requirement to create a single-select browse filter on the
created_by attribute on a custom subobject. The subobject has project as
main object.

Since the created_by attribute is not a browse, I have created another
created_by_filter attribute with an NSQL dynamic lookup for the filter and simple processes
that load this created_by_filter attribute whenever a record is
created or updated. The problem is I am unable to find a way to limit the
filter to only those records on the current subobject list page. The filter
brings back all records in the subobject table, some which come from other
projects. I have tried to add a url parameter to the NSQL query to limit the
records to those related only to the sub-object however it does not seem to
work.


This NSQL works on the lookup but brings back all of the created_by records
for the sub-object from every project:

SELECT @SELECT:links.created_by:created_by@,
@SELECT:resources.first_name:first_name@,
@SELECT:resources.last_name:last_name@,
@SELECT:resources.full_name:full_name@
FROM srm_resources resources, odf_ca_project_doc_links links
WHERE links.created_by = resources.user_id
AND @FILTER@
group by links.created_by, resources.first_name, resources.last_name,
resources.full_name

The url for the list page of the subobject on one project '(xxxx1234' is altered):

https://xxxx1234-test.ondemand.ca.com/niku/nu#action:projmgr.projectProperties
&odf_view=projectCreate.subObjList.project_doc_links&id=5021407&odf_pk=5021407
&parentObjectCode=project&odf_concrete_parent_object_code=project&odf_parent_i
d=5021407&odf_cncrt_parent_id=5021407&classCode=project


When I try to limit the NSQL to only subobject rows on the current subobject
by adding the parameter, it results in no data returned even though the
parameter exists in the url:

SELECT @SELECT:links.created_by:created_by@,
@SELECT:resources.first_name:first_name@,
@SELECT:resources.last_name:last_name@,
@SELECT:resources.full_name:full_name@
FROM srm_resources resources, odf_ca_project_doc_links links
WHERE links.odf_parent_id =
@where:param:xml:integer:/data/odf_parent_id/@value@
AND @FILTER@
and links.created_by = resources.user_id
group by links.created_by, resources.first_name, resources.last_name,
resources.full_name


this sql brings back the correct records of data in Oracle:

SELECT links.created_by,
resources.first_name,
resources.last_name,
resources.full_name,
FROM odf_ca_project_doc_links links, srm_resources resources
WHERE resources.user_id = links.created_by
AND links.odf_parent_id = 5021407

Any assistance would be appreciated.

Thanks,
Sam

Outcomes