AnsweredAssumed Answered

Lookup dependant on custom attribute on parent object

Question asked by Plidian on Sep 26, 2014
Latest reply on Oct 3, 2014 by Kathryn_Ellis

So I've gotten stuck and I'm hoping someone else has done something like this before.  I have a requirement to create a required attribute on the task object that is a different set of values based on the value of a custom attribute on the project object.

My first attempt at this did the following.

1.     Create an object associated lookup that is associated with the task object.

2.     In the lookup I created a variable

3.     Created a lookup attribute on the task object

4.     Associated the variable with the Project id attribute on the task object

 

Select 
@SELECT:vaalues:VAALUES@
from 
(
  (
  Select 
  case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Project Management, Monitoring and Control' else null end  a
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Plan' else null end b
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Requirements' else null end as c
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh') then 'Strategy' else null end as d
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Design' else null end as e
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Build' else null end as f
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Test' else null end as g
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','type800','glic_other_non_dsc','glic_upg_mig_ref') then 'Deploy' else null end as h
, case when proj_type in ('glic_op_excellence','Application Administration','glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','glic_reg_compliance','type800','glic_other_non_dsc','type700','glic_reg_compliance','glic_upg_mig_ref') then 'Closure' else null end as i
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','glic_reg_compliance','type800','glic_other_non_dsc','type700','glic_reg_compliance','glic_upg_mig_ref') then 'Database Administration' else null end as j
, case when proj_type in ('glic_innovation','glic_cap_disc_growth','glic_investment','glic_op_excellence','glic_strategy','glic_upg_mig_refresh','glic_cap_non_disc_growth','glic_reg_compliance','type800','glic_other_non_dsc','type700','glic_reg_compliance','glic_upg_mig_ref') then 'System Administration' else null end as k
, case when proj_type in ('glic_reg_compliance','glic_reg_compliance') then 'Audit' else null end as l
, case when proj_type in ('type1200') then 'Business Administration' else null end as m
, case when proj_type in ('type1200') then 'Non-Project Meetings' else null end as n
, case when proj_type in ('type1200') then 'Non-Work Time' else null end as o
, case when proj_type in ('glic_investment','glic_upg_mig_refresh','type1200','glic_upg_mig_ref') then 'Training' else null end as p
, case when proj_type in ('type1200') then 'Travel' else null end as q
, case when proj_type in ('glic_reg_compliance','type700','glic_reg_compliance') then 'Break-Fix' else null end as r
, case when proj_type in ('glic_reg_compliance','glic_other_non_dsc','glic_reg_compliance') then 'Configuration Management' else null end as s
, case when proj_type in ('glic_cap_non_disc_growth','glic_reg_compliance','type800','glic_other_non_dsc','type700','glic_reg_compliance','glic_upg_mig_ref') then 'Minor Enhancement' else null end as t
, case when proj_type in ('glic_cap_non_disc_growth','glic_reg_compliance','type800','glic_other_non_dsc','type700','glic_reg_compliance','glic_upg_mig_ref') then 'Monitor' else null end as u
, case when proj_type in ('glic_reg_compliance','glic_other_non_dsc','glic_reg_compliance','glic_service_delivery') then 'Service Delivery' else null end as v

from 
  (
   Select obj_request_type proj_type
   FROM odf_ca_project ocp
   join inv_investments i on i.id=ocp.id
   where i.code=@WHERE:PARAM:USER_DEF:STRING:PROJECT_CODE@
   )
  )
unpivot
  (
  vaalues
  for vaalues_type in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v)
   ) 
)

task_attrib.png

This worked, with one important caveat. Because of the way that Clarity handles the object reference you can't create a new task anymore.  Since the lookup requires the record to exist before it can make the association.

Is there a way around this?

Is there a NSQL construct that can return the taskID, projectID or other unique identifier without requiring the task to exist first?

Outcomes