What are some reasons that two different reference fields referencing the same table with the same value would not match on a "!=" or a "DOES NOT CONTAIN" GlideRecord query operator?

[bcronrath]

I've been trying to do some cleanup due to an integration that used a custom company field instead of the default one on the incident table.   I'm trying to find the places where company value is different between the two fields where they are not null.   They are both reference fields referencing the core_company table.   I am running into some confusing behavior.   I have noticed that using either the != or DOES NOT CONTAIN operator is giving me only incidents where the company does in fact actually match the sys_id value from the core_company table.   When I use the = or CONTAINS operator, I get no results.   I'm completely stumped right now as to why this is.   I have double and triple checked that the values that are output from this gs.print are exactly the same.   Clearly something in the system considers them to be different.   Here is my script:

var gl = new GlideRecord('incident');

gl.addQuery('u_customer_type','external');

gl.addQuery('company','!=','u_company');

gl.addNotNullQuery('company');

gl.addNotNullQuery('u_company');

gl.query();

while(gl.next()){

  gs.print(gl.number + " Company: " + gl.company + " u_company: " + gl.u_company);

}

Does anyone have any thoughts?

Best regards,

Brian

[The SN Nerd · Accepted solution]

Please try the following:

var gl = new GlideRecord('incident');

gl.addQuery('u_customer_type','external');

gl.addEncodedQuery('companyNSAMEASu_company');

gl.addNotNullQuery('company');

gl.addNotNullQuery('u_company');

gl.query();

while(gl.next()){

   gs.print(gl.number + " Company: " + gl.company + " u_company: " + gl.u_company);

}

As I understand it, your query was saying, in English

SYS ID of COMPANY is not equal to "u_company"

which of course is wrong ! Alas, you were getting sys_ids that were the same!

NSAMEAS is "is different"

If ever having troubles with queries, do it in a list then copy the query to clipboard.

Then, write you code and output 'current.getEncodedQuery()'

[bcronrath]

Thank you Paul!   Looks like that did it.

Forgive my newbie-ness, but would you be able to elaborate on why the normal operators do not work for these referenced fields?   My confusion comes from the fact that both u_company and company reference a company value, so would not the referenced value between them be the same when the string is matching?   I just want to make sure I understand why I wasn't able to query in the way I was before, so that I cannot make similar mistakes in the future.

Thank you!

Edit:   Oh and just to elaborate - both company and u_company are storing a sys_id from the core_company table.

[The SN Nerd]

Using GlideRecords to Query Tables may be helpful.

In a nutshell, '=' and '!=' expect the syntax FIELD, OPERATOR, VALUE.
Reference fields are storied as SYS_ID's in the database.

Lets say my Incident has a company value of '324j23h4jk32gj4h32' in the database, your query therefore says

Is the Incidents company value ( 324j23h4jk32gj4h32) exactly the same as the string 'u_company'?

The encoded query 'NSAMEAS' expects FIELD, OPERATOR, FIELD.

[bcronrath]

Ahhh ok that makes perfect sense now!   Thank you so much Paul, this is very helpful.