Query to fetch the templates
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", rt.templatename "Template name" , sd.name "Service Catalog Name", (CASE when rt.is_catalog_template='true' then 'Service Template' else 'Incident Template' END) "Template Type" 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)
left join columnaliases ca on fc.field_name like ('udf_%_' || ca.columnaliasesid)
where rt.templatename not like 'Default Service Item'
order by 2
MSSQL:
select Distinct (CASE when fc.field_name like 'udf_%' then ca.aliasname else fc.field_name END) "Fields", rt.templatename "Template name" , sd.name "Service Catalog Name", (CASE when rt.is_catalog_template='true' then 'Service Template' else 'Incident Template' END) "Template Type" 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)
left join columnaliases ca on fc.field_name like ('udf_%_' + cast(ca.columnaliasesid as varchar))
where rt.templatename not like 'Default Service Item'
order by 2
New to ADSelfService Plus?
Related Articles
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 ...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", ...Query to fetch additional fields configured in request templates
Postgres: SELECT ca.aliasname AS "UDF field Name", ca.columnname AS "UDF Column Name", ca.field_type AS "UDF Field Type", sd.name AS "Service Category", rtl.templatename AS "Template Name" FROM columnaliases ca LEFT JOIN form_customizer fc ON ...Query to show tasks created per template along with status (MSSQL & PGSQL)
Tested in builds from PGSQL (14300) or MSSQL (14306) Since Task template is not stored in Task details, we cannot show count based on task templates. However, we have tried to form a query with task subject considering that, when task templates are ...Hide templates from drop-down while using a specific template using FAFR
The below script can be used in the respective incident template under Form Load section of Field and Form Rules. if(["Application Crashes Frequently","New incident"].indexOf(jQuery("select#template_box").select2("data").text)==-1){ ...