Using GlideQuery() to query a table and return values

[jhuggins]

Hello. We currently have a requirement that requesters select a hardware asset in a reference list on a catalog item. At a certain point in the flow, that catalog item and other information is passed to a script include. The script include should receive the passed information, query the hardware asset table to make sure the selected asset is assigned to the requested for of the request, and return the result.

I am using the GlideQuery() API and have followed the documentation as far as building the query and using correct syntax in the script include [1]. However, my query is not returning any values as we can see in our log files [2][3], despite the correct asset sys_id and requested for information being passed in. 

Any help is appreciated to figure out why the query is not returning any results. I have tried just using the first .where condition to match the sys_id of the asset record to the sys_id of the passed in asset, but am receiving the same results.

Thanks in advance

[1]

var UnassignAssetFromReqFor = Class.create();

var removeAsset;
var assigned_to;

UnassignAssetFromReqFor.prototype = {
    initialize: function() {
    },
	
	unassignAsset: function(removeAsset, assigned_to) {
		gs.log('Asset: ' + removeAsset); // verify asset sys_id passed correctly
		gs.log('Assigned to: ' + assigned_to.getDisplayValue()); // verify requested for of request is passed correctly
		
		var asset = new global.GlideQuery('alm_hardware') // initialize query
			.where('sys_id', removeAsset) // only query items where sys_id is the same as the passed asset
			.where(new GlideQuery('alm_hardware') // AND 
				.where('assigned_to', assigned_to) // the assigned to of the asset is the same as the passed requested for
 				.orWhere('parent', removeAsset)) // OR the parent of the asset is the same as the passed asset
 			.select('u_asset_number', 'asset_tag', 'parent')
			.toArray(100);

		gs.info(JSON.stringify(asset, null, 2)); // verify query results
		
		gs.log('these are the assets: ' + asset); // verify query results

        },
	
	type: 'UnassignAssetFromReqFor'
};

[2]

output of 'gs.info(JSON.stringify(asset, null, 2));' is []

[3]

these are the assets: (this part is empty as there are no results found)

[jhuggins]

This is not for a scoped app.

I  have also tried using just the 

.where('sys_id', removeAsset) // only query items where sys_id is the same as the passed asset

line and was still getting the same output.

[Allen Andreas · Accepted solution]

Hi,

Ok. I said that because you have a global.GlideQuery in there.

Past all of that, you'd want to check your values for what you're passing as function params. With logging, what are you getting for removedAsset and do you need to add .toString() to ensure it's in appropriate format?

The whole thing with GlideQuery is that it's meant to totally fail if a certain piece is not correct. GlideRecord, would just ignore it.

So something must be wrong with perhaps the params (if you've temp removed the nested GlideQuery).

Like I've used just this...

var asset = new global.GlideQuery('alm_hardware') // initialize query
.where('sys_id', '00a96c0d3790200044e0bfc8bcbe5dc3')
.select('asset_tag', 'parent')
.toArray(5);
gs.info(JSON.stringify(asset));

and I get a result like this:

Obviously I don't have your custom field so I removed it, but the point here is that it works.

So as we narrow things down, it comes down to either:

1. removedAsset isn't anything helping the query actually happen or isn't in the right format that GlideQuery expects (a string sys_id in your case).
2. The custom field is perhaps named wrong (although the query in background "worked"...did something, it threw errors and wasn't a clean array -- so I'm listing that here as a probably cause).

I'd recommend checking those two things 🙂

Please mark reply as Helpful/Correct, if applicable. Thanks!

[jhuggins]

My issue seemed to be not making the removeAsset sys_id into a string. Running similar test scripts to Hitoshi, I am getting the correct output, now.

.where('sys_id', removeAsset.toString())
.where(new GlideQuery('alm_hardware')
	.where('assigned_to', assigned_to.toString())
	.orWhere('parent', removeAsset.toString()))
.select('u_asset_number', 'asset_tag', 'parent')
.toArray(100);

*** Script: Asset: 5e46fec61b5409107e230ed2cd4bcb7b
*** Script: Assigned to: undefined
*** Script: [
  {
    "u_asset_number": "63870",
    "asset_tag": "ESDD-63870",
    "parent": null,
    "sys_id": "5e46fec61b5409107e230ed2cd4bcb7b"
  }
]

How would I go about updating fields using GlideQuery? 

.where('sys_id', removeAsset.toString())
.where(new GlideQuery('alm_hardware')
	.where('assigned_to', assigned_to.toString())
	.orWhere('parent', removeAsset.toString()))
.select('u_asset_number', 'asset_tag', 'parent')
.update({ 
	asset_tag: 'u_asset_number',
	parent: '', 
	assigned_to: ''}, ['asset_tag', 'parent', 'assigned_to'])
.toArray(100);

I'm following the '.update()' examples in the docs but am getting the following error.

*** Script: Asset: 5e46fec61b5409107e230ed2cd4bcb7b
*** Script: Assigned to: undefined
*** Script: undefined

[Allen Andreas]

Hi,

Yeah, I ran tests as well in my above reply, which brought me to the two points mentioned above.

In any case, this is a second question to your main question as I don't believe this was about updating a record (my apologies if I missed that and didn't cover it).

