# Execute all Stored Calcs

**URL:** <https://forum.servoy.com/t/execute-all-stored-calcs/12916>\
**Category:** Classic Servoy\
**Created:** [September 23, 2010, 2:48pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916 "2010-09-23T14:48:36Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 23, 2010, 2:48pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/1 "2010-09-23T14:48:36Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 23, 2010, 3:30pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/2 "2010-09-23T15:30:38Z")

</div>

Hi Stef,

To recalculate a table you use the databaseManager.recalculate(foundset) function.

Hope this helps

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 23, 2010, 4:12pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/3 "2010-09-23T16:12:12Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ROCLASI](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/roclasi/32/4371_2.png) [@ROCLASI](https://forum.servoy.com/u/ROCLASI)\
**Post date:** [September 23, 2010, 4:32pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/4 "2010-09-23T16:32:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![ryanparrish](https://avatars.discourse-cdn.com/v4/letter/r/9dc877/32.png) [@ryanparrish](https://forum.servoy.com/u/ryanparrish)\
**Post date:** [September 23, 2010, 4:52pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/5 "2010-09-23T16:52:01Z")

</div>

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?

```auto
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");
	
}

```

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 24, 2010, 7:05am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/6 "2010-09-24T07:05:40Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [September 24, 2010, 8:23am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/7 "2010-09-24T08:23:11Z")

</div>

why do you want to recalculate it just on all tables ?

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 24, 2010, 9:47am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/8 "2010-09-24T09:47:45Z")

</div>

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?

```auto
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();	
	}
}

```

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [September 24, 2010, 11:27am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/9 "2010-09-24T11:27:41Z")

</div>

how does it stop?  
an error?  
do you see this dialog? plugins.dialogs.showInfoDialog(‘Calculations’, ‘All Calculations are Executed’, ‘OK’);

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 24, 2010, 11:59am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/10 "2010-09-24T11:59:26Z")

</div>

no there is no error and the progressBar is shown at Table 13 (11%).

I’ll try to debug the function.

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 24, 2010, 1:02pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/11 "2010-09-24T13:02:29Z")

</div>

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:

```auto
Execute Calcs for Table (70/105) profile_positions

```

and the ProgressBar shows:```  
Calulating (43/105) fractions

```auto

```

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [September 24, 2010, 3:42pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/12 "2010-09-24T15:42:04Z")

</div>

what does: globals.showProgress(i, tableNamesArray.length, “Calculating (” + (i+1) + “/” + tableNamesArray.length + ") " + tableNamesArray\*, false); exactly do then?\*

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 27, 2010, 7:24am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/13 "2010-09-27T07:24:26Z")

</div>

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?

```auto
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?

```auto
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

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 28, 2010, 3:48pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/14 "2010-09-28T15:48:45Z")

</div>

OK I got it now to work! It was a mistake of myself.

Regards and Thanks for the Help

Stef

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 29, 2010, 8:40am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/15 "2010-09-29T08:40:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [September 29, 2010, 8:59am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/16 "2010-09-29T08:59:33Z")

</div>

it knows which one are calcs and will just recalc/get only those.

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 29, 2010, 10:02am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/17 "2010-09-29T10:02:26Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\
**Post date:** [September 29, 2010, 12:22pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/18 "2010-09-29T12:22:31Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 29, 2010, 1:14pm UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/19 "2010-09-29T13:14:43Z")

</div>

This would be a big improvment for me! I created an request in the support system for that.

Regards, Stef

---

<div class="post-metadata">

**Author:** ![stefbrt](https://avatars.discourse-cdn.com/v4/letter/s/858c86/32.png) [@stefbrt](https://forum.servoy.com/u/stefbrt)\
**Post date:** [September 30, 2010, 8:01am UTC](https://forum.servoy.com/t/execute-all-stored-calcs/12916/20 "2010-09-30T08:01:53Z")

</div>

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

[Next page](https://forum.servoy.com/t/execute-all-stored-calcs/12916.md?page=2)