Query to display additional field associations with templates (MSSQL)

Query to display additional field associations with templates (MSSQL)

Tested in build MSSQL (14306)


Use case

The reports shows in which templates the created additional fields are associated 

Query

select sd.name "Service Catalog Name",
           rt.templatename "Template name",
           fc.field_name "Fields" from requesttemplate_list rt
left join servicedefinition sd on sd.serviceid = rt.parent_service
left join form_customizer fc on fc.form_name = ('WorkOrder_' + rt.templatename)
where fc.index_tech >= 0 and fc.field_name='udf_sline_901'
order by 1

Go to Admin >> Service Catalog - Additional Fields/Incident - Additional Fields >> Note the API name for the additional field and replace it with the highlighted text in the query




Sample Table



                  New to ADSelfService Plus?

                    • Related Articles

                    • How to populate requester details in request additional field

                      Requirement: Populate requester additional field information into request additional field. Usecase: Let us consider a scenario where certain employee details are imported from the Active Directory into User - Additional Fields. The business ...
                    • Additional field values

                      This report helps to find the picklist values in the additional fields. First you need to find the name of the table and the column holding the data, by executing the following SDP query report:  SELECT Tablename, Columnname FROM ColumnAliases WHERE ...
                    • Query to fetch the fields in the templates (MSSQL & PGSQL)

                      Tested in builds from PGSQL (14300) or MSSQL (14306) The query fetches all the fields with display name used across Request templates PQSQL: select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", ...
                    • Request with ServiceCatalog Additional fields

                      Service catalog additional fields are category specific fields. These fields are stored in a dynamic table, so we need to join the specific template to get the resultant.  To make any changes to a query, refer to the KB article below. ...
                    • How to populate user names in a request additional field

                      This sample script is to populate both technician and requester names in a request additional field Use Case: Populate all user name in a request additional field. Execution Steps:  1.  Create a new 'Single Line' additional field and add them in the ...