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?

[Ankur Bawiskar]

@NithyadeviH9387 

Usually import sets are used to either create or update target table record.

what's your business requirement to delete the target record if it's not present in staging table?

Now coming to your question for employees -> This is sys_user record.

How are you loading/importing User? If it's LDAP then the sys_user record will automatically be marked as Active=false when your ldap import run.

Then you can run a weekly scheduled job to delete those users.

you can use onAfter transform script or onComplete script for your requirement.

Note: I will recommend discussing this with your customer to delete the record during transformation

If my response helped please mark it correct and close the thread so that it benefits future readers.

[NithyadeviH9387]

Hi Ankur,

I have given example as user table,in my case it is different table that the records are imported and transformed with attachment.Not using LDAP as datasource here.

[Ankur Bawiskar]

@NithyadeviH9387 

if the data is not imported it means that employee is not in staging table.

then how will you know which employee to search for in target table as you don't know the emp number for example.

Unless you know how will you delete?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[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.🇮🇳