GlideQuery .select(Fields) is not working as expected

[Surya_Prakash]

Hi All,

I have a requirement where I'm passing set of sys_id's in a order to get some fields using the GlideQuery.select(Fields) method.

My expected output was to get the result in same order of the sys_id I'm passing in the query, but the code is returning data's based on some other order.

Sample code:

var sysID = {1,2.3,....100};

var selectedFields = [a,b,c,d];

var gq = GlideQuery(table);

gq = gq.where('sys_idIN' + sysID);//Till here the code was going on the same order as the sysid I passed.

gq = gq.select(selectedFields);

gs.info("ANS: " + gq); //Here the sysid is getting mixed up and showing data on some random order

Is it possible to get the output also in the same order of sys_id which was passed in the script?

Thanks,

Surya. P

[Rajesh Chopade1 · Accepted solution]

hi @Surya_Prakash 

Try bellow script and check once:

var sysIDs = ['sys_id_1', 'sys_id_2', 'sys_id_3', /* ... up to 100 */];
var selectedFields = ['a', 'b', 'c', 'd'];

var gq = new GlideQuery('your_table_name');
gq = gq.where('sys_id', 'IN', sysIDs); 

var results = gq.select(selectedFields).toArray();

// Create a temporary map to hold the results keyed by sys_id
var resultMap = {};
results.forEach(function(record) {
    resultMap[record.sys_id] = record;
});

// Output results in the same order as the original sys_id array
var orderedResults = sysIDs.map(function(id) {
    return resultMap[id]; // Retrieve from the map
}).filter(function(record) {
    return record !== undefined; // Filter out any undefined values if sys_id was not found
});

// Log the ordered results
orderedResults.forEach(function(result) {
    gs.info("Result for sys_id " + result.sys_id + ": " + JSON.stringify(result));
});

i hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

rajesh

[Surya_Prakash]

Hi @Rajesh Chopade1,

Thanks for your reply, this was the solution I expected and with some minor changes to my code I was able to achieve the output.

Thanks,

Surya. P

[Surya_Prakash]

Hi @Rajesh Chopade1,

Thanks for the reply. Currently I'm getting the code in below format. Can you please help to just fetch the sysid's (Value) from the code.

GlideQuery<tablename> [
{
"type": "where",
"field": "sys_id",
"operator": "IN",
"value": [
"sysid 1",
"sysid 2",
"sysid 3"

],

},

]

Thanks,

Surya. P

[Rajesh Chopade1]

hi @Surya_Prakash 

Here you just simply focus on fetching the values from the "value" array.

Assuming you have input in a variable, you can use following code.

// Assuming this is your input data structure
var input = {
    "type": "where",
    "field": "sys_id",
    "operator": "IN",
    "value": [
        "sysid 1",
        "sysid 2",
        "sysid 3"
    ]
};

// Extracting sys_ids
var sysIds = input.value;

gs.info("Extracted sys_ids: " + sysIds.join(", ")); 
// output : Extracted sys_ids: sysid 1, sysid 2, sysid 3

//If you want to use these sys_id values in a GlideQuery for further operations, you can use the IN operator
var gq = new GlideQuery('tablename');
gq = gq.where('sys_id', 'IN', sysIds);
gq.select('sys_id'); // or any other fields you want