Difference between revisions of "Mapping Fields from Customised Forms"

From Hornbill
Jump to navigation Jump to search
(21 intermediate revisions by 7 users not shown)
Line 7: Line 7:
 
== Introduction ==
 
== Introduction ==
  
−
When creating customised forms, and custom questions the default behaviour is for the question and answer to be visible on the request form in a '''Questions''' collapsable section. It is also possible to map the answer from a custom question to one of the default fields on a request.  
+
Any information captured in customised forms results in the question and answer to be visible on the request form in a '''Questions''' collapsable section. Custom forms also support field mapping which is a mechanism that allows us to write the information to the main request table.
 +
 
 +
===Why would I use field mapping?===
 +
 
 +
Field mapping is desirable for two reasons:
 +
:# to make the answers to custom questions more accessible for reporting (i.e. Measures, Widgets, Reports).
 +
:# to make the answers to custom questions available in email templates.
 +
 
 +
The information gathered in a progressive capture custom form is stored in a table h_itsm_questions and is intended to act as a sort of audit history displaying what information was provided at the point the request was logged. Answers in the Questions section of a request can be edited. This option is only available for single-line and multi-line text fields and only for users having "Service Manager Delete Questions" or "Service Desk Admin" roles. Any other type of answers cannot be edited.
 +
<br>
 +
 
 +
The way the data is stored in the table h_itsm_questions does not offer us much flexibility when it comes to reporting and also means is not accessible via the email template variable picker. The exercise of field mapping ensures the data is written to h_itsm_requests (the main request table) which means it is readily available.
 +
 
 
|style="width:5%"|
 
|style="width:5%"|
 
|
 
|
Line 13: Line 25:
  
 
== Related Articles ==
 
== Related Articles ==
−
:* [[Progressive_Capture_Designer#Mapping_Fields_from_Customised_Forms| Mapping fields from custom forms]]
+
:* [[Customised_Forms|Customised forms]]
 +
:* [[Request_Details_Form_Designer|Request Details Form Designer]]
 
|}
 
|}
  
−
== Field ID ==
+
== Configuring a Custom Field for Mapping ==
−
When creating a custom question on a customised form, each question will require a Field ID to be specified, it is here that the mapping can be performed. When defining the Field ID, if you use any of the following values, the answer to this question will be mapped to the field you have specified.  
+
When creating a custom question on a customised form, each question requires a Field ID and it is here that the mapping can be performed. When defining the Field ID, if you use any of the following values, the answer to this question will be mapped to the database column you have specified.
 +
 
 +
[[File:Mapping_Custom_Fields.png|400px|thumb|<div align="center">'''Field mapping takes place when a supported database column name is specified as the field id of a custom field.'''</div>]]
 +
 
 +
===Which Database Columns Support Field Mapping?===
 +
 
 +
To map a custom form answer to one of the supported database columns, the form field ID need to be set using the following format '''h_custom_a''' or appropriate column you would like to map to. The table below lists all the database columns that support field mapping and the type of content that they can hold.
 +
