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

[Shraddha M]

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. 

[Shraddha M]

On running data source for the first time the Incident Data looks like this -

INC#1

Short description - MDM

Description - EXT_MDM_1,EXT_MDM_2

On doing "Load all records" again on the data source, it is giving below result on the same above incident

INC#1

Short description - MDM

Description - EXT_MDM_1,EXT_MDM_2,EXT_MDM_1,EXT_MDM_2

Basically this should have created another incident same as like original (because job will run daily to fetch data from oracle db and each day there can be different set of extracts)

INC#1

Short description - MDM

Description - EXT_MDM_1,EXT_MDM_2

INC#2

Short description - MDM

Description - EXT_MDM_1,EXT_MDM_2

[jaheerhattiwale]

@Shraddha M Got it. So when the new data is received, you want to create a new unique incident no matter if same incident is present before.

Let me work on that and come back to you

[Shraddha M]

Exactly

[jaheerhattiwale · Accepted solution]

@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.

[Shraddha M]

Thanks a ton! Jaheer.

The solution worked really well! 🙂