Mapping to Virtual Tables

Virtual tables are used to perform migrations to ServiceNow that cannot be done through a standard table mapping.  Each virtual table uses a specialised script on the ServiceNow transform map to perform the required migration. Often, the virtual table will create, update or delete multiple records in its execution. Virtual Tables can be thought of as server side processes that can be run, with the field mappings being the parameters for the process.

To create a new mapping based on a virtual table, create a new Form Mapping, then select the virtual table from the target form drop-down list.  Virtual tables are at the top of the list of target tables.

The following virtual tables are available: 

Virtual Table Name	Purpose
#!JOURNAL ENTRY MIGRATOR!#	Migrates ServiceNow journal entries OR external comments/work notes to SN journal entries on target instance. Thiscan include creation of the audit record that allows the journal entry to be viewed in an activity log.
#!WF ACTIVITY VARIABLE VALUE MIGRATOR!#	Migrates Workflow Variables between ServiceNow instances
#!VARIABLE VALUE MIGRATOR!#	Migrates Variable Values between ServiceNow instances
#!HRM_G2S_TEMPLATE_RELATIONSHIP_PROCESSING!#	Converts template relationships in the ServiceNow HR Global application to template relationships in the ServiceNow HR Scoped application.
#!R2S_ORDER_GUIDE_DISPLAY_PROCESSING!#	Converts BMC Remedy Service Request Definition Q&A into ServiceNow Order Guide Q&A with corresponding ordering and UI Policies for hiding/un-hiding conditional questions.
#!R2S_WORKFLOW_TRANSFORM_PROCESSING!#	Converts BMC Remedy Process Definition Templates (PDTs) into ServiceNow equivalent Workflow objects.
#!GENERATE RECORDS FROM REFERENCE LIST!#	Generates a set of target records from a single reference field. The same thing can often be acomplished using a 'One To Many' mapping. See this article for more details.
#!GENERATE METRIC DEFINITIONS!#	Generate a set of Metric Definitions from lists of display, numeric and ordering values. Used primarily in migration of RSA Archer Assessment Question Choices into Service Now Metric Definitions.
#!GENERATE ASSESSMENT DATA!#	Generate a set of Assessment Instance Question Records for a given Set of Metrics. Used primarily in migration of RSA Archer migration of RSA Archer Questionnaire Data into Service Now Assessment Instance Questions.

Migrating Journal Entries

The Journal Entry Migrator virtual table is used to run a script using input from a source Journal Entry table or external comments/work notes table so that the entries are created in the correct order and with the correct usernames and timestamp on the journal entries for comments and work notes.

When mapping to this virtual table the following fields should be set:

Target Field	Mapped Value
Parent ID	the sys id of the target parent record (if mapping from a ServiceNow source instance, use a reference mapping of the parent record)
Parent Table	map to the 'name' field if using a source ServiceNow Journal Entry, otherwise, set the table name explicitly (e.g. 'incident').
Field Name	map to the 'element' field if using a source ServiceNow Journal Entry, otherwise set to 'comments' for customer comments or 'work_notes' for work note activities.
Entry Text	map to the 'value' field if using a source ServiceNow Journal Entry, other wise map the field that contains the journal text.
Created On	map to the  sys_created_on field or other date field that contains the create-date for this entry.
Created By	map to the sys_created_by field or other field that contains the id of the user that created this entry (use a lookup if necessary)
Add Audit Entry	True' (if the journal entries are displayed in an activity field you need audit records created as well)
Disable History Refresh	Defaults to False, set to True if you want to prevent the journal history records from being refreshed.  This is normally required to refresh the displayed list of activities on the target record after the audit records have been created.

For example if you want to migrate Journal Entries for the Incident table, first create a mapping from Incident to Incident; ensure that the created field is mapped and that the migration method is set to Import Set (Scripted).  This is important as it needs to match the create date on the original incident for the journal entries to be displayed correctly.

Finally create a mapping from the Journal Entry table to the Journal Entry Migrator Virtual table using the values defined above.

The above mappings are provided in the Incident and Problem migration template for ServiceNow->ServiceNow migrations.

Migrating Workflow Activity Variables

The Workflow Activity Variable Value Migrator virtual table is used to run a script to migrate workflow activity variable values from the source to the target instance.  It migrates from the source sys_variable_value table to create the corresponding variable values for the target workflow activity table.  Note that the workflow activity that the variables relate to should be migrated prior to this migration.

When mapping to this virtual table the following fields should be set:

