Transform Map remove records that no longer exist from destination table

[Katie A]

Hello,

I am building an import table that is populated by an external script.

The Transform Map maps the data from the Import table to the Destination table.

The import will run once a day.

Question: How can I remove the data from the Destination table when the record no longer exists at the source? In other words, an existing record is removed from the source and will no longer appear in the Import table the next day. How can I then remove that record at the destination using a transform map script?

[nyardi]

Hiya,

Did you get around this problem? I have the exact same use case and it is turning out to be harder than I thought it would!

[Katie A]

Hi All,

Here is how I solved this issue for the transform maps.

1. Created a new field on the destination table called 'Last Import DateTime' data type is Date/Time
2. Added a new Transform Map field, source is 'Use Source Script' and mapped to Last Import Date/Time field on destination.

The Last Import Date/Time field now tracks the last time the Transform Map updated the record

Note: Set date format to yyyy-MM-dd hh:mm:ss

Source Script:

answer = (function transformEntry(source) {

  // Set to current datetime
  var currentDateTime = gs.nowDateTime();
  return currentDateTime;

})(source);

3. Created a Scheduled Job to run on the table and check the Last Import Date/Time field, if the date/time was more than 3 days ago, set the record to active = false. The record could be deleted in the script but it is usually better to de-activate rather than delete as a best practice.

Scheduled Job:

ExampleDeactivate();

function ExampleDeactivate() {
  var gr = new GlideRecord('u_example_table');
  gr.addQuery('u_active', true);
  gr.addNotNullQuery('u_last_import_datetime');
  // Last Import DateTime at or before 72 hours ago
  gr.addEncodedQuery('u_last_import_datetime<=javascript:gs.hoursAgo(72)');
  gr.setLimit('1000');
  gr.query();
  while (gr.next()) {
    // Set Active to false
    gr.u_active = false;
    gr.update();
    gs.log('Record set to Inactive Name: ' + gr.u_name, 'Active Status Management');
  }
}