Import Sets 2 Source Records to 1 Target Record

[kemmy1]

Hello!

I'm trying to create or coalesce 2 source import set records to 1 target record.

I got a cool script to do this (see below), but my issue is that my source records do not have a common source Classification field.

Here's what I want to do:

Source Record			Target Record
Facility	Classification		Facility	Classification Name
ABC	1		ABC	Low
ABC	2
ABC	3		ABC	High
ABC	4
XYZ	1		XYZ	Low
XYZ	2
XYZ	3		XYZ	High
XYZ	4

So, then I created a new column on the import set table called "Classification Name" and I'm trying with a "onBefore" Insert/Update business rule to update this new column to set the records with 1 and 2 Classification Name to "Low" for example.

The column gets set when I manually update the record, but not when I kick of the import (the business rule should kick off on import right?)

Here's my business rule script:

var classification = current.u_classification;

    switch (classification .toString()) {

        case '1':            

            current.classification_name= 'Low';

            break;

        case '2':

            current.classification_name= 'Low;

            break;

        case '3':

           current.classification_name= 'High';

            break;

        case '3':

            current.classification_name= 'High';

            break;

       }

Here is the script (starting of it) that I'm using for my transform map.  It's an onbefore transform map script and I'm not mapping any fields (just FYI):

var sourceTable = new GlideRecord(source.getTableName());

    sourceTable.addQuery("u_dtf_id", source.u_dtf_id.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.detloc += "," + source.u_dtf_id.toString();

            targetTable.update();

        }

        ignore = true;

    } else {

        target.detloc = source.u_dtf_id.toString();

        //target.TARGET_FIELD_FOR_FIELD_EXT = source.FIELD_EXT.toString();

    }

[Matthew_13 · Accepted solution]

Hi Buddy,

 This is a good approach overall, and you’re on the right track 👍

What I am seeing with the business rule is about right. Import Set loads don’t consistently trigger onBefore/Insert/Update business rules the same way manual updates do from check, which is why the field updates when you edit the record yourself but not when the import runs.

For this kind of normalization logic, the best place to handle it is directly from my recommendation is in the Transform Map, less a Business Rule. Since you already have an onBefore transform script, you can probably move the classificattion logic there and set the value on source record before your coalescing logic runs.

Like:

switch (source.u_classification + '') {
    case '1':
    case '2':
        source.u_classification_name = 'Low';
        break;
    case '3':
    case '4':
        source.u_classification_name = 'High';
        break;
}

This basically ensures the value are there during the transform and makes your coalescing logic more relaible.

One small thing to note I seen as well is your business rule script has a duplicate case '3' where one of those likely should be case '4'.

Overall though, your design makes sense 🙂 and moving this piece into the transform should get you unstuck. Hope this helps buddy!

@kemmy1 - Please mark as Accepted solution and Thumbs Up if you found Helpful!!