How to set a Date field to empty when transforming if the source date field in Excel is empty?

[mnaranjor]

I am relatively new to ServiceNow and I am having the following issue when importing data to a table. I have 7 Date fields in my transform map: u_lease_expires, u_purchase_date, u_retired_date, u_warranty_expiration, u_picked_by_beneficiary, u_order_received, and u_loaner_expires.

I noticed that if the field in the Excel file has a value, after the transformation, the correct value transfers to the target table. However if the date field in the Excel value is empty, after the transformation, some target date fields in the table get the following value: 1903-08-13. 

I matched all the Date fields in the transform map to yyyy-MM-dd format and did the same in Excel. You can see in the first screenshot that this worked by looking at the Purchased Date column. I am running a Transform Script onBefore to clean most of the fields in the target table before adding the data from Excel:

I don't know why the rest of the Date fields are getting this value and I am at my wit's end here. Will appreciate some help.

Miguel

[mnaranjor]

This is the change made to Transform script:

target.substatus = '';
target.u_lease_expiration = '';
target.location = '';
target.stockroom = '';
target.u_stockroom_number = '';
target.u_stockroom_section = '';
target.u_section_shelf = '';
target.acquisition_method = '';
target.delivery_date = '';
target.purchase_date = '';
target.retirement_date = '';
target.u_picked_by_beneficiary = '';
target.warranty_expiration = '';
target.managed_by = '';
target.assigned_to = '';

[Abhinay Erra]

How about this

in the onbefore script do a GlideRecord query and set the values there. Tranform log will show all the rows are ignored, but it should have still updated the values

var gr= new GlideRecord(target.getTableName());

gr.get(target.sys_id);

gr.u_lease_expiration = '';
gr.location = '';
//set rest of fields

gr.update();

[mnaranjor]

Well, I tried your code and still gives the same result...I might need to delete all those records and test again with a few, but it would be nice to know what is causing this.

[Anisse Mahtat]

Hi, I have this issue, have-you a good answer ?