ok, let’s take an example of how to create a contactlist with company names
table companies ; columns company_id, company_name
table contacts ; columns contact_id, contact_name, company_id
var myQuery = " SELECT contacts.contact_name, companies.company_name"+
" FROM contacts, companies"+
" WHERE contacts.company_id = companies.company_id";
var maxRows = 100 //this limits amount of rows returned
capture the query result in a dataset
(check out the database manager node in the method editor)
var myDataset = databaseManager.getDataSetByQuery(controller.getServerName(), myQuery, null, maxRows)
myDataset is now an object that in fact looks like a matrix.
col1 col2
John Sony
James Philips
Henry Technics
Now you can use all methods inside the Datamanager>JSDataset to extract data from the Dataset.
e.g. myDataset.getValue(2,2) will return “Philips”
In our case we are going to use
myDataset.rowIndex to set the row of the dataset as we loop through it.
roomDataset[2] to retrieve a column, where [2] reflects the column nr. (company_name)
If you’re not familiar with HTML have a look at this basic structure.
<html> //always start the html code with "html" lowercase , in order to trigger the html renderer in Servoy
<TABLE> // start creating table
<TR> <TD></TD> <TD></TD> </TR> // "<TR>" stands for a row, "<TD>" for a cell
<TR> <TD></TD> <TD></TD> </TR> // note that each opening tag"<TD>" is closed again by a "slash" tag </TD>
</TABLE>
// you can add all kinds of design elements to tags.(alignment, colors, borders etc.)
// search on google for html info about html editors, lessons etc.
So here is where the data and html come together…
var HTML = "<html>"+ // all the HTML code is captured inside this var.
"<TABLE>";
for( var i = 1 ; i <= myDataset.getMaxRowIndex() ; i++ )
{
myDataset.rowIndex = i; //set the row
HTML += '<TR bgcolor="#ffff33">'+
'<TD align=right> '+myDataset[1]+'</TD>'+ //refers to contact_name
'<TD align=right> '+myDataset[2]+'</TD>'+//refers tocompany_name
'</TR>'
}
Now that you have all HTML code lined up do following:
1)create a global named myHTMLReport
2) place it in any form you want
3) set the display type property to “HTMLarea”, and make it non editable
add this line in your script:
globals.myHTMLReport = HTML //fill the global with the html created in this script.
Attach this script to the onShow event of the form. (or perhaps other events)
