Using a "Field Name" field type in a UI Action GlideRecord query

[kevinray]

Uggg...I'm so tried of staring at this code and not figuring it out. I'm sure it's simple. please help!!!

I have a form. On the form I have a "Table Name" type field, a "Field Name" type field, and a "Conditions" type field.

What i'm trying to do is create a set of records (on a different table) based on the information entered in those three fields and the"Field Name" has to be a field that represeted the 'user'. So for example,

the table could be "cmdb_ci_computer", (field name is u_table)
the conditions could be "manufacturer=aa0a6df8c611227601cd2ed45989e0ac^os=Windows XP Professional^EQ" (field name is "u_conditions")
the "Field Name" could be "assigned_to" (field name is u_associated_user_field)

So for every person that has a record on the computer table that matches those conditions, create a record in table X.

This query totally works when i hard code the field name and "not Null Query" line to "assigned to" because the cmdb_ci_computer table has the "assigned_to" field. The issue is that the user reference of a table isn't always called "assigned to" so I have to let the select the appropriate column on the table that represents a person.

This works:

//Set record to indicate Campaign has started
var gdt1 = new GlideDateTime();
current.campaign_initiated = true;
current.u_start_date_time = gdt1;
current.update();



var qLookup = new GlideRecord(current.u_table.toString());
qLookup.addEncodedQuery(current.u_conditions);
qLookup.addNotNullQuery(current.assigned_to);
qLookup.query();

while (qLookup.next()){
	var cInsert = new GlideRecord('x_26677_campaign_m_campaign_instance');
	cInsert.initialize();
	cInsert.u_agent_instructions = current.u_agent_instructions;
	cInsert.u_campaign_details = current.u_campaign_details;
	cInsert.u_campaign = current.sys_id;
	cInsert.u_customer_name = qLookup.assigned_to;
	
	cInsert.insert();
}

I'm able to get the table name in the query using current.u_table.toString() and this works fine. Figured it would work the same way for the Field Name field (on the qLookup AND the cInsert lines), but it doesn't.

This doesn't work

//Set record to indicate Campaign has started
var gdt1 = new GlideDateTime();
current.campaign_initiated = true;
current.u_start_date_time = gdt1;
current.update();



var qLookup = new GlideRecord(current.u_table.toString());
qLookup.addEncodedQuery(current.u_conditions);
qLookup.addNotNullQuery(current.u_associated_user_field.toString());
qLookup.query();

while (qLookup.next()){
	var cInsert = new GlideRecord('x_26677_campaign_m_campaign_instance');
	cInsert.initialize();
	cInsert.u_agent_instructions = current.u_agent_instructions;
	cInsert.u_campaign_details = current.u_campaign_details;
	cInsert.u_campaign = current.sys_id;
	cInsert.u_customer_name = qLookup.u_associated_user_field.toString();
	
	cInsert.insert();
}

then i tried to do it as a function.

Still dont work.....

//Set record to indicate Campaign has started
var gdt1 = new GlideDateTime();
current.campaign_initiated = true;
current.u_start_date_time = gdt1;
current.update();

createInstance(current.u_associated_user_field.toString());

function createInstance(uName){
var qLookup = new GlideRecord(current.u_table.toString());
qLookup.addEncodedQuery(current.u_conditions);
qLookup.addNotNullQuery(uName);
qLookup.query();

while (qLookup.next()){
	var cInsert = new GlideRecord('x_26677_campaign_m_campaign_instance');
	cInsert.initialize();
	cInsert.u_agent_instructions = current.u_agent_instructions;
	cInsert.u_campaign_details = current.u_campaign_details;
	cInsert.u_campaign = current.sys_id;
	cInsert.u_customer_name = qLookup.uName;
	
	cInsert.insert();
}
}

[The SN Nerd]

You should not dot-walk in a GlideRecord query loop.

What you are seeing is expected behaviour - and here is why:

A dot walked reference field value is actually a GlideElement object.

https://developer.servicenow.com/app.do#!/api_doc?v=jakarta&id=c_GlideElementAPI

For example:

var ge = current.assigned_to; //ge is a GlideElementReference object
var strRef= current.getValue('assigned_to'); // strRef is a string containing a sys_id
var str = current.state.toString() // str is a string

Since GE is an object, every time you return it, you are just returning what is essentially a memory pointer.
Every record looped will be populated with the last value in that address (the last value from the last qLookup record looped).

[kevinray]

I really appreciate the reply and telling me what i'm doing wrong, but would really really appreciate it if someone could tell me how to do it right.

How do I put the Field Name value in to the query?

[The SN Nerd]

I think part of the confusion here is that the query you say works, doesn't actually work.

Add a .getEncodedQuery() call and you will see that your conditions have a logical error.

For example, lets say I have an incident record.
I am trying to make a query based off the Incident assigned to

var gr = new GlideRecord('incident');
gr.addEncodedQuery("active=true^assigned_to!=");
gr.addNotNullQuery(current.assigned_to);
gr.query();
gs.addInfoMessage(gr.getEncodedQuery());

active=true^assigned_to!=^46b87022a9fe198101a78787e40d7547!=NULL

Your query is saying, a string value is not equal to null.
This is always true, so it is redundant and does nothing to your query.

It will still return results.

I am not quite sure what you are trying to do in your first notNullQuery...

The output of getEncodedQuery() should reveal what is wrong with your queries that "don't work"

[nateanderson · Accepted solution]

 Hope this helps. You were super close! 

Pablo was in the right direction. 

Also... If you want your "Field Name List" to be shorter...

If you go into the dictionary of your "Field Name" (associated user) field. And add the attribute: types=reference   it'll only list reference fields in the field pick list. Sadly, i don't see a way to limit it down to "fields referencing the user table" but this should help... 

Also the types attribute, can have a semi-colon separated list of "field types".. so you could do: types=reference;glide_list;integer;string;glide_date_time     etc... 

Here is... what should be a working example... 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

//Set record to indicate Campaign has started var gdt1 = new GlideDateTime(); current.campaign_initiated = true; current.u_start_date_time = gdt1; current.update(); var tableName = current.u_table.toString(); var conditions = current.u_conditions.toString(); var associatedUserField = current.u_associated_user_field.toString(); var qLookup = new GlideRecord(tableName); qLookup.addEncodedQuery(conditions); qLookup.addNotNullQuery(associatedUserField); qLookup.query(); while (qLookup.next()){ var cInsert = new GlideRecord('x_26677_campaign_m_campaign_instance'); cInsert.initialize(); cInsert.u_agent_instructions = current.u_agent_instructions; cInsert.u_campaign_details = current.u_campaign_details; cInsert.u_campaign = current.sys_id; /* this is really the only line i saw wrong... cInsert.u_customer_name = qLookup.u_associated_user_field.toString(); it should be... */ cInsert.u_customer_name = qLookup.getValue(associatedUserField); /* so if we were to type out what this call looks like if we were to replace the var with the actual string we're storing above... it would look like: cInsert.u_customer_name = qLookup.getValue('assigned_to') for example. alternatively, you can use bracket notation... qLookup[associatedUserField] is the same as qLookup['assigned_to'], which is the same as current.assigned_to What's cool about this; is that we're actually getting the GlideElement object; Which if it's a reference field then we can do stuff like.. var associatedUser = qLookup[associatedUserField].getRefRecord(); gs.info('Email address is: ' + associatedUser.email + ', Manage is: ' + associatedUser.manager.getDisplayValue()); */ cInsert.insert(); }