Database view Query not working

[Steve42]

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

[jeffgreener]

You'll need to do 3 separate Glide records most likely, First one to get the user Sys_id, then get a list of sysid's from task that match that user, then get your journal entries for those tasks. This can get pretty ugly pretty quickly. I'd be surprised if the Journal table doesn't also have a ref to the user table as well. You may be able to dotwalk from the task to the user table.

a quick hint, if you can get the filtering to be right in from a list view, just right click on the breadcrumbs and copy query, then in script do a gr.AddEncoded('<copied filter>');

then tweak as you need to, by subbing Sys_id's from another GR record. 

to help more I'd need to better understand where the goal data is. I think you're trying to pull all journal entries for the user who's assigned to a given task? to get the Username/Created date from the journal table? Is that right?

[Ashutosh Munot1]

Hi,

This is my database view and i use this query which works for me.

var grUSDVD = new GlideRecord('u_database_view_discovery');
grUSDVD.addEncodedQuery("");
grUSDVD.orderByDesc('win_sys_updated_on');
grUSDVD.setLimit(50);
grUSDVD.query();
while (grUSDVD.next()) {
gs.info('sof_display_name: ' + grUSDVD.getValue('sof_display_name'));
gs.info('sof_discovery_model: ' + grUSDVD.getValue('sof_discovery_model'));
gs.info('win_name: ' + grUSDVD.getValue('win_name'));
gs.info('rel_type: ' + grUSDVD.getValue('rel_type'));
gs.info('vir_name: ' + grUSDVD.getValue('vir_name'));
gs.info('reltwo_type: ' + grUSDVD.getValue('reltwo_type'));
gs.info('esx_name: ' + grUSDVD.getValue('esx_name'));
gs.info('relfour_type: ' + grUSDVD.getValue('relfour_type'));
gs.info('vclust_name: ' + grUSDVD.getValue('vclust_name'));
gs.info('relthree_type: ' + grUSDVD.getValue('relthree_type'));
gs.info('vdc_name: ' + grUSDVD.getValue('vdc_name'));
gs.info('win_sys_updated_on: ' + grUSDVD.getValue('win_sys_updated_on'));
gs.info('win_sys_created_on: ' + grUSDVD.getValue('win_sys_created_on'));
}

So what you do is:

Change this:

gs.info(gr.sys_user);

to

gs.info(gr.getValue(usr_email));

Thanks,
Ashutosh Munot

[Steve42 · Accepted solution]

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

[Ashutosh Munot1]

Hi,

you should always use the prefix used for that table. In your case i guess usr is the prefix used in where clause for referencing user table.

so use gr.getValue(usr.email); to get the value of email.

So it should like below:

gs.info('Name: ' + gr.getValue(gr.prefix.name));
  gs.info('company: ' + gr.getValue(gr.prefix.company));

Thanks,
Ashutosh

[Ashutosh Munot1]

Hi,

This also has to be updated.

gr.orderByDesc('sys_created_on');

to something like:

gr.orderByDesc('prefix_sys_created_on');