Target Field	Mapped Value
Model Name	the name of the workflow activity definition (use a lookup to retrieve this from the wf_activity_definition table);
Parent ID	the SysID of the target workflow activity definition record (use a reference mapping to retrieve this)
Activity Variable Name	the name of the activity variable (use a lookup to retrieve the element field from the wf_activity_variable table
Parent Table	map to the 'table' field
Value	map to the 'value' field
Order	map to the  ‘order’ field

An example of this mapping is provided in the Workflow Definitions template for ServiceNow -> ServiceNow migrations.

Migrating Variable Values

The Variable Value Migrator virtual table is a more general version of the workflow variable value migrator.  It can be used to migrate variable values that belong to any parent table.  The source table should be the sys_variable_value table.   Note that the parent table that the variables relate to must be migrated prior to this migration.

When mapping to this virtual table the following fields should be set:

Target Field	Mapped Value
Parent ID	the SysID of the target parent record (use a reference mapping to retrieve this)
Variable ID	the name of the variable (use a lookup to retrieve this from the parent table);
Parent Table	map to the 'table' field
Value	map to the 'value' field
Order	map to the  ‘order’ field

When using virtual tables, only the import set migration option is allowed and they can only be used for ServiceNow target migrations. The script itself determines if business rules are run and whether to create or update when the target entry exists or not.

Generating records from a reference list

The Generate Records from Reference List virtual table is used to generate a set of records based on the list of reference values in a single source field. Note that the One to Many mapping function can often be used in place of this virtual table and is generally easier to develop.

When mapping to this virtual table the following fields should be set:

Referenced Table Name	The name of the table that the list of values references. Each value will be used to identify a record on this table and the sys_id of the record found will be migrated to the target reference field. This table must exist on the target server.
Referenced Field Name	The name of the field on the referenced table to which each value in the list refers. This field must exist on the table above.
Referenced Value List	The set of values. This must be a comma delimited list. A Service Now List Field or an RSA Archer Related Records/Cross Reference field can be mapped without modification. Alternatively a Lookup result with multiple match option = concatenate csv can be used. A target record will be created (or updated) for each value in this list.
Referenced Table Query	Optional. A Service Now encoded query string. This restricts the records under observation in the reference table and can be used if the values in the list are not unique in the referenced table. This query must be valid when run against the referenced table
Target Table Name	The name of the table where records will be created or updated. This table must exist on the target server.
Target Child Reference Field Name	The name of the field on the target table which will be populated with a value in the reference list. This will be populated with the sys_id of the matched referenced record.
Target Key Field 1 Name	Optional. The name of a target field which, in addition to the child reference field will be used to serve as a key for the target record. If populated, This field must exist on the target table.
Target Key Field 1 Value	Optional. The value that target key field 1 should be set to / matched with
Target Key Field 2 Name	Optional. Similar to 'Target Key Field 1 Name' above
Target Key Field 2 Value	Optional. Similar to 'Target Key Field 1 Value' above
Target Key Field 3 Name	Optional. Similar to 'Target Key Field 1 Name' above
Target Key Field 3 Value	Optional. Similar to 'Target Key Field 1 Value' above
Target Additional Field Name List	Optional. A comma delimited list of field names on the target table, that need to be populated with values. All these fields must exist on the target table to avoid an error being reported for the migration. If populated all fields must exist on the target table
Target Additional Field Value List	Optional. A comma delimited list of values to which the fields defined above will be set to on the target records. There must be the same number of values in the value list as there are fields in the field list.

 Note that where a comma delimited list is passed, you can optionally delimit each value in double quotes. However you must be consistent in this:

value1,value2,value3 is valid

"value1","value2","value3” is valid

"value1",value2,value3 is not valid, mixture of delimited and undelimited values

There must be no spaces before or after each comma

Generating Metric Definitions

This is used to migrate answer values from the question library in RSA Archer into Service Now Metric Definition records.

When mapping to this virtual table the following fields should be set:

Metric Id	This is the sys_id of the Metric to which the generated Metric Definition (Choice) records should relate.
Display Values Text	This is a comma delimited list of the display value for each Metric Definition (choice) that should be created for the metric
Numeric Values List	This is a comma delimited list of the numeric value for each Metric Definition (choice)
Order Values List	This is a comma delimited list of the numeric ordering value for each Metric Definition (choice)

Note that where a comma delimited list is passed, you can optionally delimit each value in double quotes. However you must be consistent in this:

value1,value2,value3 is valid

"value1","value2","value3” is valid

"value1",value2,value3 is not valid, mixture of delimited and undelimited values

There must be no spaces before or after each comma.

The 3 comma separated lists must have the same number of values otherwise an error will occur.

If a metric definition with the same display value already exists on the target, the existing record will be updated.

Generating Assessment Data Records

This is used to migrate question answer values from a questionnaire record in RSA Archer into Service Now Assessment Instance Question records. A single questionnaire source record can therefore generate multiple Assessment Instance Question records.

When mapping to this virtual table the following fields should be set:

Assessment Instance Id	This is the sys_id of the Assessment Instance that the answer values will reference.
Metric Identifier Field Name	This is the name of the field on the Metric table that can be used to identify which metric each individual question relates to.
Metric Identifier List	This is a comma separated list of metric identifiers. Each Identifier should match with one and only one Service Now Metric record
Metric Query	Optional. An additional query that can be applied when identifying the metric for each metric identifier. This should be a Service Now encoded query string.
Question Answers List	This is a comma delimited list of answers to the metrics identified in the metric identifier list. These will be used to populate the Assessment Instance Question records for the Assessment.
Additional Field Name (1-4)	Optional: These 4 fields can be used to identify additional fields on the Assessment Instance Question records that need to be populated
Additional Field Values (1-4)	Optional: These 4 fields can be used to define the values to give the field identified by Additional Field Name. The value van be either a single value or a comma separated list of values. If a list is used, each value will be used in order when generating the target records.

Note that where a comma delimited list is passed, you can optionally delimit each value in double quotes. However you must be consistent in this:

value1,value2,value3 is valid

"value1","value2","value3” is valid

"value1",value2,value3 is not valid, mixture of delimited and undelimited values

There must be no spaces before or after each comma.

All comma separated lists must have the same number of values otherwise an error will occur.

If a record already exists for the assessment id / metric combination it will be updated.