What is the issue with my query to table?

[ztb1997]

I have a script include that has a method I am calling from a catalog client script. In the script include method, it is throwing a couple of errors to my logs:

The customer_employer and parent_employer fields in the x_1234_dlr_reemplo_employer_relationship table are both references. These references appear to have those fields... let me know and I can provide images of tables and references in those tables. Here is my method in the script include:

getBusinessAccountsForPOA: function() {
        gs.info("getBusinessAccountsForPOA begins...");
        var directCompanies = [];
        var relatedCompanies = [];
        var arr = [];

        var userName = gs.getUserName();
        gs.info("getBusinessAccountsForPOA method invoked for user " + userName);

        var grCustomerContact = new GlideRecord('customer_contact');
        grCustomerContact.addEncodedQuery('user_name=' + userName + '^user_name.locked_out=false');
        grCustomerContact.query();
        gs.info("customer_contact query executed");

        while (grCustomerContact.next()) {
            // Add the direct company tax ID
            var companyTaxId = grCustomerContact.company.tax_id.getDisplayValue();
            var companyDBA = grCustomerContact.company.name;
            directCompanies.push({
                value: companyTaxId,
                label: companyTaxId + ' - ' + companyDBA
            });
            gs.info("Added base company: " + companyTaxId + ' - ' + companyDBA);

        }

        var grEmployerRelationship = new GlideRecord('x_1234_dlr_reemplo_employer_relationship');
        grEmployerRelationship.addEncodedQuery(
            'parent_employer.company.tax_id=' + companyTaxId +
            '^employer_relationship_type.descriptionINPower of Attorney Benefits,Power of Attorney Tax,Power of Attorney Limited Tax'
        );
        grEmployerRelationship.query();
        gs.info("We are querying to find where parent_employer tax_id matches what we got in customer contact: " + companyTaxId);
        while (grEmployerRelationship.next()) {
            gs.info("Found a record in employer_relationship table where parent_employer tax_id matches what we got in customer contact: " + companyTaxId + ' new taxid: ' + grEmployerRelationship.customer_employer.company.tax_id);
            var parentTaxId = grEmployerRelationship.customer_employer.company.tax_id.getDisplayValue();
            var dba = grEmployerRelationship.customer_employer.company.tax_id;
            var label = parentTaxId;

            if (dba) {
                label = parentTaxId + ' - ' + dba;
            }

            relatedCompanies.push({
                value: parentTaxId,
                label: label
            });
        }
        return JSON.stringify(relatedCompanies);
    }

What am I doing incorrect? Like I said, let me know if you would like to see the tables. I can see I am getting information from customer_contact table in my logging. I just don't appear to be grabbing the data for the relatedCompanies. Thanks!

[ztb1997]

I ended up getting logging showing up for this specific log, which indicates that we are obviously finding records as I placed this log within logic that requires at least one match. However, it doesn't return the customer_contact.company.tax_id. Even when logging, I added that customer tax_id, and it is just blank:

gs.info("Found a record in employer_relationship table where parent_employer tax_id matches what we got in customer contact: " + companyTaxId + ' new taxid: ' + grEmployerRelationship.customer_employer.company.tax_id);

And here is the actual log (companyTaxID is painted over intentionally):

[Ankur Bawiskar]

@ztb1997 

Is it working with admins?

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Robert H]

Hello @ztb1997 ,

My first thought is that there is no field named "tax_id" on the table that "customer_employer" and "parent_employer" are referencing.

And you're getting results in your logging because, by default, ServiceNow will ignore the invalid part of the query and just check the other conditions.

You mentioned that you already checked that the field is present but feel free to share the screen shots of the relevant Dictionary entries.

Is the table where the "customer_employer" and "parent_employer" are stored in the same scope as the Script Include? If they're not there might be a small chance that the issue is due to missing cross scope access, although the error would look different.

Regards,

Robert

[ztb1997]

Hello @Robert H  , 

The table where "customer_employer" and "parent_employer" are stored are in fact in the same application scope as the Script Include.

Here are screen shots of the table I am accessing showing the columns:

Here is the Employer table (referenced in the above):

That Business column's name is "company" even though the label is Business. Finally here you can see what is the tax_id column on that Business reference (note that this application scope is Global, which is different from everything else):

Perhaps I need to insert a new record into [sys_scope_privilege] to give access between application scopes for this specific table? See: How to access Global Scope Table from Scoped Appli... - ServiceNow Community

Although, I believe it looks like I should already be able to, Source Scope is the application scope my script include and catalog client script/variable set/record producer/all other tables I'm using are in:

Regards,

Zachary