Importing the sys_import_state field from an import set

[User163016]

We have an integration with our HR systems that sends user data to our user table and triggers various on boarding activities. When the data hits our system it goes directly into an import set table, imp_user. From there it transforms and populates the sys_user table. The problem that we're having is that the imp_user table only holds the data for a few days before it gets purged. I need that data as a permanent record of what we received. So, I've created a new table to house this data, u_imp_user_history. Now the transform runs for this table and sends it to my new table. This works great, but I can't get the correct value to come over for the sys_import_state field. It always sends over Pending.

To address this, I've created a business rule. This rule runs on the imp_user table, after insert. The condition is if State changes from Pending, run the following script:

(function executeRule(current, previous /*null when async*/) {
  var rec = new GlideRecord('u_import_user_history');
  rec.addQuery('sys_id', current.sys_id);
  rec.query();
  while (rec.next()) {
      rec.u_status = current.sys_import_state;
      gs.log("STATUS: " + rec.u_status);
      rec.update();
    }
})(current, previous);

I can't seem to get this to update the status value on the new table.  Any ideas?

[ARG645 · Accepted solution]

chadhopkins,

I understand that you want to capture whats coming in into the Import Set Table. So you crated a new table named u_imp_user_history to capture it. I believe the reason why the state is defaulting to  PENDING is because : When a record in the import set gets inserted, its state will initially set to pending then it will get changed automatically to either inserted/ignored/etc etc. When your Business Rule runs, it actually running at the time of insert so it will propagate the state value as PENDING. 

Instead of going through all this, if you want to preserve the data in the import set itself without having to create a new table. Search for a scheduled Job named Import Set Deleter.  This job is responsible for deleting the import sets in a periodic time interval. This Job uses a script included named ImportSetCleaner. in this script include search for the function named _queryIsets and add a line of code as shown in the below script (gr.addQuery('table_name','!=','YOUR TABLE NAME');)

 _queryIsets : function() {
      var gr = new GlideRecord('sys_import_set');
      gr.setLimit(this.isetsChunk);
      
      if (this.table)
         gr.addQuery('table_name', this.table);
      
      if (this.daysAgo > 0)
         gr.addEncodedQuery('sys_created_onRELATIVELE@dayofweek@ago@' + this.daysAgo);

      /*** Put your Import set table name**/   
      gr.addQuery('table_name','!=','YOUR TABLE NAME');
      /******************************/


      gr.query();
      var isets = new Array();
      while (gr.next()) {
         this._log('Cleaning import set ' + gr.number);
         isets.push(gr.sys_id + '');
      }
      
      return isets;
 }

Hope this Helps,

Aman Gurram

[User163016]

Thanks Aman.  I'll give this a try and let you know how it works out.  

[User163016]

Thanks Aman!