question OnBefore TransformScript to delete Target records in Developer forum

OnBefore TransformScript to delete Target records

swaroop — Wed, 06 Jan 2021 17:51:46 GMT

Hello All,

When a record is passed using Transform Map, and if sys_id of the source record matches with Target record and if it contains all the fields empty then it should delete the target record. Can anyone suggest me how to do that.

Thanks & Regards,

S.Swaroop.

Re: OnBefore TransformScript to delete Target records

Amit Gujarathi — Wed, 06 Jan 2021 19:32:18 GMT

Hi Swaroop,

You have the scenario if the record is already present with the same sys_id then it should delete the record.

you can achieve by writing in the onBefore transform script , it will validate before inserting the record in the target table .

Please mark correct if its helpful.

Regards,

Amit Gujarathi

Re: OnBefore TransformScript to delete Target records

swaroop — Thu, 07 Jan 2021 11:16:57 GMT

Hello Amigujarathi,

Excatly, i stuck with the script. Can you help me with the Onbefore Script for validation and deletion of the record.

Thanks & Regards,

S.Swaroop.

Re: OnBefore TransformScript to delete Target records

Ankur Bawiskar — Thu, 07 Jan 2021 11:42:04 GMT

@swaroop

update as this in onBefore transform script

I have taken an example of incident table and Coalesce on number field

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

    // Add your code here

var incNumber = source.u_number;

var gr = new GlideRecord('incident');

gr.addQuery('number', incNumber);

gr.query();

if(gr.next()){

// check here for all target fields are empty or not

// if yes then delete the record using gr.deleteRecord()

ignore = true;

}

else{

ignore = true;

}


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

Regards
Ankur

Re: OnBefore TransformScript to delete Target records

Ankur Bawiskar — Thu, 07 Jan 2021 11:42:36 GMT

Hi,

I just shared the script.

But would like to understand the business use-case/requirement around this

Regards
Ankur

Re: OnBefore TransformScript to delete Target records

swaroop — Thu, 07 Jan 2021 12:06:07 GMT

Hi Ankur,

Requirement - When data has sent through Transform map, Onbefore script should validate if same 'sys_id' was there in Target table, Then it should validate remaining fields are empty or not. If remainig fields are empty then it should delete the target record, If remaining source fields are not empty then delete the record in Target table first and then create the record again in the Target table.

Thanks & Regards,

S.Swaroop.

Re: OnBefore TransformScript to delete Target records

Ankur Bawiskar — Thu, 07 Jan 2021 12:16:45 GMT

Hi,

I have shared the above script.

Ensure you enhance it for creating record after deletion

Regards
Ankur

Re: OnBefore TransformScript to delete Target records

swaroop — Thu, 07 Jan 2021 12:32:44 GMT

Hello Ankur,

Thanks for the quick response.

I am using the below script, it was deleting the record but not creating the record. Can you please suggest where i am doing it wrong.

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

// Add your code here
var gr = new GlideRecord('incident');
gr.addQuery('number', source.u_number);
gr.query();
if (gr.next()) {
target.deleteRecord();
} else {
target.insert();
}

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

Thanks & Regards,

S.Swaroop.

Re: OnBefore TransformScript to delete Target records

Ankur Bawiskar — Thu, 07 Jan 2021 12:40:47 GMT

Hi,

As I said you have to enhance my script

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

// Add your code here
var gr = new GlideRecord('incident');
gr.addQuery('number', source.u_number);
gr.query();
if (gr.next()) {
gr.deleteRecord();

gr.initialize();

gr.number = source.u_number;

gr.short_description = 'My Testing';

gr.insert();
} else {
ignore = true; // since record not found
}

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

Regards
Ankur

Re: OnBefore TransformScript to delete Target records

swaroop — Thu, 07 Jan 2021 12:47:01 GMT

Hello Ankur,

Thanks for the code.

I will use the script as mentioned and update you on the same.

Thanks & Regards,

S.Swaroop.

Re: OnBefore TransformScript to delete Target records

Ankur Bawiskar — Thu, 07 Jan 2021 12:50:13 GMT

Sure.

We can just guide you and may not know the actual requirement.

You should be enhancing code from your side

Regards
Ankur

Re: OnBefore TransformScript to delete Target records

swaroop — Thu, 07 Jan 2021 12:55:23 GMT

Hello Ankur,

Sure, will do that from my end.

Thanks & Regards,

S.Swaroop.