Using Department OBS as a parameter

[Benoit Dorion]

I am creating a lookup query that should display a specific list of results depending on the value the user has selected in the Department OBS field.

It looks like the Department OBS attribute is a virtual attribute. Because of this, is it even possible to refer to the Department OBS field as parameter in a lookup query?

Also, how can I find out the value of the Department OBS field if it is virtual. Normally I would populate it with the value I'm looking for, save it and run a SQL query to find out the value that is saved in the database. That's not really an option with an attribute that doesn't exist in the DB. 

[Debroop Dasgupta]

Hello Benoit, 

I have never tried creating a parameterized lookup with an OBS. I will defer to the more technically skilled folks to answer that question. However, you may try pulling in the Department attribute (the value mirrors the Department OBS value without the full path) and then create a parameterized lookup on this. 

The Project's Department can be located in the PAC_MNT_PROJECTS table under the DEPARTCODE column. 

Hope this helps. 

Thanks,
Regards,
Debroop

[Martti Kinnunen]

The department OBS is an OBS and you can build a query to get the units.
For example the query below gets the department OBS unit associated with a project
in a system where the name of the department OBS is *Corporate Department OBS'
as seen in Administration - OBS
You can further develop that to be used for a dynamic lookup and further that for a portlet parameter and also for other objects

select

inv_investments.id,
inv_investments.code,
inv_investments.name Project,
prj_obs_types.id Type_id,
PRJ_obs_types.unique_name Type_unique,
PRJ_obs_types.name OBS_type_name,
prj_obs_units.ID OBS_unit_ID,
prj_obs_units.Unique_name OBS_unit_unique,
prj_obs_units.name OBS_unit_name

from
inv_investments,
prj_obs_units,
prj_obs_associations,
prj_obs_types

where
prj_obs_associations.record_id=inv_investments.id
AND prj_obs_associations.UNIT_ID=prj_obs_units.id
and prj_obs_associations.table_name= 'SRM_PROJECTS'
and prj_obs_types.id=prj_obs_units.type_id
and inv_investments.ODF_OBJECT_CODE = 'project'
and PRJ_obs_types.name = 'Corporate Department OBS'

Order by OBS_unit_ID desc, inv_investments.name