<br>
 +
{| class="wikitable" width="70%" style="text-align: left"
 +
 
 +
! width = "180px" | Available Columns
 +
! width = "220px" | Data Type/Capacity
 +
! width = "600px%" | Description
 +
 
 +
|-
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_summary
 +
|                            <!-- Data Type/Capacity --> VARCHAR/255 characters
 +
|                            <!-- Description        --> The Summary field of a request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_description
 +
|                            <!-- Data Type/Capacity --> TEXT/unlimited*
 +
|                            <!-- Description        --> The description field of a request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_site
 +
|                            <!-- Data Type/Capacity --> VARCHAR/64 characters
 +
|                            <!-- Description        --> The site field for a request
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_custom_a - h_custom_o
 +
|                            <!-- Data Type/Capacity --> VARCHAR/255 characters
 +
|                            <!-- Description        --> ''VARCHAR'' custom fields a - o each suitable for holding up to 255 characters of any type.
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_custom_p - h_custom_t
 +
|                            <!-- Data Type/Capacity --> TEXT/unlimited*
 +
|                            <!-- Description        --> ''TEXT'' custom fields p - t each suitable for holding large amounts of text characters of any type.
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_custom_21 - h_custom_25
 +
|                            <!-- Data Type/Capacity --> DATETIME/a single date-time stamp
 +
|                            <!-- Description        --> ''DATETIME'' custom fields 21 - 25 each suitable for holding a single date-time stamp (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker.
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_custom_26 - h_custom_30
 +
|                            <!-- Data Type/Capacity --> INTEGER/any whole number
 +
|                            <!-- Description        --> ''INTEGER'' custom fields 26 - 30 each suitable for holding whole numbers. When mapping to these columns, ensure the following RegEx is specified in your Custom field settings: ''[0-9]''
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_custom_31 - h_custom_40
 +
|                            <!-- Data Type/Capacity --> TEXT/unlimited*
 +
|                            <!-- Description        --> ''TEXT'' custom fields 31 - 40 each suitable for holding large amounts of text characters of any type.
 +
 
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_backout_plan
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Back Out Plan of a Change or Release Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_support_plan
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Support Plan  of a Change or Relase Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_communication_plan
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Communication Plan of a Change or Release Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_test_plan
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Test Plan of a Change or Release Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_implementation_plan
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Implementation Plan of a Change or Release Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_security_implication
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Security Implications of a Change or Release Request
  
−
[[File:Mapping_Custom_Fields.png|600px]]
+
|-
 +
| align = "left"            |<!-- Column Name  --> h_change_justification
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Change Justification a Change Request
  
−
===Available Fields to Map to===
+
|-
 +
| align = "left"            |<!-- Column Name  --> h_release_justification
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Change Justification a Release Request
  
−
* '''Summary''' - To map to this field the Field ID needs to be set as '''h_summary'''
+
|-
−
* '''Description''' - To map to this field the Field ID needs to be set as '''h_description'''
+
| align = "left"            |<!-- Column Name  --> h_release_plan
−
* '''Custom Fields a - p''' - To map to any of the custom fields the Field ID need to be set using the following format '''h_custom_a''' or appropriate for the custom field you would like to map too.
+
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        --> The Release Plan for a Release Request
  
−
When custom questions have been mapped to a default request field, the question and answer are still available in the '''Questions''' section on a request as well as the default fields mapped too.
+
|-
−
* This will allow for the original answer to be retained
+
| align = "left"            |<!-- Column Name  --> h_disruption_level
−
* The default fields values can be changed once logged
+
|                            <!-- Data Type/Capacity --> VarChar/255 Characters
 +
|                            <!-- Description        --> The Disruption Level of a Change or Release Request
  
−
===Considerations===
+
|-
 +
| align = "left"            |<!-- Column Name  --> h_change_category
 +
|                            <!-- Data Type/Capacity --> VarChar/100 Characters
 +
|                            <!-- Description        --> The Category of a Change Request
 +
 
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_release_category
 +
|                            <!-- Data Type/Capacity --> VarChar/100 Characters
 +
|                            <!-- Description        --> The Category of a Release Request
 +
|-
 +
| align = "left"            |<!-- Column Name  --> h_proposed_start_time
 +
|                            <!-- Data Type/Capacity --> DATETIME/a single date-time stamp
 +
|                            <!-- Description        -->  The Proposed Start date/time of the a Change or Release Request - (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker
  
−
* If the Default '''Request Details''' form is used in your progressive capture flow, and you try to map to h_summary or h_description, the values will not overwrite or be written to the summary or description fields, but the custom questions and answers will still be written to the '''Questions''' section on the request.
+
|-
−
* If the same mapping is used on different custom forms in your progressive capture flow, the first mapping will be written to the specified default field on the request, and any subsequent mapping to the same field will not overwrite this value, but will be written to the '''Questions''' section on the request.
+
| align = "left"            |<!-- Column Name  --> h_proposed_end_time
 +
|                            <!-- Data Type/Capacity --> DATETIME/a single date-time stamp
 +
|                            <!-- Description        -->  The Proposed End date / time of the a Change or Release Request - (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker
  
−
===Using Custom Questions and Answers in Email Templates & Measures===
+
|-
 +
| align = "left"            |<!-- Column Name  --> h_workaround
 +
|                            <!-- Data Type/Capacity --> Text/Unlimited*
 +
|                            <!-- Description        -->  The Workaround for a Problem or Known Error record
  
−
If you have a requirement to include the answers to custom questions in email templates within your business process, it is now possible to utilise the above mapping to write the answers to the custom questions into default fields which are immediately available to use as variables when creating your email templates.  
+
|}
 +
unlimited* - TEXT fields have a maximum capacity of 65000 characters.
 +
<br>
 +
<br>
  
−
By using the above mapping to any of the requests custom fields, when creating your email template, simply look for the '''Extended Information''' options available from the variables drop down pick list. They will look like this: '''<nowiki>{{Extended Information.H_custom_x}}</nowiki>'''.  The Extended Information options are listed as 1 through to 20 and these equate to the custom fields a through to p which you will have mapped to.  By selecting the required variable in your email template, this will pass through the answer from your custom question which has been mapped to the corresponding custom field.
+
===Considerations===
  
−
* '''Update -  From Service Manager 2.41''' mapped answers are also written to the custom columns in the primary h_itsm_requests table (custom a-p), this will allow the custom fields to be used when defining email template variables using the available custom fields, and also as criteria when creating measures in the Advanced Analytics.   This update only applies to data in mapped fields for requests logged after the release of Service Manager 2.41.  The mapped answers are still also mapped to the above Extended Information table.
+
* When specifying the column you wish to map to in your custom form id, '''this value is case sensitive'''. e.g. ''h_summary'' will acheive the desired mapping but ''h_'''S'''ummary'' will fail to pull any values through. Ensure that the mapped value is written in lower case.
 +
* When the option of ''value / display'' is offered (e.g. in a static drop-down list) it is the '''display''' value that will be mapped to the column.
 +
* Do not store Telephone Numbers in ''Integer'' fields. At best you will lose the initial ''''0'''', even more likely is that the value will exceed the maximum allowed for an Integer and the operation will fail.
 +
* If the Default '''Request Details''' form is used in your progressive capture flow, and you try to map to h_summary or h_description, the values will not overwrite or be written to the summary or description fields i.e. the standard "Request Details" form takes precedence, but the custom questions and answers will still be written to the '''Questions''' section on the request.
 +
* If the same mapping is used on different custom forms in your progressive capture flow, the first mapping will be written to the specified default field on the request, and any subsequent mapping to the same field will not overwrite this value, but will be written to the '''Questions''' section on the request.
 +
* When custom questions have been mapped, the question and answer are still available in the '''Questions''' section on a request as well as now being available in the mapped field. This restriction can be controlled via the application setting "guest.itsm.progressiveCapture.overwriteCustomFieldValue".
 +
* Once you have configured field mapping in your progressive capture custom forms, you may need to expose the mapped field on the request. See the [[Request_Details_Form_Designer|'''Request Details Form Designer''']] for more information.
 +
* When creating your email template, simply look for the '''custom''' options available from the variables drop down pick list. By selecting the required variable in your email template, this will pass through the answer from your custom question which has been mapped to the corresponding custom field.
  
 
[[Category:Administration]]
 
[[Category:Administration]]

Revision as of 17:04, 9 March 2022

Home > Administration > Progressive Capture Designer > Mapping Fields from Customised Forms

Introduction

Any information captured in customised forms results in the question and answer to be visible on the request form in a Questions collapsable section. Custom forms also support field mapping which is a mechanism that allows us to write the information to the main request table.

Why would I use field mapping?

Field mapping is desirable for two reasons:

  1. to make the answers to custom questions more accessible for reporting (i.e. Measures, Widgets, Reports).
  2. to make the answers to custom questions available in email templates.

The information gathered in a progressive capture custom form is stored in a table h_itsm_questions and is intended to act as a sort of audit history displaying what information was provided at the point the request was logged. Answers in the Questions section of a request can be edited. This option is only available for single-line and multi-line text fields and only for users having "Service Manager Delete Questions" or "Service Desk Admin" roles. Any other type of answers cannot be edited.

The way the data is stored in the table h_itsm_questions does not offer us much flexibility when it comes to reporting and also means is not accessible via the email template variable picker. The exercise of field mapping ensures the data is written to h_itsm_requests (the main request table) which means it is readily available.

Related Articles

Configuring a Custom Field for Mapping

When creating a custom question on a customised form, each question requires a Field ID and it is here that the mapping can be performed. When defining the Field ID, if you use any of the following values, the answer to this question will be mapped to the database column you have specified.

Field mapping takes place when a supported database column name is specified as the field id of a custom field.

Which Database Columns Support Field Mapping?

To map a custom form answer to one of the supported database columns, the form field ID need to be set using the following format h_custom_a or appropriate column you would like to map to. The table below lists all the database columns that support field mapping and the type of content that they can hold.

Available Columns Data Type/Capacity Description
h_summary VARCHAR/255 characters The Summary field of a request
h_description TEXT/unlimited* The description field of a request
h_site VARCHAR/64 characters The site field for a request
h_custom_a - h_custom_o VARCHAR/255 characters VARCHAR custom fields a - o each suitable for holding up to 255 characters of any type.
h_custom_p - h_custom_t TEXT/unlimited* TEXT custom fields p - t each suitable for holding large amounts of text characters of any type.
h_custom_21 - h_custom_25 DATETIME/a single date-time stamp DATETIME custom fields 21 - 25 each suitable for holding a single date-time stamp (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker.
h_custom_26 - h_custom_30 INTEGER/any whole number INTEGER custom fields 26 - 30 each suitable for holding whole numbers. When mapping to these columns, ensure the following RegEx is specified in your Custom field settings: [0-9]
h_custom_31 - h_custom_40 TEXT/unlimited* TEXT custom fields 31 - 40 each suitable for holding large amounts of text characters of any type.


h_backout_plan Text/Unlimited* The Back Out Plan of a Change or Release Request
h_support_plan Text/Unlimited* The Support Plan of a Change or Relase Request
h_communication_plan Text/Unlimited* The Communication Plan of a Change or Release Request
h_test_plan Text/Unlimited* The Test Plan of a Change or Release Request
h_implementation_plan Text/Unlimited* The Implementation Plan of a Change or Release Request
h_security_implication Text/Unlimited* The Security Implications of a Change or Release Request
h_change_justification Text/Unlimited* The Change Justification a Change Request
h_release_justification Text/Unlimited* The Change Justification a Release Request
h_release_plan Text/Unlimited* The Release Plan for a Release Request
h_disruption_level VarChar/255 Characters The Disruption Level of a Change or Release Request
h_change_category VarChar/100 Characters The Category of a Change Request
h_release_category VarChar/100 Characters The Category of a Release Request
h_proposed_start_time DATETIME/a single date-time stamp The Proposed Start date/time of the a Change or Release Request - (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker
h_proposed_end_time DATETIME/a single date-time stamp The Proposed End date / time of the a Change or Release Request - (YYYY-MM-dd HH:mm:ss). These columns should ONLY be used in conjunction with a date-time picker
h_workaround Text/Unlimited* The Workaround for a Problem or Known Error record

unlimited* - TEXT fields have a maximum capacity of 65000 characters.

Considerations

  • When specifying the column you wish to map to in your custom form id, this value is case sensitive. e.g. h_summary will acheive the desired mapping but h_Summary will fail to pull any values through. Ensure that the mapped value is written in lower case.
  • When the option of value / display is offered (e.g. in a static drop-down list) it is the display value that will be mapped to the column.
  • Do not store Telephone Numbers in Integer fields. At best you will lose the initial '0', even more likely is that the value will exceed the maximum allowed for an Integer and the operation will fail.
  • If the Default Request Details form is used in your progressive capture flow, and you try to map to h_summary or h_description, the values will not overwrite or be written to the summary or description fields i.e. the standard "Request Details" form takes precedence, but the custom questions and answers will still be written to the Questions section on the request.
  • If the same mapping is used on different custom forms in your progressive capture flow, the first mapping will be written to the specified default field on the request, and any subsequent mapping to the same field will not overwrite this value, but will be written to the Questions section on the request.
  • When custom questions have been mapped, the question and answer are still available in the Questions section on a request as well as now being available in the mapped field. This restriction can be controlled via the application setting "guest.itsm.progressiveCapture.overwriteCustomFieldValue".
  • Once you have configured field mapping in your progressive capture custom forms, you may need to expose the mapped field on the request. See the Request Details Form Designer for more information.
  • When creating your email template, simply look for the custom options available from the variables drop down pick list. By selecting the required variable in your email template, this will pass through the answer from your custom question which has been mapped to the corresponding custom field.
Retrieved from "https://wiki.hornbill.com/index.php?title=Mapping_Fields_from_Customised_Forms&oldid=28896"