How to query a reference column with an extended column?

[Jeff Cassels]

I have extended two base tables to add a few attributes for Configuration Compliance. One has a reference to the other. Call them compliance_test and compliance_result. compliance_result has a reference to compliance_test. I want to do query on one of the extended fields on compliance_test
The field "control" is the field name of the reference to compliance_test from compliance_result. I can query using the base field, but not the extended. I get the error field name 'control.extended_field' not found in table 'compliance_result'. Is this possible to do?

var gr = new GlideRecord("compliance_result");
gr.addQuery("control.extended_field", "CCI-001090"); // does not work
gr.addQuery("control.base_field", "CCI-001090"); // works

[Willem · Accepted solution]

you can use table.ref_<extended table name>.field.

So in your case:

var gr = new GlideRecord("compliance_result");
gr.addQuery("control.ref_compliance_test.extended_field", "CCI-001090");
gr.addQuery("control.base_field", "CCI-001090");

[Willem]

Also make sure you checked the field name extended_field. Is that the technical name on the table? Does it contain a string value? Or is it a reference (then a sys_id is saved as the value)? Or a choice? That has a value and display value.

[Jeff Cassels]

That worked. Man, I fought that all day and I almost missed the "ref_". Thank you!