We built a parameterized lookup for a project field called "Funding AU" that displays list of matching values from a custom object based on the project "work type" (a custom field). The lookup is working fine on project. It also works fine in the filter section of project list page provided a work type value is selected then the Funding AU is selected.
But the issue arises when we try to build a power filter. It is not displaying any values for Funding AU field since technically, it is unable to read the parameter for work type ... I am handling a null value but that is not helping either. Any thoughts or ideas?
Try#1: Lookup query where parameter is in main bodySELECT DISTINCT
@SELECT:CODE:CODE@
,@SELECT:NAME:NAME@
,@SELECT:LAST_UPDATED_DATE:LAST_UPDATED_DATE@
FROM (select code, name, wf_funding_au_type, wf_active, last_updated_date
from odf_ca_wf_funding_au
WHERE wf_funding_au_type in ('P','C')
AND (@WHERE:PARAM:USER_DEF:STRING:WF_WORK_TYPE@ in ('wf_project', 'wf_program') or @WHERE:PARAM:USER_DEF:STRING:WF_WORK_TYPE@ is null)
UNION All
SELECT code, name, wf_funding_au_Type, wf_active, last_updated_date
FROM odf_ca_wf_funding_au
WHERE wf_funding_au_type in ('NP','C')
AND (@WHERE:PARAM:USER_DEF:STRING:WF_WORK_TYPE@ = 'wf_non_project' or @WHERE:PARAM:USER_DEF:STRING:WF_WORK_TYPE@ is null)
)
WHERE 1=1
and @FILTER@
@BROWSE-ONLY:
AND WF_ACTIVE = 1
AND CODE NOT IN ('TBD','N/A')
:BROWSE-ONLY@