Information on Modern UX Picklists from DB

Products

• Clarity PPM On Premise
• Clarity PPM SaaS

Issue/Introduction

• Is it possible to obtain the following information, about Picklists, of the Modern UX, from the DB?
  • Identify the workspace in which a particular Picklist is located
  • Which user created which particular Picklist etc.

Resolution

It is possible to get this type of information from the DB. A sample query is provided below.

Query:

select
    opdo.object_code,
    opd.name picklist_name,
    choice_data.name choice_name,
    sr.full_name created_by,
    sr.created_date
from
    odf_picklist_def_objects opdo
    join odf_picklist_defs opd on opd.id = opdo.picklist_def_id
    left join (
        select
            ccn.name,
            cl.lookup_type
        from
            cmn_lookups cl
            join cmn_captions_nls ccn on ccn.pk_id = cl.id
            and ccn.table_name = 'CMN_LOOKUPS'
            and ccn.language_code = 'en'
    ) choice_data on choice_data.lookup_type = opd.lookup_type
    join srm_resources sr on sr.user_id = opd.created_by
order by
    opdo.object_code,
    opd.created_date desc;