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

jaheerhattiwale · ‎12-07-2022

@Shraddha M Just map the fields and add coalesce on source field. That should solve your problem.

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

Shraddha M · ‎12-07-2022

But I don't want to completely ignore the date. I want to append "EXT_MDM_2" to incident tkt : ex>> "EXT_MDM_1 , EXT_MDM_2" likewise

jaheerhattiwale · ‎12-07-2022

@Shraddha M Tried and Tested 100% working solution

As per you example below:

1. Create a field map for Field_SRC and make it coalesce

2. Don't create a field map for Field_EXT

3. Create a onBefore transform script and add just one line to it as below

target.description += ","+source.Field_EXT.toString();

Note: Assuming you are moving the data to description field, you can replace it with your target field

Please mark as correct answer if this solves your issue.

Field_SRC	Field_EXT
MDM	EXT_MDM_1
MDM	EXT_MDM_2
FINN	EXT_FINN_1
FINN	EXT_FINN_2

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

Shraddha M · ‎12-07-2022

Thank you Jaheer.

This transform map will be invoked by scheduled job which will run once a day.

It is working perfectly on first run. On running the transform map again it is updating the data..i.e. on day2 it should create new set of incidents for common Field_SRC values

jaheerhattiwale · ‎12-07-2022

@Shraddha M Once the records gets created in the import set table the transform map automatically gets executed. No matter from where the data is getting inserted in import set table.

So definitely it will work with the scheduled job as well.

Please mark as correct answer if this solves your issue. Thank you.

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