Using GlideRecord to query values from custom table in Script Include

[JohnnieGross]

I have a custom table where I am submitting a query via a script include.   This script include is called from a client script in the Quote object (sn_quote_mgmt_core_quote).  I am working in the native UI as admin and not in the workspace at this point for CSM.   However, when sending the query to the custom table, it always returns null for the value of the field I'm trying to lookup and return in my client script.  This works great if I point it to a non-custom table that we did not create, like customer_account or customer_contact or sys_user etc. where I was hard-coding a Sys ID in the Include Script along with the non-customer table and the return value worked every time.  I tried several custom tables where I also hard-coded the Sys ID, table and return fields and it just doesn't return anything - always null.  I've adjusted the client script to return values from a non-custom table along with the script includes - and it always works as expected with the non-custom table.  

I'm wondering if I need to be doing something to my custom table or is it an ACL maybe or something that does not let the GlideRecord query the custom table to return a field value?  That is my guess - but perhaps I'm doing something else wrong.

My script include looks like this, referencing the custom table.

var getROMAssumptionsInfo = Class.create();

getROMAssumptionsInfo.prototype = Object.extendsObject(AbstractAjaxProcessor, {

    romAssumptionsDetatils: function() {

        var cr = this.getParameter('sysparm_value');

        var gr = new GlideRecord('u_rom_assumptions');

        gr.addQuery('sys_id', cr);

        gr.query();

        if (gr.next()) {

            return gr.u_rom_assumptions_details;

        }

    },

    type: 'getROMAssumptionsInfo'

});

Thanks for any guidance!

John

[Nishant8]

Hello @JohnnieGross, Can you please try to execute the SI from Background Script and verify whether you receive desired output?

you can make your SI callable from CS and Background script with a small change as below:

var getROMAssumptionsInfo = Class.create();
getROMAssumptionsInfo.prototype = Object.extendsObject(AbstractAjaxProcessor, {
    romAssumptionsDetatils: function(customSysID) {
        var cr = this.getParameter('sysparm_value') || customSysID;     
        var gr = new GlideRecord('u_rom_assumptions');
        gr.addQuery('sys_id', cr);
        gr.query();
        if (gr.next()) {
            return gr.u_rom_assumptions_details;
        }
    },
    type: 'getROMAssumptionsInfo'
});

[JohnnieGross · Accepted solution]

I was able to resolve my issue.  Apparently, if the lookup table is made in the Global scope and has the prefix of "u_", then it will not work.  I recreated the table in the application Quote Management Data Model, the system prefixed the table with the prefix for Quote Management and therefore is added to an Application.  Once I did this an updated all my references to that new lookup table and updated the Script Includes, it started working.  I'm not sure why this particular functionality was ignoring a table made in Global, I'm not sure.  But it's working for me now.  Thanks to everyone who helped me navigate this issue.

[stevemarkovick]

 Thanks for sharing your solution, John! Indeed, custom tables in the Global scope (especially with "u_" prefix) can sometimes behave unexpectedly with GlideRecord queries, especially when called from scoped applications. Good job on figuring it out by moving the table into the correct application scope! Just to add — in similar cases, it's also useful to check for ACLs, Business Rules, or protection policies on custom tables, as they might silently block data access.