CA Service Management

Expand all | Collapse all

Query to get all fields and their values for a form

Jump to Best Answer
  • 1.  Query to get all fields and their values for a form

    Posted 01-15-2019 11:34 PM

    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



  • 2.  Re: Query to get all fields and their values for a form
    Best Answer

    Posted 01-16-2019 02:36 AM

    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
    ============================================================================



  • 3.  Re: Query to get all fields and their values for a form

    Posted 01-16-2019 03:26 AM

    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.



  • 4.  Re: Query to get all fields and their values for a form

    Posted 01-19-2019 01:53 PM

    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..



  • 5.  Re: Query to get all fields and their values for a form

    Posted 01-16-2019 03:59 AM

    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.