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

Go to solution
Shraddha M
Tera Contributor

‎11-22-2022 03:03 AM

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. 

 

0 Helpfuls
  • 3,987 Views
1 ACCEPTED SOLUTION

Go to solution
jaheerhattiwale
Mega Sage
In response to Shraddha M

‎12-07-2022 04:00 AM - edited ‎12-07-2022 04:05 AM

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

 

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

jaheerhattiwale_0-1670414384187.png

 

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.

View solution in original post

0 Helpfuls
15 REPLIES 15

Go to solution
Goran WitchDoc
ServiceNow Employee

‎11-22-2022 02:41 PM

Hi,

 

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

0 Helpfuls

Go to solution
Shraddha M
Tera Contributor
In response to Goran WitchDoc

‎11-23-2022 01:34 AM

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 AField BField C
ABC1AADD
ABC1BBEE
ABC2CCFF
ABC2GGHH
0 Helpfuls

Go to solution
ersureshbe
Giga Sage
In response to Shraddha M

‎11-23-2022 02:05 AM

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

ersureshbe_0-1669197831409.png

 

0 Helpfuls

Go to solution
Shraddha M
Tera Contributor
In response to ersureshbe

‎12-06-2022 11:49 PM - edited ‎12-06-2022 11:53 PM

Hi Suresh,

 

Field_SRCField_EXT
MDMEXT_MDM_1
MDMEXT_MDM_2
FINNEXT_FINN_1
FINNEXT_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:gs.beginningOfToday()@javascript: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

0 Helpfuls