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

Steve42 · ‎12-03-2019

Ashutosh, where would I find the prefix would that be the table prefix so like on the Task table in the definition of the Database view I see its prefix is tsk, so if i wanted the task table and the company field I would so something like this?

gs.info('Company Name: ' + gr.getValue(gr.tsk.company);

Steve42 · ‎12-03-2019

That worked. I'm starting to get the information I'm wanting to look at.

Thank you x 100000000

DrewW · ‎12-03-2019

Are you making sure to use the table prefixes when accessing data? It does not look like you are unless you gave a table the prefix of "gr".

Example for the sc_task_sla table, sct is the prefix for columns in the sc_task table and taskslatable is the prefix for the task_sla table columns. You should be able to look it up in your instance.

var a = new GlideRecord("sc_task_sla");
a.addQuery("sct_parent", "21325c5adb052bc0e86e85754b96195c");
a.query();
while(a.next()){
   gs.print(a.getDisplayValue("sct_parent"));
}