Which MDB table holds the information for hard-coded select controls in Catalog forms?

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

If a select control has hard coded values, these values are stored in the MDB. This document will enable you to get this information from the MDB.

Environment

Service Catalog 14.1 and higher

Resolution

The MDB table which holds this information is usm_form_entities. The form is called "TestForm". It has a single select control in it called "SelectTest" and that select control had three options added to it, "TestOptionOne", "TestOptionTwo" and "TestOptionThree". Each of these three options had the "value" parameter set to "ValueOne", "ValueTwo" and "ValueThree".

In the usm_form_entities table,we need to look at the form_entity_id, form_entity_parent_id and form_entity_type fields.

form_entity_id - ID of the form entity.
form_entity_parent_id - ID of the parent form entity.
form_entity_type - The type of form entity:

2 - Form
9 - Select control
11 - Select control option

To find the id of the form, use the form name:

SELECT * from usm_form_entities WHERE form_entity_name = 'TestForm'

You will get a result such as this:

form_entity_id form_entity_name form_entity_type 
14594          TestForm         2

To find the select control, we use the form_entity_id of the form and the form_entity_type of 9.

SELECT * from usm_form_entities WHERE form_entity_parent_id = 14594 AND form_entity_type = 9

A result will appear such as this:

form_entity_id form_entity_name form_entity_type 
14595          SelectTest       9

To find the options, we use the id of the select control as the form_entity_parent again.

SELECT * from usm_form_entities WHERE form_entity_parent_id = 14595

This can be done in one SQL statement:

SELECT * FROM usm_form_entities WHERE form_entity_parent_id IN 
(SELECT form_entity_id FROM usm_form_entities WHERE form_entity_parent_id IN 
(SELECT form_entity_id FROM usm_form_entities WHERE form_entity_name = 'TestForm'))

form_entity_id form_entity_name form_entity_type
14596          TestOptionThree  11 
14597          TestOptionTwo    11 
14598          TestOptionOne    11

If you want to find the values which are defined for the options, you can use the form_entity_id values from the previous SQL statement as a lookup into the usm_form_component_attributes MDB table:

SELECT * FROM usm_form_component_attributes WHERE form_comp_id IN (14596, 145967,14598) 

form_comp_attr_id form_comp_id attr_locale   attr_name   attr_value  attr_type 
596850            14596        _system       value       ValueThree  1 
596851            14597        _system       value       ValueTwo    1 
596852            14598        _system       value       ValueOne    1