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!

[Robert H]

Hello Zachary @ztb1997 ,

Thank you for the details. Now it's clear: 

Your script is currently written as if the "tax_id" were a field on the "Employer" table. But as per your screen shots it's actually a field on the "Business" table, which is referenced in the "Business" [company] field.

So you need to do one more dot-walk step in your script:

grEmployerRelationship.addEncodedQuery(
            'parent_employer.company.tax_id=' + companyTaxId // ... and so on

instead of

grEmployerRelationship.addEncodedQuery(
            'parent_employer.tax_id=' + companyTaxId // and so one

Regards,

Robert

[ztb1997]

Apologies @Robert H  , originally at the time of the post that is what my code was, but I have since added that dotwalk prior to my previous replies. I have updated original post. I am still at the same point of not being able to get that tax_id though, as that didn't solve the issue unfortunately 😞 Thanks for noticing that, though.

I believe this means it is likely there is an ACL/Role that may be missing for the user to access the table.

Apologies and regards,

Zachary

[Robert H]

Hello Zachary @ztb1997 ,

Ok, thanks for the clarification. In that case the script should work...

Here is what I would try next to isolate the root cause:

Run just the small part that throws the error, as a Background Script:

var companyTaxId = 'SET_TO_SOME_EXISTING_TAX_ID_VALUE';

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();

Please try this both in the "x_1234_dlr_reemplo" and Global scope.

Does this run fine?

Regards,

Robert