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,

Can you add log statements in your onBefore script. Also the source table is staging table so how it will have a field not starting with u_

in your script you are checking 'sp_vr' field. Is that a valid field in the source table

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

gs.log("NF onBefore Script Called");

gs.log("NF source.sp_vr is:"+source.sp_vr);

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");

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 to run the transform map with code corrected, now it updates the data but doesn't check on the conditions that we have in built.

seems that   it is skipping the rows and updating the rest.

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

gs.log("NF onBefore Script Called");

gs.log("NF source.u_supplier_validation_result is:"+source.u_supplier_validation_result);

if(source.u_supplier_validation_result==""){

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");

Regards,

Narmi

[Ankur Bawiskar]

Hi Narmi,

So this is working fine right as per your requirement. It is skipping the records based on your condition.

Regards

Ankur

[farci]

Hi Ankur,

No it is not working fine, It is not evaluating the condition and updating the record.

The records which I tried updating are of different workflow state, other than the one which is permissible for update, I even tried removing the 'supplier validation result' field value and tried to upload it still updates the record. In both of these cases it shouldn't had allowed me to update.

The last condition on updating additional records which are not already existing in the table. I need to check this if the transform insert the new record into the table.

Regards,

Narmi

[Ankur Bawiskar · Accepted solution]

Hi Narmi,

Any update on this?

Can you mark my answer as correct, helpful and hit like if you were able to achieve the requirement. This helps in removing this question from unanswered list and helps users to learn from your thread. Thanks in advance.

Regards

Ankur