Clarity

  • 1.  Table linking attribute and corresponding lookup name

    Posted Apr 18, 2017 03:51 AM

    Hi, I want to know the DB tables that holds link between attribute in an object and if the data type of the attribute is lookup, how can i link the lookup name with the attribute name? I tried with tables CMN_CAPTIONS_NLS and CMN_LOOKUP_TYPES. I get the lookup name and attribute list. How could I extract both attribute name and lookup name?



  • 2.  Re: Table linking attribute and corresponding lookup name
    Best Answer

    Posted Apr 18, 2017 04:08 AM

    have a look at this thread ; has your answer I think and a few more besides...

     

    TIP : Documenting your Configuration (via SQL) 



  • 3.  Re: Table linking attribute and corresponding lookup name

    Posted Apr 18, 2017 04:35 AM

    Hi, 

     

    Im using a query to get object configuration from DB, this might help you.

     

    SELECT
    Distinct
    objdtl.Obj_id ID
    ,objdtl.ViewCode ViewCode
    ,objdtl.view_type view_type
    ,objdtl.Section Section_lb
    ,objdtl.Property_Label Property_Label
    ,objdtl.Column1 Column_nm
    ,objdtl.Attribute Attribute
    ,objdtl.Data_Type Data_Type
    ,objdtl.Display_Type Display_Type
    ,objdtl.Required Required
    ,objdtl.Read_Only1 Read_Only
    ,objdtl.Default1 Default_vl
    ,objdtl.Miscellaneous Miscellaneous
    ,objdtl.partition_code partition_code



    FROM
    (
    select
    (oo.id +ov.id )as "Obj_id"
    ,ov.partition_code
    ,ov.code AS "ViewCode"
    ,ov.view_type as "view_type"
    , ovs.label As "Section"
    , VAT.name AS "Property_Label"
    ,(Case when ova.col=1 then 'Left' else 'Right' end )As "Column1"
    ,(CASE WHEN CAT.name is null THEN attribute_code ELSE attribute_code+' ('+CAT.name+')' END) AS "Attribute"
    ,(case widget_type when 'text' then 'String' when 'textarea' then 'String' when 'browse' then 'Lookup' when 'select' then 'Lookup' when 'datepicker' then 'Date' when 'checkbox' then 'Boolean' else widget_type end) As "Data_Type"
    ,case widget_type when 'text' then 'Text Entry' when 'textarea' then 'Text Entry' when 'browse' then 'Browse' when 'select' then 'Pull-Down' when 'datepicker' then 'Date' when 'checkbox' then 'Check Box' else widget_type end As "Display_Type"
    ,case ova.is_required when '1' then 'Y' end As "Required"
    ,case ova.is_editable when '1' then '' when 0 then 'Y' end As "Read_Only1"
    ,CASE WHEN widget_type = 'checkbox'
    THEN case ova.default_value when 0 then 'Unchecked' else 'Checked' end
    ELSE case ova.default_value when 'date_today' then 'Today' when 'date_tomorrow' then 'Tomorrow' when 'userId' then 'Current User' when '' then ''
    else 'Value '''+ova.default_value+''' from lookup' end END AS "Default1"
    , isnull(case when lookup_type is null then '' else 'Lookup name '''+ISNULL((select C.name from cmn_captions_nls C , cmn_lookup_types L where L.lookup_type = OCA.lookup_type and C.pk_id = L.id and language_code = 'en' and table_name = 'CMN_LOOKUP_TYPES'), '')+' / '+ISNULL((lookup_type), '')+''' ' end, '')
    + isnull(case OCA.is_editable when 0 then 'Read-only "Checked" ' else null end, '')
    + isnull(case OCA.extended_type when 'lookup' then null else case OCA.data_type when 'string' then 'Maximum Size: '+isnull(CAST(data_size as CHAR(50)), '')+' ' else null end end, '')
    + isnull(case OCA.extended_type when 'lookup' then null else case OCA.data_type when 'number' then 'Decimal places: '+isnull(CAST(scale as CHAR(50)), '') else null end end, '') AS "Miscellaneous"
    FROM odf_views ov
    JOIN odf_objects oo on ( oo.code = ov.object_code )
    JOIN odf_view_attributes ova on ( ova.view_id = ov.id )
    LEFT JOIN cmn_captions_nls VAT ON ( ova.label_pk_id=VAT.pk_id and VAT.language_code = 'en'
    and VAT.table_name='ODF_VIEW_ATTRIBUTES' )
    LEFT JOIN odf_custom_attributes oca on ( oca.object_name = oo.code
    and oca.internal_name = ova.attribute_code )
    LEFT JOIN cmn_captions_nls CAT ON ( CAT.table_name = 'ODF_CUSTOM_ATTRIBUTES'
    and CAT.pk_id = oca.id and CAT.language_code = 'en' )
    LEFT JOIN odf_view_sections ovs on ( ovs.id=ova.section_id )
    WHERE ov.Object_code = @param_object_code
    and hidden != 1
    and section_id != -1


    ) objdtl



  • 4.  Re: Table linking attribute and corresponding lookup name

    Posted Apr 18, 2017 06:53 AM

    Thank you so much guys!