Identifying table/field of the Look-up onjects

[Anon Anon]

Hello Everyone, I have a scenario where I need to identify the table and field of a lookup object. Can someone help me understand how objects with lookup data type can be identifed for the regular table and field. I need help to identify the look up objects field and table to complete my report.

[Anon Anon]

Hi,

There are a couple of answers to your question I think!

Firstly to need to examine the OBJECT in the Clarity studio in order to identify the lookup used against the object.

Then you need to look at the LOOKUP in the Clarity studio.

There are 2 main "types" - static lists and query-based.

For the static lists, then the values are in the CMN_LOOKUPS tables on the database, there is a convienient database view CMN_LOOKUPS_V that gets the data you'll need, so you go to that view with the lookup name, the value (from the object) and the language code you want and that will give you the lookup-value.

For the query-based lookups its a bit more complicated, since you would have to "replicate" the query lookup somehow - this will depend from lookup to lookup how complex that is. You just need to look at the NSQL definition of the lookup to see what you need. No easy answer there.

--

There are also the multi-level lookups (the static-dependent lists), but they tend to be a complex variant of the static lookup, so the same logic as above should work.

One other "sort-of lookup" that you might need is where an object has a multi-valued attribute - then you also need to go the ODF_MULTI_VALUED_LOOKUP table (that name isn't right, but its something like that!) since the object attribute value is not stored on the object itself, but in that other table.

Have fun!

[Anon Anon]

Thanks for the piece of information.

I tried looking the way you mention. But I am still not able to figure out the way to get the values for my look up field (Request Type).
I have a "Request Type" field (obj_request_type) which is coming from odf_ca_idea. The data type for it is lookup string coming from "Idea and Project Type" lookup. I looked this lookup table which has a general properties like- lookup Name, Lookup ID and my source is static in this scenario. I could check the values for it too.
Its static with multiple values/attribute. ( type100= project, type 200= application change and so on.so I assume its sort of lookup for multi attributes (as you mention).
As you mention, I would be able to get this information from CMN_LOOKUPS_V, I see all things (lookup id, look up code, look up name, language etc) but I don’t find Request type in it.
How to link these two things and get values for request type?
I am using this field on crystal reports. So I assume I have to pull this field coming from odf_ca_idea, as I don’t see the particular field in CMN_LOOKUPS_V Table.
If I am pulling lookup type instead of Request type on my report, I am not getting any data.I could get till here but not able to link this piece of information.

Right now, I am able to find the lookup table and know if it’s a static/dynamic, but not able to understand how these tables (odf_ca_idea and CMN_lookups_v) could be joined to get values for request type.
I am new to Clarity and need some advice/ procedural steps to continue from here.

Thanks

[Stephen Hogge]

There are a couple of ways of displaying this data ina report.

One, if the number of data attributes in your lookup is small, you can always do a decode on that attribute from the table instead of trying to do an additional join:

decode(odf_ca_idea.obj_request_type,'type100','Project','type200','Application')

And so on untill you have all the values. Thjs data is easy to pull from the lookup itself, with the value to decode being the value listed as ID in the static list, and the value to decode into being the value under Lookup Value or Description.

Two, if you would like to do the join with the CMN_LOOKUPS_V table, keep in mind the following:

The LOOKUP_TYPE in CMN_LOOKUPS_V should match the Lookup ID of the lookup.
The LOOKUP_CODE in CMN_LOOKUPS_V should match the value of the column in ODF_CA_IDEA.
The NAME in CMN_LOOKUPS_V should be the value you want to display in the report.
The LANGUAGE_CODE in CMN_LOOKUPS_V defines which language you will display.

So you should be able to use a query like the following the get the values you want:

select a.name
from cmn_lookups_v a,
odf_ca_idea b
where b.obj_request_type = a.lookup_code and
a.lookup_type = 'LOOKUP_ID' and
a.langauge_code = 'en'