{"revision":"c10276e953456835cdbb1f3a60d58ae1980be353","document":{"id":22185,"path":"integrate-applications/system-import-sets/c_MappingOptions.md","title":"Mapping options","bundle":null,"doc_type":null,"release":"australia","body":"\n# Mapping options\n\nYou can map fields in a number of ways depending on the circumstances of the import and whether data must be transformed prior to loading onto a production table.\n\nIt is also important to note that any given import operation may require taking advantage of multiple mapping methods, and these methods can be readily used in conjunction with one another.\n\n## Automatic Mapping Utility\n\nThe simplest mapping method is where all of the field names of the import sets match the names of the fields on the production tables onto which the data will be transformed. In this case, simply click **Auto map matching fields** in the related list in the Table Transform Map form and confirm proper matching. If there are any discrepancies in terms of how fields were automatically matched, use the mapping assist utility to correct them. When all fields are matched properly, click the **Transform** related link to begin transforming data onto the destination table.\n\n## Mapping Assist Utility\n\nThe mapping assist utility provides a visually intuitive environment for specifying mapping between import set fields and production table fields. The mapping assist utility makes it possible to map a single source field \\(field on an import set table\\) to multiple destination fields \\(fields on a production table\\).\n\n\\[Omitted image \"MappingAssist.png\"\\] Alt text:\n\n## Changing the Date Format\n\nIf the date format of the source field does not match the format of the target field, you can set a date format mapping to transform dates from one format to another. For example, this mapping specifies that the date format of the CSV source file uses the **MM-dd-yyyy** format.\n\n\\[Omitted image \"FieldMapDateFormat.png\"\\] Alt text:\n\n\\[Omitted image \"FieldMapDateFormatOptions.png\"\\] Alt text:\n\nDate format options include:\n\n|Date Format|Description|\n|-----------|-----------|\n|dd-MM-yyyy|Day-month-year|\n|dd-MM-yyyy hh:mm:ss|Day-month-year hours-minutes-seconds|\n|dd-MM-yyyy hh:mm:ss z|Day-month-year hours-minutes-seconds timezone|\n|MM-dd-yyyy|Month-day-year|\n|MM-dd-yyyy hh:mm:ss|Month-day-year hours-minutes-seconds|\n|MM-dd-yyyy hh:mm:ss z|Month-day-year hours-minutes-seconds timezone|\n|yyyy-dd-MM|Year-day-month|\n|yyyy-MM-dd|Year-month-day|\n|yyyy-MM-dd hh:mm:ss|Year-month-day hours-minutes-seconds|\n\nTo denote 24 hour time format, specify a custom date format using **HH:mm:ss** instead of hh:mm:ss.\n\n**Note:**\n\nConverting from a 24-hour to 12-hour date format might cause times from 12:00 to 23:59 to be formatted as 00:00 to 11:59.\n\n## Mapping to a Duration Field\n\nServiceNow duration fields use a special data type that lists the number of milliseconds the duration value is. To map import data to a duration field use one of the following methods to transform source values into a duration.\n\n-   Calculate the duration from a start and end date\n-   Convert an existing duration value into a ServiceNow duration value\n\n## Calculating a Duration Value from a Start and End Date\n\nIf the import source has a start and end date, you can calculate a duration with JavaScript.\n\n1.  Navigate to **System Import Sets** > **Transform Maps**.\n2.  Select the transform map you want to calculate a duration value. For example, the Notification transform map that imports into the Incident table.\n3.  Select the **Run script** check box.\n4.  Enter JavaScript to transform the start and end dates into a duration. See the sample script.\n5.  Click **Update**.\n\nThis sample script transforms the source.u\\_start and source.u\\_end fields to a duration value in the target.duration field. Change the field names to match your source and target fields as needed.\n\n```\ntarget.duration = gs.dateDiff(source.u_start.getDisplayValue(), source.u_end.getDisplayValue(), false);\n```\n\n## Convert a Duration Value into a ServiceNow Duration Value\n\nIf the import source already contains a start date and a duration value, you can convert the existing duration into a ServiceNow duration. For example, you might have an Excel data source that lists a duration in seconds. ServiceNow expects durations to have millisecond values.\n\n1.  Navigate to **System Import Sets** > **Transform Maps**.\n2.  Select a transform map.\n\n    For example, the Notification transform map that imports into the Incident table.\n\n3.  Select the **Run script** check box.\n4.  Enter JavaScript to convert existing duration values into a ServiceNow duration values. See the sample script.\n5.  Click **Update**.\n\nThis sample script converts a duration in seconds \\(from the **source.u\\_duration** field\\) to a duration in milliseconds \\(in the **target.duration** field\\). Change the field names to match your source and target fields, as needed.\n\n```\n//Transform the value in source.u_duration from seconds to milliseconds\ntarget.duration.setDateNumericValue(source.u_duration * 1000);\n```\n\nThis sample script converts a duration already in milliseconds \\(from the **source.u\\_duration** field\\) to a ServiceNow duration \\(in the **target.duration** field\\). Change the field names to match your source and target fields, as needed.\n\n```\n//Transform the value in source.u_duration to ServiceNow format\ntarget.duration.setDateNumericValue(source.u_duration);\n```\n\n## Using a Script to Calculate a Source Value\n\nYou may want to use a source script instead of the **Source field** when:\n\n-   The source value is not in the format for the mapped target value.\n-   You want to look up a value before mapping to the target field.\n-   The source value must be computed from multiple fields.\n-   You need to create a compounded or calculated coalesce value in the target field.\n\nFor example, this example source script computes the value of the user\\_name field when it is blank in the source.\n\n\\[Omitted image \"FieldMapSourceScript.png\"\\] Alt text:\n\nA source script expects the calculated value to be set in the global variable `answer`.\n\n## Mapping Binary or BLOB Fields\n\nServiceNow uses a special process to import binary and binary large object \\(BLOB\\) data from JDBC data sources. All binary data is automatically converted into a record in the Attachment \\[sys\\_attachment\\] table before the transformation occurs. The import set table only stores the attachment record's sys\\_id value in the import table field instead of the actual binary value. For example, suppose you use a JDBC data source to import data from a CA Service Desk system, which stores each record's key value as binary data. When you import the Service Desk key values into a ServiceNow table, the ServiceNow field only contains a sys\\_id reference to the corresponding binary data in the Attachment Record \\[sys\\_attachment\\] table rather than the actual binary data.\n\nTo have a transform map access the attachment, use the GlideRecord API in an `onAfter` script. The script needs to run in an `onAfter` event because the `target.sys_id` object is only available after the data is placed in the import set table. For example, to map the resulting attachment to the target transform record, you can use the following script.\n\n```\nvar agr = new GlideRecord(\"sys_attachment\");\n  agr.addQuery(\"sys_id\", source.u_blob_field);\n  // the source field needs to be mapped to the source that is the BLOB\n  agr.query();\n   if(agr.next()) {\n    agr.table_name = \"cmdb_ci\"; // the target table of the transform map\n    agr.table_sys_id = target.sys_id; // the target record\n    agr.content_type = source.u_contentype; \n    // the content type string if available,  \n    // this becomes the mime encoding when clicking an attachment link\n    agr.update(); // finally, move/re-attach the attachment to the target row }\n```\n\nIf you are mapping directly to the `db_image` table, run the following `onAfter` script to display the image:\n\n```\nvar strTemp = source.u_file_name; \nvar fType = strTemp.substr(- 3); \nvar iCont = \"\" ;\n \n if(fType == 'jpg') {\n  iCont = 'jpeg' ; } \n else {\n  iCont = fType; }\n \n var agr = new GlideRecord(\"sys_attachment\");\n agr.addQuery(\"sys_id\", source.u_blob_field);\n agr.query();\n \n if(agr.next()) {\n  agr.table_name = \"ZZ_YYdb_image\";\n  agr.table_sys_id = target.sys_id;\n  agr.content_type = 'image/' + iCont;\n  agr.file_name = 'image';\n  agr.update(); }\n```\n\n**Parent Topic:**[Importing data using import sets](https://raw.githubusercontent.com/ServiceNow/ServiceNowDocs/australia/markdown/australia/integrate-applications/system-import-sets/c_ImportDataUsingImportSets.md)\n\n"}}