how to ignore inactive records on target table before an import.

[chucksimonds]

Is there a way to ignore inactive records on the target table before matching the records? Using an onBefore script to ignore inactive records ignores the inactive record, but also it ignores/drops the source record so it is not available to match on an existing active record. I would like to ignore the false record and still match on the active record. Is it possible to use a script/query to only pull in (process) the active records on the target table? Thanks!

[Chuck Tomasi]

Thanks for the use case Chuck. That's clear. I get what you're trying to do now.

Any chance of getting another type of ID from the second source? E.g. email? Employee ID? Last 4 of the SSN? Just checking. Improving the source data is a better way to reduce risk against ambiguous data than filtering the target. Sooner or later, you'll have two active users with the same first/last name. 🙂

[chucksimonds]

The email would be good to match on, but I'm not always sure it will be present in this import. I'll do some checking. And yes, there already exists the chance for 2 active records with same name but inactive/active is more common and just one more problem I was trying to eliminate.

[Chris M3]

Assuming that you do not want your second source to create new records...

Add an additional coalesce field on the active flag, with the source as a script always true

and add an on before script that if action == insert, ignore=true

[chucksimonds]

Chris, that sounds like it might work. Don't have time to try it now, but I;ll get back to this and let you know. Thanks!

[Chris M3]

There's one other possible solution.

Coalesce on sys_id, and use a source script to query for the exact record you want.

This would also allow you to add functionality to warn you of unexpected conditions (Two active users with the same name)

Something like the below

answer = getUser(source);

function getUser(source) {

        var gr = new GlideRecord('sys_user');

        gr.addQuery('first_name', source.first_name);

        gr.addQuery('last_name', source.last_name);

        gr.addActiveQuery();

        gr.query();

        if (gr.getRowCount() == 1)

                return gr.next().sys_id;

        if (gr.getRowCount() > 1) {

                // Insert logic to alert you, maybe generate an incident

        }

        return 'random-non-sys_id-value'; //Note, not -1, as that will sometimes result in odd behavior, personal experience

}

And the same on before script to ignore inserts