Hi,
i want to execute all stored calcs of all table at once. Has someone done this before?
Is it possible to get all tables which has stored calcs?
Regards,
Stef
Hi Stef,
To recalculate a table you use the databaseManager.recalculate(foundset) function.
Hope this helps
I knew this funciton before.
The question is how to get all tabels with stored calcs and recalculate them.
It’s because I don’t want to have an Form for every table which has an stored calc and I don’t want to edit my recalculate Function when i add an new Stored Calc.
Regrads,
Stef
Hi Stef,
You don’t have to have a form to use a foundset. You can create a foundset from scratch using the databaseManager.getFoundSet() function.
But I don’t think there is a way to see what dataprovider has a calculation on it though. Perhaps a feature request for a new property in the JSColumn object to tell if it has a calculation on it or not?
I put together this function and placed it on one of my ‘admin’ forms. Just call it and select the table you want to recalc from the dialog. Perhaps this would help you out?
function recalc() {
//return all the table names as array
var tableNamesArray =databaseManager.getTableNames('databasename');
var selectedValue = plugins.dialogs.showSelectDialog('Recalc','Select a table to recalc', tableNamesArray);
if (selectedValue == null || "") {
return;
}
var table_fs = databaseManager.getFoundSet("databasename", selectedValue);
table_fs.loadAllRecords();
var cur_fs_cnt = databaseManager.getFoundSetCount(table_fs);
for (var j=1; j<= cur_fs_cnt; j++) {
application.output(j)
cur_record = table_fs.getRecord(j)
databaseManager.recalculate(cur_record)
}
databaseManager.saveData();
plugins.dialogs.showInfoDialog("Recalc", "done", "sweet");
}
Thank the last 2 Posts helped much! I will make a function with «getTableNames()» and «getFoundSet()» hope this works.
Thanks for the Help!
Regrads,
Stef
why do you want to recalculate it just on all tables ?
After we made an Import of the data from an old application with raw and then execute an JasperReport with an SQL and the report contains StoredCalculation they are empty.
I made this function but it only gets to the 13th Table and stops then! The 13th Table has only 15 record and no calcs! Is there a Problem with the Cache i have to manage?
function handleCalculateStoredCalcs() {
var tableNamesArray = databaseManager.getTableNames(databaseManager.getDataSourceServerName(controller.getDataSource()));
var answer = plugins.dialogs.showQuestionDialog('Calculations', 'Are you sure you want to execute all calculations for all tables', 'Cancel', 'OK');
if (answer == 'OK') {
for (var i = 0; i < tableNamesArray.length; i++) {
globals.showProgress(i, tableNamesArray.length, "Calculating (" + (i+1) + "/" + tableNamesArray.length + ") " + tableNamesArray[i], false);
//application.output("Execute Calcs for Table (" + (i+1) + "/" + tableNamesArray.length + ") " + tableNamesArray[i]);
var currentFoundset = databaseManager.getFoundSet(databaseManager.getDataSourceServerName(controller.getDataSource()), tableNamesArray[i]);
currentFoundset.loadAllRecords();
databaseManager.recalculate(currentFoundset);
databaseManager.saveData();
}
plugins.dialogs.showInfoDialog('Calculations', 'All Calculations are Executed', 'OK');
globals.hideProgress();
}
}
how does it stop?
an error?
do you see this dialog? plugins.dialogs.showInfoDialog(‘Calculations’, ‘All Calculations are Executed’, ‘OK’);
no there is no error and the progressBar is shown at Table 13 (11%).
I’ll try to debug the function.
OK I am now in debuging the function and got now to table 70 and it is still working! It looks like the ProgressBar is not uptaded.
The console with applicaiton output says now:
Execute Calcs for Table (70/105) profile_positions
and the ProgressBar shows:```
Calulating (43/105) fractions
what does: globals.showProgress(i, tableNamesArray.length, “Calculating (” + (i+1) + “/” + tableNamesArray.length + ") " + tableNamesArray*, false); exactly do then?*
This functions updates the ProgressBar. I found now out that the UserInterface sometimes is not fast enough to Update and when the recalculate takes longer the progressbar shows 1 or 2 Tables before. The Funktion is going through all Tables but is not working.
The Stored Calcs are not updated and I dont know why! Has someone any suggestion why the StoredCalcs are not saved in the Database?
function handleCalculateStoredCalcs() {
var answer = plugins.dialogs.showQuestionDialog('Calculations', 'Are you sure you want to execute all calculations for all tables', 'Cancel', 'OK');
if (answer == 'OK') {
var tableNamesArray = databaseManager.getTableNames(databaseManager.getDataSourceServerName(controller.getDataSource()));
application.output(new Date() + ': Start executing stored-calculation');
for (var i = 0; i < tableNamesArray.length; i++) {
globals.showProgress(i, tableNamesArray.length, 'Execute Calcs for Table (' + (i+1) + '/' + tableNamesArray.length + ') ' + tableNamesArray[i], false);
application.output(new Date() + ': Execute Calcs for Table (' + (i+1) + '/' + tableNamesArray.length + ') ' + tableNamesArray[i]);
var currentFoundset = databaseManager.getFoundSet(databaseManager.getDataSourceServerName(controller.getDataSource()), tableNamesArray[i]);
currentFoundset.loadAllRecords();
databaseManager.recalculate(currentFoundset);
databaseManager.saveData();
}
application.output(new Date() + ': Finished executing stored-calculation');
plugins.dialogs.showInfoDialog('Calculations', 'All Calculations are Executed', 'OK');
globals.hideProgress();
}
}
We have an old Applikation there we did the recalculate for one table with an form on the Table and it worked. Is there a Probleme?
function handleRecalculate(){
var statement = "SELECT id FROM stations";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), statement, null, 50000);
foundset.loadRecords(dataset);
databaseManager.recalculate(foundset);
}
Thanks and Regards, Stef
OK I got it now to work! It was a mistake of myself.
Regards and Thanks for the Help
Stef
I wanted to know how the recalc function of servoy is working. Is the function going through every dataprovider or only through calcs?
Regards
Stef
it knows which one are calcs and will just recalc/get only those.
But intresting is that if i execute recalc on an table with no calcs it needs much time. So it dont checks from the beginning if there are calcs and goes through all records. Could that be right?
Regards
Stef
ah it doesnt test upfront, we could improve that…
we just go over the records and then get the list of calcs and try to ask them
So yes we still loop over all the records…
I guess we never thought that somebody would call that method if the table doesnt have calcs…
This would be a big improvment for me! I created an request in the support system for that.
Regards, Stef
I see you fixed it! Nice!
I have one last question: Are you looking in the recalculate function only for stored calcs or for all calcs? It seems the function is going through all calcs and I don’t get why this is neccessary. For me it would be right if only the sored calcs are executed! When is it neccessary to execute the normal calcs?
Regards