Is there a way to do an export via a method and run it as a chron? I like to export records and just have the user click a button that would export a found set or run the method as a scheduled event.
Thanks for any help!
Armando
Depends on the file type you want to create. When it is some sort of text based you can write it yourself.
An alternative to ‘do it all yourself’ is the data plugin. You can find it here: Servoy Components – Plugins, Beans, Web Components, Angular Services & Servoy Developer Consulting.
Hope this helps
The following global method can be modified to export records for any table in a solution by changing the first four variables. The exported file is saved to the soluton folder.
var vColumnNames = 'company_name,contact_name';
var vTableName = 'contacts';
var vFilter = ' WHERE company_id = 2'; //optional filter
var vExportedFileName = 'contacts_exported.mer'
//set desired maximum of exported rows
var maxReturnedRows = 10000;
//sql query
var query = 'SELECT ' + vColumnNames + ' FROM ' + vTableName + vFilter;
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturnedRows);
//can create mer, csv or tab delimited file
var mer = dataset.getAsText(',','\n','"',false)
//var csv = dataset.getAsText(',','\n','"',true)
//var tab = dataset.getAsText('\t','\n','"',true)
//adds comma separated column names without quotes to first line of exported file
mer = vColumnNames + '\n' + mer
var textData = mer
var success = plugins.file.writeTXTFile(vExportedFileName,textData);
The essence of the above method was Harjo’s original method (see http://forum.servoy.com/viewtopic.php?t … ort+method). Thank you Harjo. My small contribution is to add the line for exporting in mer format. I find that mer files are convenient in that they can be converted directly into a FileMaker table with the same column names when double-clicked (on Windows using fmp 5.5).
Dean Westover
Below is a new version that uses regular expressions to remove the time from date fields and to re-arrange the order of the month, day and year. This version also shows how to export to html.
var vColumnNames = 'column_name1,column_name2';
var vTableName = 'table_name';
var vFilter = '' //this is optional - for example replace '' with ' WHERE company_id = 2';
var vExportedFileName = 'exported.html' //defaults to Servoy folder if name not preceded with path
//set desired maximum of exported rows
var maxReturnedRows = 2500;
//sql query
var query = 'SELECT ' + vColumnNames + ' FROM ' + vTableName + vFilter;
var dataset = databaseManager.getDataSetByQuery(currentcontroller.getServerName(), query, null, maxReturnedRows);
//from here you can create a mer, csv, tab or html file
//var vFile = dataset.getAsText(',','\n','"',false); //this would create mer file
//var vFile = dataset.getAsText(',','\n','"',true); //this would create csv file
//var vFile = dataset.getAsText('\t','\n','"',true); //this would create tab file
//var vFile = '<html>' + dataset.getAsHTML() + '</html>'; //this would create html file
//let's create a html file
var vFile = '<HTML><HEAD><STYLE TYPE="text/css"></STYLE></HEAD>'
vFile += dataset.getAsHTML() + '<HTML>';
//the code below removes time from dates using a regular expression
//regular expressions allow pattern matching and reording of elements
vFile = vFile.replace(/(\d\d\d\d)-(\d\d)-(\d\d)( \d\d\:\d\d\:\d\d\.\d*))/g,'$2/$3/$1');
//in prior code line $2/$3/$1 is what does the reordering of month, day and year.
//the numbers 2, 3 and 1 indicate the the corresponding matching parentheses
//in the regular expression pattern (earlier in the same line).
//to change forward slashes in the date to dashes use $2-$3-$1 instead
//if you want to right justify all columns that contain a period try this
vFile = vFile.replace(/<TD>(\-?\d*\.)))/g,'<TD align=right>$1');
//include the next line only if you want to remove those ugly nulls
vFile = utils.stringReplace(vFile,'null',' ');
//use stringReplace to change appearance of table
vFile = vFile.replace('CELLPADDING=1','CELLPADDING=2');
//use stringReplace to change appearance of column names, etc.
//vFile = vFile.replace('ugly column name','pretty column name');
//the next code line should only be used for mer files
//it adds comma separated column names without quotes
//to the first line of the exported file
//vFile = vColumnNames + '\n' + vFile
var success = plugins.file.writeTXTFile(vExportedFileName,vFile);
Hope this helps.
Dean Westover
I just improved the regular expression code in the above example that removes the time from date fields. What was previously four lines requiring four Replaces has been reduced to one line with one Replace.
The code that controls font family and size is now added by an assignment instead of requiring a Replace. Removing the letters “null” in empty fields from the html table was breaking the table borders when the borders were set to 1, so “null” is now being Replaced by the html code for an invisable character " ". This keeps the table from breaking.
A new regular expression example has been added that right justifies all html columns that contain a period.
Dean Westover
Westy:
…so “null” is now being Replaced by the html code for an invisable character " ". This keeps the table from breaking.
For your information: that ‘invisible character’ is a space. A non-breaking space ( ).
Very handy for forcing multiple spaces and to make sure text doesn’t break.
Westy:
I just improved the regular expression code in the above example that removes the time from date fields. What was previously four lines requiring four Replaces has been reduced to one line with one Replace.
Thanks, Westy
very useful and very fast. 
The only little issue it seems to have is that it has some problem to deal with HI-ASCII character, not correctly mapped.