GlideQuery sets to "NULL" the value of a reference field when you try to clear it.

[Ramon Cordova]

Hi all!

I've been using GlideQuery for a while and came across a "potential" problem today.

So, when working with GlideQuery and Reference fields, we need to use "NULL" to indicate we want an empty field. It is mentioned in this video.
When we do an update to try to clear the value of a reference field we need to do something like this:

//To clear the value of a reference field we do either:
new global.GlideQuery( "<table>" )
    .where( "sys_id", "<sys_id>" )
    .update( { "u_custom_field": null } );
//OR
new global.GlideQuery( "<table>" )
    .where( "sys_id", "<sys_id>" )
    .update( { "u_custom_field": "NULL"} );

//This will throw a nice error.    
new global.GlideQuery( "<table>" )
    .where( "sys_id", "<sys_id>" )
    .update( { "u_custom_field": "" } );

The problem occurs when you have a business rule running before insert and then you do something like current.getValue("u_custom_field") to get the field value, you get "NULL". This can lead to some problems because there might be code that is expecting a falsy value or an empty string, but oh surprise!! "NULL" is not a falsy value, it is a string.

Unlike GlideQuery, when you use GlideRecord to do the same, the code is something like this:

var current = new GlideRecord( "<device>" );
if ( current.get( "<sys_id>" ) ){
    current.setValue( "u_custom_field", "");
    current.update();
}

and then, when the BR's code runs the statement: current.getValue("u_custom_field") you get null (not a string) which is a falsy value, so code like this works:

var referenceFieldValue = current.getValue("u_custom_field"); //null
if( !referenceFieldValue ){
    // do something
}

var referenceFieldValue = current.u_custom_field; //GlideElement
if( referenceFieldValue == "" ){
    // do something
}

Now, with GlideQuery, the above code would break and to solve it we need to update the BR's code to account for that "NULL" value:

var referenceFieldValue = current.getValue("u_custom_field"); //null
var isReferenceFieldValue = referenceFieldValue && referenceFieldValue !== "NULL";
if( !isReferenceFieldValue ){
    // do something
}

In my experience, I have never seen someone's code accounting for "NULL" explicitly in the code.

One of the main reasons I use .getValue() in a GlideRecord object is to keep consistency in the data types retrieved when you get the value of a field.

I'd like to get your opinions on this thread.

Hope someone in ServiceNow sees this post and comes up with a better solution or workaround.

Thanks!