Script Background - GS.PRINT - value from databse

[Pastupe]

Hello,

Before I write the text here I want to thank you to all who spend time to read and reply on this, Thank You.
(I had opened same topic some time ago - name was""" script background - gs.print how to get the result to excell ? "" but there were not respond's I expected)
Now I have found one way I want to Share with you all and Also want to find if some of You does know any other way how to achieve the below.


I need get data from database. I have script in Script's-Background. This script give me result's back.
To be able take those data to excell file, You must write SEMICOLON in gs.print before every value I want get.

Like this --- gs.print(";"+ c.u_code_of_mill +";"+ c.sys_id +";"+ c.u_country_code);
(without semicolon i cannot get value's in separate column later in excel or CSV)

After I get result on the screen, I copy the data from screen to Notepad and save it as CSV file to be able have every value in separate column. So here I finally achieve that I have everey values I asked for in, in separate column in CSV or excel.

Now My question is if there is any other way how to achieve this ?
Please note, that I cannot use reporting as I need get back also SYS_ID value which is not displayed on the form where I take a data from table, so reporting is not a solution here.

Any Tip's or tric's ?

Petr

Script Background:

var c = new GlideRecord('u_mill_code');

c.addQuery('u_code_of_mill');
c.orderBy('u_mill_code');
c.query();

while (c.next()) {
c.autoSysFields(false); // Do not update sys_updated_on, sys_updated_by, and sys_mod_count
c.setWorkflow(false); // Do not run any other business rules
gs.print(";"+ c.u_code_of_mill +";"+ c.sys_id +";"+ c.u_country_code);
//gs.print(c.sys_id);
}

Result in CSV file here:

Code SYS ID Country
AT_160258162d4b9fec100438058bb8299c0a4Austria
AT_161218162d4b9fec100438058bb8299c0a5Austria
AT_162e18162d4b9fec100438058bb8299c0a5Austria
AT_912a58162d4b9fec100438058bb8299c0a5Austria
AU_600698162d4b9fec100438058bb8299c0a5Australia
AU_9172d8162d4b9fec100438058bb8299c0a5Australia
BE_130ed8162d4b9fec100438058bb8299c0a5Belgium
BE_225a18162d4b9fec100438058bb8299c0a6Belgium
BE_443658162d4b9fec100438058bb8299c0a6Belgium

[Pastupe]

Chris, now it run fine.
But cannot find reason, why incident have in attachment only one row record from 100 existing in database.

I have script below, after I run it, new incident is created with atatched txt. file. Txt.... file only contain 1 record.
see attachment also as a proof.
What i do wrong ?
Petr


var c = new GlideRecord('u_mill_code');

c.addQuery('u_code_of_mill');
c.addQuery('u_active','true');
//c.orderBy('u_mill_code');
c.query();

while (c.next()) {
c.autoSysFields(false); // Do not update sys_updated_on, sys_updated_by, and sys_mod_count
c.setWorkflow(false); // Do not run any other business rules
}



var blankInc = new GlideRecord('incident');
blankInc.insert();
var value = (";"+ c.u_code_of_mill +";"+ c.sys_id +";"+ c.u_country_code);

var attachment = new Attachment();
var attachmentRec = attachment.write("incident", blankInc.sys_id.toString(), "Petr_Test.txt", "text/plan", value);
var attachRec = new GlideRecord("sys_attachment");
attachRec.addQuery("table_sys_id", blankInc.sys_id.toString());
attachRec.addQuery("file_name", "Petr_Test.txt");
attachRec.query();
attachRec.next();
var url = "sys_attachment.do?sys_id="+attachRec.sys_id.toString()+"&view=true";
gs.print(" here");

[cnanda]

Petr,

The reason the file only contains one record is you need to do something like the following:

var c = new GlideRecord('u_mill_code');

c.addQuery('u_code_of_mill');
 c.addQuery('u_active','true');
 //c.orderBy('u_mill_code');
 c.query();
//Moved value variable above while loop for c
var value = "";
while (c.next()) {
 c.autoSysFields(false); // Do not update sys_updated_on, sys_updated_by, and sys_mod_count
 c.setWorkflow(false); // Do not run any other business rules
value += (";"+ c.u_code_of_mill +";"+ c.sys_id +";"+ c.u_country_code + "\n");
 }

You need to concatenate the results of your query to the "value" variable in each loop. The reason you only had one record was because you were looping through the "c" object, and once the loop was done, you were adding the results of the last object to the string. The above code adds each objects fields to the string, ends the line with a newline, and when it finishes you will have your complete file.

Let me know if that works for you!

[Pastupe]

I have it now. Thank You.
It work perfect. I have created several of this script's now to play with it and get different data from different table's and it work really great.

My whole script is attached below under whole text (I dont know how to put my script here in the the nice window as you did)

Chris can you please explain me what does it mean exactly that you have changed script with var value = ""; and with value += (";"+ c.u..................
1. As I undestood it, (var value = ""; ) is variable saying ALL existing data based on the Query ???
2. And (value += (";"+c.u.................. this means that value add for every found record one row in to the attachment of this script ?


I want to logically understand how this 2 things work together.
Im not on high level in javascript and I would like to learn from this case.
Thank you very much
Petr

var c = new GlideRecord('u_mill_code');

c.addQuery('u_code_of_mill');
c.orderBy('u_mill_code');
c.query();
var value = "";
while (c.next()) {
c.autoSysFields(false); // Do not update sys_updated_on, sys_updated_by, and sys_mod_count
//c.setWorkflow(false); // Do not run any other business rules
value += (";"+ c.u_code_of_mill +";"+ c.sys_id +";"+ c.u_country_code + "\n");

}


var blankInc = new GlideRecord('incident');
blankInc.caller_id = "60478";
blankInc.short_description = "Petr Test";
blankInc.category = "inquiry";
blankInc.assignment_group = "5496a"; // sys ID, only example, in real life sys id is longer
blankInc.u_service_ci = "ac2647f9879439";
blankInc.description = "Petr Test";
blankInc.insert();

var attachment = new Attachment();
var attachmentRec = attachment.write("incident", blankInc.sys_id.toString(), "Petr_Test.txt", "text/plan", value);
var attachRec = new GlideRecord("sys_attachment");
attachRec.addQuery("table_sys_id", blankInc.sys_id.toString());
attachRec.addQuery("file_name", "Petr_Test.txt");
attachRec.query();
attachRec.next();

[richard_selby]

There is a way to get a sys_id in a report. It's a bit of a hack, but it's easy and it works. Basically, you create a db view with just one table in it. When you report on your new view, the sys_id is available like any other column.

More detailed instructions:
Go to the Database Views section on the navigator, create a new view, name it, give it a label, e.g. Business Services With Sys ID, as the label is what you'll look up in the Reports section later. After saving, you will see a new form section for the View Tables related list. Add the table you wish to report on. You don't need to add any fields on the next screen. Go back to the main database view list, and you will see a blue Try It link. That will take you to a list view of your new table, which you can personalize (cog wheel icon), and you will see the Sys ID (sys_id) is there along with all the other fields. Later on, when you go to reports, your new view will be there to report on. It's the same as the base table, but with a sys_id column.

[Pastupe]

Hi Richard,
thank you for the proposal of the interesting way how to get SYS_ID via reporting.

Before i try it my self in non production instance I have an question to You.

I conclude now, that you use this in your instance if you need to get SYS_ID via report. But is the SYS_ID also then available for user's within the tool which have the right's to create their own report ?
I think we should not allow user's create report to collect SYS_ID ...
wait for you reply
thank you
Petr