Recently I wanted a way to export all the fields on a form (in a foundset) and export them to Excel. Thanks to Servoy, it was EASY!
There are two methods: one called “getFormFields” and the other called “ExcelMaker” which calls the “getFormFields” function.
The “getFormFields” method takes the NAME of a form as an argument, then pokes into the repository to find out what fields are on a form (NOT “named” fields - but ALL fields whether they have a name or not). The method will also put them in order if they have a tab sequence, and if not, will return them in creation order:
//pass in the form name
var formName = arguments[0]
//get solution ID
//Get a dataset based on query
var maxReturnedRows = 1
var query = "select solution_id from servoy_solutions where solution_name = ?"
var args = new Array();
args[0] = application.getSolutionName()
var dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);
var solutionID = dataset.getValue(1,1)
var releaseNum = application.getSolutionRelease()
//get form ID
maxReturnedRows = 1000
query = 'select a.element_id, a.property_value ' +
'from servoy_element_properties a ' +
'where a.content_id = 37 ' +
'and a.revision = (select revision from servoy_releases where solution_id = ? and release_number = ? ' +
'and element_id = a.element_id) and a.property_value = ? ' +
'order by a.property_value'
args = new Array();
args[0] = solutionID
args[1] = releaseNum
args[2] = formName
dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);
var formID = dataset.getValue(1,1)
//get all the dataproviders for the current form (with or without a name)
query = 'select b.property_value, c.property_value, b.element_id, d.property_value ' +
'from servoy_elements a, servoy_element_properties b, servoy_element_properties c, servoy_element_properties d ' +
'where a.object_type_id = 4 ' +
' and a.element_id = b.element_id ' +
' and a.element_id = c.element_id ' +
' and a.element_id *= d.element_id ' +
' and b.content_id = 15 ' +
' and c.content_id = 24 ' +
' and d.content_id = 28 ' +
' and a.solution_id = ? ' +
' and a.parent_element_id = ? ' +
' and b.property_value NOT LIKE ? ' +
'order by d.property_value, b.element_id'
args = new Array();
args[0] = solutionID
args[1] = formID
args[2] = 'globals.%'
dataset = databaseManager.getDataSetByQuery('repository_server', query, args, maxReturnedRows);
var colNames = new Array()
if(dataset.getMaxRowIndex() > 0)
{
colNames = dataset.getColumnAsArray(1)
}
return colNames
Next we have the main “ExcelMaker” method (I made it a global method in a module so that I can use it from ANY solution). It takes no arguments and can be executed from any form:
//get the form name
var formName = currentcontroller.getName()
//var tabData = forms[formName].controller.copyAllRecords()
//get the table information
var tableName = forms[formName].controller.getTableName()
var max = forms[formName].foundset.getSize();
//get the names of the fields on the form
var colList = globals.getFormFields(formName);
var maxCols = colList.length
if(colList.length > 0)
{
//output the column names first
var output = colList.join('\t')
//loop through foundset of form and get all the columns and data
for ( var i = 1 ; i <= max ; i++ )
{
forms[formName].foundset.setSelectedIndex(i)
for ( var c = 0 ; c < maxCols ; c++ )
{
if(c == 0)
{
//first column
output += '\n"' + forms[formName].foundset[colList[c]] + '"'
}
else
{
//subsequent columns
output += '\t"' + forms[formName].foundset[colList[c]] + '"'
}
}
}
//prompt for the fileName
var fileName = plugins.file.showFileSaveDialog('myExport.xls')
//see if they chose an export name
if(fileName)
{
var success = plugins.file.writeTXTFile(fileName, output)
if(success)
{
plugins.dialogs.showInfoDialog( '', 'Export successful.', 'OK')
}
else
{
plugins.dialogs.showErrorDialog( 'Error', 'An error occured when trying to export your data.', 'OK')
}
}
else
{
plugins.dialogs.showInfoDialog( '', 'Export canceled by user.', 'OK')
}
}
I hope this helps in your development efforts!