Setting null values in the target table

[kshitishchodank]

Hi All,

I am very new to Service Now and using REST API. I am trying to update a particular field in the target table to null (e.g. : email), but it is not getting set. The problem is that I am sending only some columns in the REST request and other fields are not sent.   If I want to set any field as NULL or empty than the request is ignored. I enabled 'Copy empty fields' in the transform map but if the request does not contain all the fields, then the fields which are not sent in the request are set as empty. I tried using scripts but I wasn't successful and I think the values should be handled before they are inserted in the import table (I am likely to be wrong). Any help is highly appreciated as this issue is very urgent.

I went through this link How to clear out the field on target table via Web Service + Transform Map

Thanks in advance.

[kshitishchodank]

Not so sure how this will help. But my problem is that I am not sending all the fields present in the import table. I am sending only those fields in my REST request whose values I have changed. Should that data be handled before it reaches to import tables or some transform map script will resolve the issue.

[Pradeep Sharma]

Hi,

So basically you can insert the data to staging table i.e import set table and from there transformation map will insert the record to target table.

All you have to do is to insert the data in staging table.

http://wiki.servicenow.com/index.php?title=Import_Sets

[kshitishchodank]

Hi Pradeep,

Thanks for all the help. I am a newbie but if I am not wrong the fields that are not sent in the REST request will be set as empty string in the import table. Also the fields which are sent as empty from the request are set as empty string. So how can we differentiate which fields are sent as empty and which fields are not sent in the request. I want to set as empty in target table only those fields which are sent as empty from the REST request.

[kshitishchodank]

Hi,

I have used the following approach. I am sending a string with space(" ") for any field that I want to set as empty string or NULL in the target table. With the help of transform map script I am checking for string with space and trying to set the target field to NULL. I am using the below script.

var gr =new GlideRecord('sys_dictionary');

gr.addQuery('name','staging-table-name');

gr.query();

while(gr.next()){

      var ele = gr.element;      

      var val = ele.replace('u_','');

      if(source.getValue(ele)!=null){

              gs.log(gr.element+":"+source.getValue(ele)+":"+val);

              if(source.getValue(ele) == " "){

                      gs.log("in loop for "+ele);

                      ttarget.setValue(val,"");

              }

              else

                      target.setValue(val,source.getValue(ele));

      }

}

The problem is I am not able to set the value in target table. The condition is getting satisfied and it is going inside the loop. The value that is set in the target table field is same as the value that is sent from the REST Api.   Any help would be really appreciated.

Thanks ,

Kshitish

[kshitishchodank · Accepted solution]

I am using onBefore script type and setting target value , instead I should have set source value. Changing that solved the problem. Thanks for all your help.