Determine inactive records from a transform map

KB15 · ‎06-01-2017

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.

andrewpilachows · ‎06-02-2017

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

View solution in original post

Pradeep Sharma · ‎06-01-2017

Hello Kkim,

In this case, the only option is to GlideRecord the target table and make the record inactive. or the other way would be to create an BEFORE business rule on the target table and set the flag inactive based on some unique conditions based on data imported.

KB15 · ‎06-01-2017

If I use this approach, what would I be querying against? I don't think it'd be the import able vs the target table because the import table would have thousands more records in it.

Pradeep Sharma · ‎06-01-2017

I think the best option is to have unique field on the form something like source and populate it as import set. Now you can defined scheduled job to inactive those records which are not source import set and not updated by integration user.

There may be other options though but this is what I can think as of now.

KB15 · ‎06-01-2017

I don't think I follow completely. Would you mind if you could explain a little further or provide an example?