Clear DateTime field during Transform

[Todd_Goodhew]

Thanks in advance for any help. This one has me stumped.

I have a DateTime field on a target table.  During import, I want to clear this field of any value (make it NULL) during import based on the value of a corresponding source field.  

This should be pretty simple with a field map script on the transform, but no matter what I do, I can't seem to clear the target field of a value.  I can set it to another date/time value just fine.  I just can't seem to clear it of any value.

I know the source values exist in the source data and I know that part of the if statement passes true, previous version of the code had logging in it to validate this piece.

Any thoughts?

Here's the code:

answer = (function transformEntry(source) {

	var dtUtil = new DateTimeUtils();
	var value;
	
	// if the account is set to never expire or has no value, then clear out the field
	if ( source.u_accountexpires == '9223372036854775807' || source.u_accountexpires == '0' || source.u_accountexpires === undefined){
		value = '';  // return an empty string to clear out the value.
	} else {
	// otherwise convert the value from the MS Interger8 format to GlideDateTime format and set the target field with it
	value = dtUtil.int8ToGlideDateTime(source.u_accountexpires); 
	}

return value;
	
})(source);

[ccajohnson]

Is this in a scoped application? If it is, is the script include in the same scope? If it is not, perhaps you need to add the application scope to the script include call: global.DateTimeUtils();

[Michael Ritchie]

In your import set table, what is the field type for u_accountexpires?  A string or an integer?  Your if condition may not be evaluating correctly because you are comparing an integer to a string and may need to adjust.  I would also suggest using gs.nil() function instead of undefined.

Maybe the following will work:

answer = (function transformEntry(source) {

	var dtUtil = new global.DateTimeUtils();
	var value;
	
	// if the account is set to never expire or has no value, then clear out the field
	if ( source.u_accountexpires == '9223372036854775807' || source.u_accountexpires == '0' || gs.nil(source.u_accountexpires)){
		target.u_ad_account_expires = '';
	} else {
		// otherwise convert the value from the MS Interger8 format to GlideDateTime format and set the target field with it
		value = dtUtil.int8ToGlideDateTime(source.u_accountexpires); 
	}

return value;
	
})(source);

[Todd_Goodhew]

This is a global app.  I'm just importing data from LDAP and importing into the sys_user table.  Nothing special.

The source field "u_accountexpires" is of type String.  the data that comes from AD is in in the Integer8 format.  I know the IF statement works.

the most current code has test provided by Sachin that do work, but generate error messages during import.

		//target.u_ad_account_expires = 'NULL';
		//target.setValue("u_ad_account_expires",'');

[Abhinav21]

@Todd.Goodhew : I'm struggling with the same issue. Did you get it resolved, please share the solution.

[Todd_Goodhew]

Unfortunately, I cannot remember how I resolved this.  That instance and its code is long gone now.

I know I did get it working.