Query on a name-value pair field

[John Francis]

We see value is using a name-value pair field to contain various attributes on change requests.  Using this single field means we don't need to add several other custom fields.  The challenge now is that there doesn't seem to be a way to query (in a list view or report) on this field because the only options are "is", "is not", or "is anything".  At a minimum, I would like to be able to look for certain "name" values, perhaps using a "contains" selector.

While this type of field seems to add flexibility, not being able to query on it limits its usefulness.  Has anyone else used this field type and found a way to query on it for list views or reports?

[SwarnadeepNandy]

Hello @John Francis,

One way is to use a script to parse the name-value pair field and extract the values you want to query on. You can then use those values as filters in your list view or report. For example, you can use the following script to get the value of a specific name in the name-value ...1:

// Script to get the value of a specific name in the name-value pair field
var gr = new GlideRecord('change_request');
gr.addQuery('sys_id', 'sys_id_of_record_here');
gr.query();
if (gr.next()) {
  var nv_field = gr.nv_field; // assuming nv_field is the name of the name-value pair field
  var name = 'name_you_want_to_query_on'; // assuming this is the name you want to query on
  var value = nv_field[name]; // get the value of the name
  gs.print(name + ' = ' + value); // print the name and value
}

Another way is to use a scripted filter in your list view or report. A scripted filter allows you to write a script that returns true or false for each record, and only displays the records that return true. For example, you can use the following script to filter records that have a specific name-value pair in the name-value pair field:

// Scripted filter to filter records that have a specific name-value pair in the name-value pair field
var gr = new GlideRecord('change_request');
gr.addQuery('sys_id', current.sys_id); // current is the record being evaluated by the filter
gr.query();
if (gr.next()) {
  var nv_field = gr.nv_field; // assuming nv_field is the name of the name-value pair field
  var name = 'name_you_want_to_query_on'; // assuming this is the name you want to query on
  var value = 'value_you_want_to_query_on'; // assuming this is the value you want to query on
  if (nv_field[name] == value) { // check if the record has the desired name-value pair
    return true; // return true if it does
  }
}
return false; // return false otherwise

Hope this helps.

Kind Regards,

Swarnadeep Nandy

[jasonmarvin]

// Script to get the value of a specific name in the name-value pair field
var changeRequest = new GlideRecord('change_request');
changeRequest.addQuery('sys_id', 'sys_id_of_record_here');
changeRequest.query();
if (changeRequest.next()) {
var nameValueField = changeRequest.nv_field; // assuming nv_field is the name of the name-value pair field
var targetName = 'name_you_want_to_query_on'; // assuming this is the name you want to query on
var value = nameValueField[targetName]; // get the value of the name
gs.print(targetName + ' = ' + value); // print the name and value
}

// Scripted filter to filter records that have a specific name-value pair in the name-value pair field
var changeRequestFilter = new GlideRecord('change_request');
changeRequestFilter.addQuery('sys_id', current.sys_id); // current is the record being evaluated by the filter
changeRequestFilter.query();
if (changeRequestFilter.next()) {
var nameValueField = changeRequestFilter.nv_field; // assuming nv_field is the name of the name-value pair field
var targetName = 'name_you_want_to_query_on'; // assuming this is the name you want to query on
var targetValue = 'value_you_want_to_query_on'; // assuming this is the value you want to query on
if (nameValueField[targetName] == targetValue) { // check if the record has the desired name-value pair
return true; // return true if it does
}
}
return false; // return false otherwise

Certainly! Here are some improvements to your code:

1. Use a More Meaningful Variable Name:

javascriptCopy code

// Script to get the value of a specific name in the name-value pair field var changeRequest = new GlideRecord('change_request'); changeRequest.addQuery('sys_id', 'sys_id_of_record_here'); changeRequest.query(); if (changeRequest.next()) { var nameValueField = changeRequest.nv_field; // assuming nv_field is the name of the name-value pair field var targetName = 'name_you_want_to_query_on'; // assuming this is the name you want to query on var value = nameValueField[targetName]; // get the value of the name gs.print(targetName + ' = ' + value); // print the name and value } // Scripted filter to filter records that have a specific name-value pair in the name-value pair field var changeRequestFilter = new GlideRecord('change_request'); changeRequestFilter.addQuery('sys_id', current.sys_id); // current is the record being evaluated by the filter changeRequestFilter.query(); if (changeRequestFilter.next()) { var nameValueField = changeRequestFilter.nv_field; // assuming nv_field is the name of the name-value pair field var targetName = 'name_you_want_to_query_on'; // assuming this is the name you want to query on var targetValue = 'value_you_want_to_query_on'; // assuming this is the value you want to query on if (nameValueField[targetName] == targetValue) { // check if the record has the desired name-value pair return true; // return true if it does } } return false; // return false otherwise

2. Clarify Variable Names:

Use more descriptive names to improve code readability.

3. Comment Clarifications:

Added comments for better understanding of variable purposes.

4. Consistent Naming:

Ensured that the variable names are consistent throughout the code.

5. Removed Unnecessary Comments:

Removed comments that repeated the obvious or did not add significant information.

Remember to replace placeholder values like 'sys_id_of_record_here', 'name_you_want_to_query_on', and 'value_you_want_to_query_on' with actual values in your use case.