Looking for some conceptual help if possible, I raised this in another question ‘Find Largest Value in a Column’ - which I resolved but it thru up this challenge - so its not actually a duplicate posting !
I have some (a lot) of calulations which update a single column but which use multiple tables as the determining factor. So:
Table a is a list of potential criteria to use for a calculation (col_a = UpdateTo1, col_b = Where Table b = X, col_c = Where table c = Y etc.)
Table b a table to search for this criteria, Table c another table to search for criteria.
I’ll need SQL to do this of course, but I cant get my head round how this would work most effectively in Servoy. A stored calculation seems like the obviouys answer BUT I seem NOT able to use Database Manager, nor able to call a global function when creating the calculation script.
Q. So is the stored calculation script really only suited to working with columns in the same table?
Q. If not - how is a calc script structured to call a global function / use Database Manager methods?
Q. If so - is it a case of creating a global method to do the entire calculation and then adding it to the onDataChange event of all of the concerned dataproviders? Seems lke not needing to do that is the real benefit of the stored calculation.
Q. What other structure might I apply to get this end result?
As I mentioned I have a lot of calculation to do in this solution all with similar requirements, so getting this structure and concept right in my head is essential. Your feedback appreciated.
This is a big question and I guess there’s not really one way to solve it.
But let me try to clear things a bit:
Kahuna:
Q. So is the stored calculation script really only suited to working with columns in the same table?
No, but using columns of other tables (you need to use relationships) will not automatically update.
So if you use col X form table B in a calculation table A, you should do a recalculate of the related records in table A whenever the value in col X of table B changes.
This can be done using the databaseManager.recalculate function.
Kahuna:
Q. If not - how is a calc script structured to call a global function / use Database Manager methods?
Just call it like ```
globals.myGlobalScript();
Be carefull using references to - for instance - forms, because calculations do not know from which form-scope they are called.
> Kahuna:
> Q. If so - is it a case of creating a global method to do the entire calculation and then adding it to the onDataChange event of all of the concerned dataproviders? Seems lke not needing to do that is the real benefit of the stored calculation.
That would be a possibility, and you should do this anyway to trigger 'recalculation' when using calculations.
Maybe this makes your 'how to...' een easier choice.
> Kahuna:
> Q. What other structure might I apply to get this end result?
We used the table events to trigger a script whenever there are changes on a record.
Depending on the table and which columnvalue has been changed we call a global method that updates the field from another table.
The table and columns on which we trigger are records in a table from which we create a global object in order to minimize queries during the use of the application.
Hope this can help you somehow...
Thanks for that concise feedback Marc - clarified a lot for me. One question still though:
You mentioned using databaseManager to recalculate - I assume you meant from a global script not from the calulation script? I cant see how I can use databaseManager from a calculation (though I see where th globals.script(); is usable - I was thrown when I could’nt get the globals functions thru the explorer tree).
Kahuna:
ou mentioned using databaseManager to recalculate - I assume you meant from a global script not from the calulation script?
That’s it! You should trigger this in an onDataChange method, or as part of a global method which is called by table events.
So assume a stored calculation in table ‘A’ called ‘myStoredCalc’ which references a column ‘X’ in table ‘B’
table B is related to table A via relationship ‘table_B_to_table_A’
Let’s say we use an onDataChange method on form ‘myTable_B_form’ on the element that uses column X from table B as the dataprovider:
var $foundset = forms.myTable_B_form.table_B_to_table_A;
databaseManager.recalculate($foundset);
Doing this using onDataChange has the disadvantage that you have to create a lot of methods like this.
I’d prefer a global method which does it al for me and should be called from a table event.
Adding logic to your routine could be as simple as adding a record to a table which holds all the information you need.
mboegem:
No, but using columns of other tables (you need to use relationships) will not automatically update.
So if you use col X form table B in a calculation table A, you should do a recalculate of the related records in table A whenever the value in col X of table B changes.
This can be done using the databaseManager.recalculate function.
Marc, are you sure about this?
Maybe an official answer from R&D would be helpful.
stored calcs with foreign references will only update when they are forced to, which means they have to be in the clients cache.
So from the point of ‘reliability of the data’, I’d say you should always make sure to perform a recalculation when changing the foreign value.
Otherwise you will end up with half of your records being updated already and the other half not.
we do this also by using databaseManager.recalculate or else you will end up with wrong calculations!
the only way they will update, is dat you view or touch the record!
for instance, a table-view only shows the first 200 records, so yes for those 200 it will update automaticly, but not for the rest!
That’s correct Harjo, you need to “touch” the records to have the calc updated but this happens for ANY calculation, not only for the ones that reference columns from other tables.
Kahuna:
Q1. So is the stored calculation script really only suited to working with columns in the same table?
Q2. If not - how is a calc script structured to call a global function / use Database Manager methods?
Q3. If so - is it a case of creating a global method to do the entire calculation and then adding it to the onDataChange event of all of the concerned dataproviders? Seems like not needing to do that is the real benefit of the stored calculation.
First a (stored) calculation will always provide the correct answer when asked for (via scripting or GUI), but a (stored) calculation will not be triggered by global changes, like changing a global variable or when changing a related field over a global relation. (since if there are a million rows in the database with a stored calc depending on a global, we will not start updating a million rows, use dbmg.recalculate() in this case to force every row to be updated with correct stored calc value)
To answer your questions:
q1) related values can be used as well as global vars
q2) well technically its possible to call global methods or anything you want, but you need to be an experienced javascript coder to know all the side effects (like scoping, that’s why we hide these possibilities by default in developer)
q3) well of course you can do your own calc alike stuff ondatachange, but that requires more coding on your end
ngervasi:
That’s correct Harjo, you need to “touch” the records to have the calc updated but this happens for ANY calculation, not only for the ones that reference columns from other tables.
If no foreign(related) columns are referenced, these particular records are in memory. This means they will recalculate anyway…
ngervasi:
That’s correct Harjo, you need to “touch” the records to have the calc updated but this happens for ANY calculation, not only for the ones that reference columns from other tables.
If no foreign(related) columns are referenced, these particular records are in memory. This means they will recalculate anyway…
I don’t think so, only the records displayed on screen are in memory and will be updated so if you have a form with 200 loaded recs only those 200 will have the calc updated. If you are running a method you need to touch the record to have it’s calcs updated. At least this is my understanding of how calcs works.
To recap: if you need to always have correct values in the backend you should code your own calculations (using onDataChange or table events), if you use servoy as the only frontend it will take care of updating the database when needed, you just need to make sure that you run a recalculate() if you don’t touch the involved records and you need to perform some sort of aggregation/reporting on that calculated column.