Transform Map Script

[Ramanjaneyulu]

Hi Team,

I have a below requirement on transform map :

Over view on the configuration and requirement :

There was a daily data import process via scheduled pull integration for custom account table.

data will first import in staging table and via transform map it will process to account table.

At the external data base when any account record updated then data base will created new record for that update and mark existing record as Inactive.

because of this while importing data from external data base two records are getting created in staging table for the same account, one is active record and other one is Inactive record.
We this account record unique field is Account code in servicenow and external data base.

the same account code has been set as coalesce in servicenow transform map.
We are not allowed to use other fields as coalesce part from account code.

Issue :
because of one active record and one Inactive records while transform the data from staging table to target table system randomly selecting either one of record.

Solution to do :
required to avoid randomly selecting either one of record by system, it should only pick active record when two records found one as active and other one as inactive.

the same will not applicable vise versa it should directly process if account having single record even it is inactive record.


Could you please provide some approach for this solution.

[Kris Moncada]

I personally would address the issue "system randomly selecting either one of record."

Without seeing the scripts, (i.e. onBefore, onAfter, onStart, onComplete transform map scripts) my guess is that some "system" script is processing records as they come. For example, with no "orderBy" set in the script, if an "inactive" record is returned first (and there's an "active" record next), then that's the record it's going to use to process the account.   Then on some future run of the script, "active" might be first.

So in summary, I would address the "randomness" by looking at script that is doing the the "randomly selecting". Some things to try:

• filter out "inactive" records.
• orderBy "active" records.
• In your transform script onBefore, you can use ignore = true, to prevent the transform map from inserting the record, if the record is inactive. More info here: https://docs.servicenow.com/bundle/tokyo-platform-administration/page/script/server-scripting/refere...