9 Replies · Latest reply on Sep 18, 2017 3:39 AM by Ankur Bawiskar

    Transform Map script to update records based on field value

      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

        • Re: Transform Map script to update records based on field value
          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

            • Re: Transform Map script to update records based on field value
              Narmi 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

                • Re: Transform Map script to update records based on field value
                  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

                    • Re: Transform Map script to update records based on field value
                      Narmi 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

                        • Re: Transform Map script to update records based on field value
                          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

                            • Re: Transform Map script to update records based on field value
                              Narmi 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