Combine multiple rows from import set table to create 1 incident record

Shraddha M · ‎11-22-2022

Hi,

We have a requirement to automate incident creation at SNow end by querying oracle database [error table]

The fields defined in oracle error table are source, status, project. We need to create 1 incident record for common "source" values.

jaheerhattiwale · ‎12-07-2022

@Shraddha M Tried and Tested solution. 100% working solution.

Please remove all the field maps and write only on onBefore transform script

Script in transform script:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

var sourceTable = new GlideRecord(source.getTableName());
sourceTable.addQuery("FIELD_SRC", source.FIELD_SRC.toString());
sourceTable.addQuery("sys_import_set=" + source.sys_import_set.toString());
sourceTable.addQuery("sys_import_state=inserted");
sourceTable.query();

if (sourceTable.next()) {
var targetTable = new GlideRecord(target.getTableName());
if (targetTable.get(sourceTable.sys_target_sys_id.toString())) {
targetTable.TARGET_FIELD_FOR_FIELD_EXT += "," + source.FIELD_EXT.toString();
targetTable.update();
}

ignore = true;
} else {
target.TARGET_FIELD_FOR_FIELD_src=source.FIELD_SRC.toString();
target.TARGET_FIELD_FOR_FIELD_EXT = source.FIELD_EXT.toString();
}

})(source, map, log, target);

Please mark as correct answer if this solves your issue.

Please mark the answer as correct or helpful based on impact
ServiceNow Community Rising Star, Class of 2023

View solution in original post

Goran WitchDoc · ‎11-22-2022

Hi,

I don't know what your exact question is here. Can you tell us more?

Shraddha M · ‎11-23-2022

Hi,

We have a scheduled job which runs daily for fetching data from an oracle db table. The content of oracle table is as below -

The requirement is to create 1 incident record in ServiceNow for common Field A values..

Ex: Here 2 incidents should get created for ABC1 and ABC2 respectively..

Field A	Field B	Field C
ABC1	AA	DD
ABC1	BB	EE
ABC2	CC	FF
ABC2	GG	HH

ersureshbe · ‎11-23-2022

Hi, You can achieve through script in field mapping. ie., you should get the value of source.field_b == 'AA' and validate the value based on that you should allow to create the incident. but your field-b value stored somewhere in incident. So that you can i achieve through below script. You should modify the script based your field_b value instead of sys_id

Regards,
Suresh.

Shraddha M · ‎12-06-2022

Hi Suresh,

Field_SRC	Field_EXT
MDM	EXT_MDM_1
MDM	EXT_MDM_2
FINN	EXT_FINN_1
FINN	EXT_FINN_2

Ideally this should create 2 Incidents for MDM and FINN

Approach :

1. Schedule Job (run once a day) will invoke this transform map.

2. Field_SRC >> coalesce & mapped to >> short_description

3. OnBefore Script

var src=source.u_field_src;
var ext = source.u_field_ext;

var gr1 = new GlideRecord('incident');

if(action == 'insert')

{

gr1.initialize();
gr1.description = "\n INSERT Extract Name : " + ext + "\n INSERT Source Name : " + src;
gr1.short_description = src;
gr1.insert();

}
else {

gr1.addEncodedQuery('sys_created_onONToday@javascript&colon;gs.beginningOfToday()@javascript&colon;gs.endOfToday()^short_descriptionLIKE'+src);

gr1.query();
if (gr1.next()) {
gr1.description = gr1.description + "\n UPDATE Extract Name : " + ext + "\n UPDATE Source Name : " + src; // append new extract name to the original
gr1.short_description = src;
gr1.update();
}

}

This is not working. Am I missing anything ? please suggest

Regards,

Shraddha Madye