CA Service Management

Dear team,

We are using Service Catalog 14.1.

We have a requirement wherein we are asked to provide all the fields in a form from Form Designer. xml export of the form doesn't work as they need the data in plain text like Field names and if there is a drop down, then values of the drop down. 

Please suggest if there is any query we can use to extract the same from database.

Thank you,

Divya

Good Morning Divya.

Please consider the below queries. And see whether one of these helps you further.
Of course these can be used as 'starting point' for you and be tweaked per your wishes.

Kind regards, Louis.

/* Query the form components for the offering, copy value of column text_1 into: */
select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
from usm_form_entities fe, usm_form_component_attributes fca
where fca.form_comp_id in (
select distinct rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name = 'Order new Team Site'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14)
and fca.form_comp_id = fe.form_entity_id
==============================================
/* Root form components of an offering */
select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
from usm_form_entities fe, usm_form_component_attributes fca
where fca.form_comp_id in (
select distinct rd.text_1
from usm_offering o,usm_offering_ratedef_inclusion i,usm_rate_definition rd
where o.offering_name = 'Order new Team Site'
and i.parent_id = o.offering_id
and rd.rate_plan_id = i.rate_plan_id
and rd.item_type = 14)
and fca.form_comp_id = fe.form_entity_id
============================================================================
/* Recursive query to fetch all component hierarchy of a form or folder */
WITH Form (form_entity_id, form_entity_name, form_entity_parent_id, Hir)
AS
(
-- Anchor member definition
SELECT rt.form_entity_id, rt.form_entity_name, rt.form_entity_parent_id, 0 AS Hir
FROM usm_form_entities rt
WHERE rt.form_entity_name = 'Forms'
UNION ALL
-- Recursive member definition
SELECT rec.form_entity_id, rec.form_entity_name, rec.form_entity_parent_id, Hir + 1
FROM usm_form_entities rec
INNER JOIN Form AS Par ON Par.form_entity_id = rec.form_entity_parent_id
)
-- Statement that executes the CTE
SELECT form_entity_id, form_entity_name, form_entity_parent_id, Hir
FROM Form
order by Hir
============================================================================
/* Query1: When you know the name of the form, you can execute: */
select form_entity_id,form_entity_type,form_entity_name,form_entity_path from USM_FORM_ENTITIES
where form_entity_name = 'A_Components_Form'
/* Example form_entity_path = /1/21942/ */

/* And take the form_entity_path from the result into the next query:
Query2: */
select form_entity_id,form_entity_type,form_entity_name,form_entity_path from USM_FORM_ENTITIES
where form_entity_path like '%/1/21942/%'

/* Query3: */
select fe.form_entity_id,fe.form_entity_name,fe.form_entity_type,fe.form_entity_path,fca.form_comp_id,fca.attr_name,attr_value
from usm_form_entities fe, usm_form_component_attributes fca
where fe.form_entity_path like '%/1/21942/%'
and fca.form_comp_id = fe.form_entity_id
============================================================================

Good Morning Divya.

Should you want to know what the values have been entered on a form, while(after) creating a request (for an offering/service option with that form), then you need a different query.

Let me know, Louis.

No Louis, I was looking for a query to export the form details only. Not for the values after creating a request.. Let me explore the queries you have shared above.

Thanks a lot for always helping me..

Good Morning Divya.

-- For already created/existing requests:
-- First it is to retrieve the subscription_detail_id of the request:
SELECT * FROM usm_subscription_detail
where request_id=10086 and subscription_type=5

-- From the result use the value of the column named 'id' in the next query:
SELECT subscription_detail_id,form_elem_name,form_elem_value,is_visible
FROM usm_request_item_form
where subscription_detail_id=11197
-- This should show all fields(form_elem_name) and their value.

Kind regards, Louis.