Determine inactive records from a transform map

[KB15]

Is there a method to determine if a record is active or not without an explicit field? I have the following dilemma:

I have an import file that only sends active records into ServiceNow. The goal is to add the active records into a custom table while marking any records inactive which were not in the import .

For instance:

The import file has the following

Record 1

Record 2

but the existing table has

Record 1

Record 2

Record 3

Record 4

I need to find a way to tell SericeNow to make Record 3 and 4 inactive. I've tried using the action == insert/update/ignore but that doesn't seem to work out. I was thinking of a query to look for the value and if it doesn't exist, make the existing record inactive but I think that'll take too long for SN to process. We're looking at about 8000 records.

[Pradeep Sharma]

Sure. I mean create a custom field on the target table and set the default value to none. Now all the records getting updated via import set should set the custom field value to (for ex : assuming custom field name is source and the value should be set to 'import set name/transformation map name')

Now defined a scheduled job and delete the records based on the custom field i.e source value not equal to import set name.

Please let me know if you have any questions.

[justin_drysdale]

We have a similar process for groups where if a group does not exist in the import set but DOES exist in Service Now then we create a ticket to remediate the groups.

We use a scheduled job/script timed to run after the group import set is processed.

[KB15]

After some assistance, the solution was very simple. Oversight level simple.

I've created an active field in the import source table. The default value is true.

The transform map field is mapped directly to the target's field.

An onStart GlideRecord script runs to make all existing records in the target table false before the import begins

When the import starts, the source records are imported or updated as active and any matching or inserted records are also set as active.

All remaining records are already set to false.

It works well, surprisingly.

[andrewpilachows · Accepted solution]

While it's easy, it can easily cause a performance hit depending on when it runs and how many records need to be processed.   If you are looping through all records on the table, first pass would be to run a query only on active records to then loop through to set inactive.   This will still take a lot of time depending on how many records are being imported each time.   If its a small set then this should be sufficient.

If this is going to be tens of thousands of records, you might consider a different approach.   You could add a "last refreshed" field on the table and set to the current datetime on each import.   Then instead of the onStart create an onComplete script where the query is active records and last refreshed < X time ago.   If the import runs once a day, could be u_last_refreshed < gs.daysAgo(1).   This idea is borrowed from 3.1 on this wiki page about managing LDAP users where only active records are imported.

Detecting Disabled LDAP Records - ServiceNow Wiki

[KB15]

Just wanted to say that we've taken this approach instead of the original setup. Thanks for your input.