Transform Map script to update records based on field value

[farci]

Hi All,

I have a transform Map in which I had made a field as Coalesce, so that only matching records gets updated when user export the excel file and

updates it and then uses the transform map to update the records in the table using the exported file which he/she has updated.

My challenges are:

I want the transform map not to update new records that are not part of export file that he did before updating (any new additions that are not there in the table)

I want the transform map not to update or stop updating, when a specific field has 'no records' / blank, show the error message about it

I want the transform map to update the records into the table only when the record workflow status is either 'A' or 'B'.

I am sure that this can be achieved by using transform script which has to run On before query, not sure, how to achieve this.

Any help and suggestion, highly appreciated.

Regards,

Narmi

[Ankur Bawiskar]

Hi Narmi,

You can stop updating using onBefore transform map script

All these can be done in only 1 onBefore transform map script.

For point 2 I want the transform map not to update or stop updating, when a specific field has 'no records' / blank, show the error message about it

if(source.<fieldName> == ''){

error= true;

error_message = 'The field is blank';

}

For point 3 I want the transform map to update the records into the table only when the record workflow status is either 'A' or 'B'.

again onBefore script

if(target.<workflowStatusField> == 'A' || target.<workflowStatusField> == 'B'){

}

else{

error= true;

error_message = 'Record could not be updated since workflow status is not A or B';

}

}

Can you explain more on point1 I want the transform map not to update new records that are not part of export file that he did before updating (any new additions that are not there in the table)

are you saying that you don't want to add new records in the target table and only update should happen. If so then in onBefore script use action variable

if(action == 'insert'){

ignore = true;

}

Mark Correct if this solves your issue and also hit Like and Helpful if you find my response worthy based on the impact.

Thanks

Ankur

[farci]

Hi Ankur,

Thanks for the quick response,

My first point, is as you mentioned, no new records should get inserted only the existing record should get updated, provided the other conditions are met.

I am no good at coding just a beginner, here in the transform map I don't see an option to select 'On before' as we see in Business rules. Will it be possible to provide the script with the comments on what I should be doing.

Thank You,

Narmi

[Ankur Bawiskar]

Hi Narmi,

Script is: fieldName is the field in your staging table which you want to check whether is empty or not

workflowStatusField is the field in your target table which you want to check value for ie A or B

if(source.<fieldName> == ''){

error= true;

error_message = 'The field is blank';

}

if(target.<workflowStatusField> == 'A' || target.<workflowStatusField> == 'B'){

}

else{

error= true;

error_message = 'Record could not be updated since workflow status is not A or B';

}

}

if(action == 'insert'){

ignore = true;

}

Mark Correct if this solves your issue and also hit Like and Helpful if you find my response worthy based on the impact.

Thanks

Ankur

[farci]

Hi Ankur,

I tried the below script in the transform map, it does not work nor it shows me any error message.

Script and screenshot below

(function transformRow(source, target, map, log, isUpdate) {

if(source.sp_vr==""){

error=true;

error_message='Supplier Validation Results field is blank, Please update the field to complete the update';

}

if(target.s_wf==''||target.s_wf=='6'){

}

else{

error=true;

error_message='Records could not be updated as the SLAs are processed by different teams';

}

if(action=='insert'){

ignore = 'true';

}

})(source, target, map, log, action==="update");

Please note one of the field in my transform map has coalesce as 'True'.

Regards,

Narmi