Date not being set to NULL: Difference between revisions
No edit summary |
No edit summary |
||
Line 21: | Line 21: | ||
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: 12345 | ||
2018-07-20 00:44:07 [SYSTM]:[WARN ]:[24444] Skipping column [expiry_date]. Source value is NULL for record: 34567 | 2018-07-20 00:44:07 [SYSTM]:[WARN ]:[24444] Skipping column [expiry_date]. Source value is NULL for record: 34567 | ||
</pre> | |||
In order to rectify this, you will need two things: | In order to rectify this, you will need two things: | ||
1. A post Import script that says: | |||
<pre> | <pre> | ||
Line 30: | Line 31: | ||
</pre> | </pre> | ||
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: | |||
<pre> | <pre> |
Latest revision as of 14:14, 7 September 2018
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;