Database view Query not working

Steve42 · ‎12-03-2019

I'm trying to query a database view. The view has 3 tables

Sys_journal_field left joined to Task where tsk_sys = jou_element_id left join to sys_user where usr_email = jou_sys_created_by

I have the following query. I'm trying to get the records back for a specific user and the query returns undefined. I'm obviously not doing something correct, Could you a little assistance please.

I just want to get the created on date and the email address from the above tables but not having any luck

Query

var gr = new GlideRecord('u_reportable_journal_entries')
gr.setLimit(10);
//gr.addQuery("gr.email", "=", "sample@testcompany.com");
gr.query();
while (gr.next()) {
    gs.info(gr.sys_user);
}

Steve42 · ‎12-03-2019

That get's me a little closer, however here is my query

var gr= new GlideRecord('u_reportable_journal_entries');
gr.addEncodedQuery("");
gr.orderByDesc('sys_created_on');
gr.setLimit(500);
gr.query();
while(gr.next()){
gs.info('Name: ' + gr.getValue(gr.name));
gs.info('company: ' + gr.getValue(gr.company));
}

When I run it I get the following output

*** Script: Name: null
*** Script: company: null

I followed the example you put out.  
So for gs.info(gr.getValue(email)) -- which is a field in the database view u_reportable_journal_entries
I get the following error:

Evaluator: org.mozilla.javascript.EcmaError: "name" is not defined.
when I add gr.name  I get null on everything.  

Still don't understand what i'm doing incorrect

View solution in original post

Luxo Nadappan · ‎12-03-2019

hi ,

if you are using the addQuery then please remove quotes from "gr.email".

Thanks

Luxo

Steve42 · ‎12-03-2019

I'm not it's remarked out for now. But in the bottom section how can I access all of the fields in the data set, would I just prepend gr.<columnName>

Luxo Nadappan · ‎12-03-2019

Please note data base view will be used for reporting purpose.. there we can define each table and its column via alias. If you want to achieve query mulitple table,then You need to query separately ..(Glide one table and reference the same in another glide object. )

Thanks

Luxo

Steve42 · ‎12-03-2019

Ok not sure I understand what you just said, Is what i'm trying to accomplish possible?

Can I use Scripts - Background to get a subset of the data and look at it more in depth. Reporting is fine, and I'm good with just using the gui for this but I'm trying to track down an anomaly as to why a record will not print on the report.