Date not being set to NULL

Status:	Published
Version:	1.0
Authors:	HTL QA
Applies to:	All Supportworks ESP versions

Date not being set to NULL

The Data Import Manager is used to import into Supportworks and update existing data. In some cases, the date fields may have already been populated with an epoch value and if using Mysql, a new data import would reset that dae value to NULL. However, if using MSSQL, the epoch date value will not be able to change back to NULL. If for example you have an expiry date for a Configuration Item in Supportworks and later in the CI data source (source import) you set that CI expiry date to NULL, Mysql will be able to update the Supportworks expiry date to NULL but MSSQL will not be able to do that. Instead you will get the following warnings in the data import logs:

2018-07-20 00:44:07 [SYSTM]:[INFO ]:[24444] SQL import started
..
2018-07-20 00:44:07 [SYSTM]:[WARN ]:[24444] Skipping column [expiry_date]. Source value is NULL for record: 12345
2018-07-20 00:44:07 [SYSTM]:[WARN ]:[24444] Skipping column [expiry_date]. Source value is NULL for record: 34567

In order to rectify this, you will need two things:

1. A post Import script that says:

UPDATE config_itemi SET expiry_date = NULL WHERE expiry_date = 0;

2. A Value Added Transformation value to process the expiry date. If, for example, the source of the data being imported is an excel sheet in the Import script, locate the Value added transformation on the expiry_date field and add the follwoing javascript to it:

if (typeOf xls.expiry_date == undefined || xls.expiry_date = '' || xls.expiry_date = 0){
0;
} else xls.expiry_date;