Transform map script only to update(no insert) based on conditions.

[suryan]

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:

1)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)

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

3)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,

Surya

[Ankur Bawiskar]

Hi Suryan,

use onbefore transform map script as follows:

use correct field names for "fieldName" and "targetFieldName" as per your staging table and target table columns

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

error= true;

error_message = 'The field is blank';

}

if(target.<targetFieldName> == 'A' || target.<targetFieldName> == '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

[suryan · Accepted solution]

i checked the above code it is not throwing an error if i insert with empty field , it is not inserting but showing as updated 1 record successfully. How the user knows that it is not inserted if it does not throw a message

[Ankur Bawiskar]

Hi Suryan,

error=true will itself stop the code from updating. did you add the fieldName correctly from your staging table in source.<fieldName>

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

[suryan]

!)Field name is correct in source table.

if(source.u_supp_validation_result == ''){

error= true;

error_message = 'The field is blank';

}

if(action == 'insert'){

ignore = true;

}

2) I Use the above code I try to upload 5 records in that i updated 3rd record ( supplier validation result) rest i leave blank. when i transform load it is updating all 5 records