With that said, the syntax I'm familiar with is something like this:

new GlideQuery('sys_user')
.where('sys_id', userId)
.update({ email: 'admin@example.com', active: true });

where you're using JSON as the payload essentially to the .update() method and a key:value pair format such as that. I'm unsure of the array of field names after the object is closed, what that would do. In numerous examples from the below poster and yourself, the assigned_to was showing a undefined. So you'd want to ensure it's in the appropriate format to then update those fields accordingly.

In your case, I would imagine you'd use something like:

.update({ 
asset_tag: 'u_asset_number',
parent: 'value', 
assigned_to: 'value'});

More documentation here: https://developer.servicenow.com/blog.do?p=/post/glidequery-p2/

If there's still an issue, you may want to post another question to have a discussion around the update portion.

Please mark reply as Helpful/Correct, if applicable. Thanks!

[Hitoshi Ozawa]

Hi jhuggins,

Tried in my instance and the script in the question is returning result if I add a return statement.

SQL corresponding to the query is as follows:

SELECT u_asset_number, asset_tag, parent FROM alm_hardware WHERE sys_id = removeAsset and (assigned_to = assigned_to OR parent = removeAsset);

The function is missing a return statement so null is being returned.

var UnassignAssetFromReqFor = Class.create();
UnassignAssetFromReqFor.prototype = {
    initialize: function() {},
    unassignAsset: function(removeAsset, assigned_to) {
        gs.log('Asset: ' + removeAsset); // verify asset sys_id passed correctly
        gs.log('Assigned to: ' + assigned_to.getDisplayValue()); // verify requested for of request is passed correctly

        var asset = new global.GlideQuery('alm_hardware') // initialize query
            .where('sys_id', removeAsset) // only query items where sys_id is the same as the passed asset
            .where(new GlideQuery('alm_hardware') // AND 
                .where('assigned_to', assigned_to) // the assigned to of the asset is the same as the passed requested for
                .orWhere('parent', removeAsset)) // OR the parent of the asset is the same as the passed asset
            .select('u_asset_number', 'asset_tag', 'parent')
            .toArray(100);

        gs.info(JSON.stringify(asset, null, 2)); // verify query results

        gs.log('these are the assets: ' + asset); // verify query results
        return JSON.stringify(asset);
    },
    type: 'UnassignAssetFromReqFor'
};

Execution result. Took "u_asset_number" out from select statement

Test script:

var removeAsset= 'eac1ba8837f3100044e0bfc8bcbe5d4d';
var assigned_to = '5f72e70ac0a8010e003e479c72d75d47';

var a = new UnassignAssetFromReqFor();
var asset = a.unassignAsset(removeAsset, assigned_to);
gs.info(asset);

Result:

*** Script: Asset: eac1ba8837f3100044e0bfc8bcbe5d4d
*** Script: Assigned to: undefined
*** Script: [
  {
    "asset_tag": "P1000029",
    "parent": null,
    "sys_id": "eac1ba8837f3100044e0bfc8bcbe5d4d"
  }
]
*** Script: these are the assets: [object Object]
*** Script: [{"asset_tag":"P1000029","parent":null,"sys_id":"eac1ba8837f3100044e0bfc8bcbe5d4d"}]