During the transform,system has to delete the record in the target table which is not in the staging

[NithyadeviH9387]

During the transform,system has to delete the record in the target table which is not in the staging table?Is there any action to delete the unused records in the transform?for example,in the employee table some employees are left the organization,during the transform we need to remove or delete the employee who are not currently working.how to achieve or schedule this on monthly basis to remeve the records on target table.

I have idea,that we can execute on OnComplete transform script.Can anyone suggest how to write the Oncomplte transform script to execute this on monthly basis?

[ChiranjeeviR]

Hi @NithyadeviH9387 

 OOTB there is no such option with Transform Maps but you can have a way to implement deletion/deactivation. What you can do is:

1. Create a onStart Transform Script and initialize an array type variable.
   

   function onStart(<parameters>) {
   	//Use onStart constructor as provided OOTB
   	var userArr = []; //Array will be populated on onAfter Transform Scripts
   }​

2. Then create onAfter Transform Script and in that transform Script check the status of import if it is successful then push the target record sys_id in the array "userArr"
3. Finally write a onComplete Transform Script and do a GlideRecord query on User table:
   

   function onComplete( < parameters > ) {
       //Use the OOTB constructor
       var deactivateUserGr = new GlideRecord('sys_user');
       deactivateUserGr.addEncodedQuery('sys_idNOT IN' + userArr.join(','));
       deactivateUserGr.query();
       while (deactivateUserGr.next()) {
           deactivateUserGr.active = false;
           deactivateUserGr.update();
           //OR
           //deactivateUserGr.deleteRecord();
       }
   }​

Please try the above steps and see if it works for you.

If my answer is helpful then please do mark my reply as correct/helpful.🇮🇳

[Robert H]

Hello @NithyadeviH9387 ,

Here is a generic solution to this kind of problem: create the following onComplete Transform Script and adjust the first three variables as needed:

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	var targetTable = 'sys_user',
		targetIdField = 'employee_number',
		sourceIdField = 'u_employee_id';

    var existingIds = [];
    var grExisting = new GlideRecord(targetTable);
    grExisting.addNotNullQuery(targetIdField);
    grExisting.query();
    while (grExisting.next()) {
        existingIds.push(grExisting.getValue(targetIdField));
    }

	var importedIds = [];
	var grImported = new GlideRecord(import_set.table_name);
	grImported.addQuery('sys_import_set', import_set.getUniqueValue());
	grImported.query();
	while (grImported.next()) {
		importedIds.push(grImported.getValue(sourceIdField));
	}

	var existingButNotImported = existingIds.filter(id => !importedIds.includes(id));

	existingButNotImported.forEach(id => {
		// add your own code to deactivate or delete the record that has this ID here

	});


})(source, map, log, target);

A word of warning: you might want to add some validation to ensure that the import set actually contains some rows, because if that script were to run against an empty Import Set then all the records in the target table would be removed or deactivated.

Regards,

Robert

[NithyadeviH9387]

Hi Robert,

I have tried the above script,by deleting few records on excel file.Those records are imported and after transform the above On complete transform script is not executed.It remains the same.

[Robert H]

Hello @NithyadeviH9387 ,

Did you replace the placeholder

// deactivate or delete the record that has this ID

with your own code to deactivate or delete those user records, or whatever should happen to users who are not part of the Excel file? I have updated my original response to make this more clear. 

Did you update the first three variables as needed? In my example I assume that the Excel file has a column named "Employee ID", which is being compared to the "Employee number" column on the User table. If you use other column names you'd need to change these values.

Regards,

Robert