I need to find the largest value in a specific column in a table, and that value will be used in a calculation. I’m not sure what would be the most efficient method to use in Servoy?
My thoughts are to create a variable vLargest, loop through the table, compare current record value with vLargest and if its larger: vLargest = new value.
Pretty sure I’ll need to store this someplace, since I dont want to have to loop through the entire Table each session (the data will be changed only irregularly), so this calculation will be stored in a second table.
2 Questions:
Is this the most effective / sensible way to do this in Servoy (is there some esoteric method you Guru’s have squirilled away?)
How do I reference columns in table ‘a’ from a calculation in table ‘b’?
I’d use a select max(columname) from table sql statement. If the calc is not used very much directly in the calculation, otherwise triggered by something else and stored.
Jan Aleman:
I’d use a select max(columname) from table sql statement. If the calc is not used very much directly in the calculation, otherwise triggered by something else and stored.
Thanks Jan - heres the code I’ve been working with but it errors on “controller” is not defined?
function rra_max_pcr_in_db()
{
//Calculate the largest PCR in the Components Table to use in Dynamic CCR Calcs
var vLargestPCR = null
var vCurrentPCR = null
for (var i = 0; i <= foundset.getSize(); i++)
{
controller.setSelectedIndex(i)
vCurrentPCR = rra_pcr_ccr
if(vCurrentPCR > vLargestPCR){vLargestPCR = vCurrentPCR}
}
return vLargestPCR;
}
I’m pretty sure this should be allowed in a calculation, and of course controller would be required to run a SQL statement too would’nt it? Looks like I need further reference for the controller - but not sure what - a Form?
A calc should not reference the controller as it does not run in the form scope but on the column level. You don’t need to reference to a controller to do a query.
Jan Aleman:
A calc should not reference the controller as it does not run in the form scope but on the column level. You don’t need to reference to a controller to do a query.
After all of that I took a slightly different route Jan - I created an max_aggragate on that column, and used that as the dataprovider in my calc.
Not sure if this will be more expensive than firing a SQL statement?
Thanks Joas - I’ll try this again tonight, but when I was attempting to code a calculation yesterday I got errors using the controller / currentcontroller and even databaseManager in the calc window ( mentioned that above)?
Are you suggesting that these are allowed in the Calc window or just that I can run a SQL without controller Joas?
Kahuna:
Are you suggesting that these are allowed in the Calc window or just that I can run a SQL without controller Joas?
I was just showing how to use getDatasetByQuery without using “controller”.
For your calc I would use the max-aggregate you mentioned before or as Jan suggested:
Jan Aleman:
triggered by something else and stored.
I know I’m dragging this out but I need to develop some other calculations too Joas, where multiple tables will be involved in gathering data for the calc.
Jan suggested I could use SQLin the calc window but I cant see how thats possible if databaseManager is not allowed there either?? Did I miss something Joas??
What would you see as the most effective way of gathering multiple table column entries for a stored calc. In the past I would have build a custom UPDATE SQL statement with these tables joined, and stored that in the required table.
Kahuna:
What would you see as the most effective way of gathering multiple table column entries for a stored calc. In the past I would have build a custom UPDATE SQL statement with these tables joined, and stored that in the required table.
I think that really depends on the situation. Usually relations and/or aggregates will do the trick.
The point with calculations is that you don’t really control when they are executed. For example when you show a calculation in a list, it get’s executed dozens of times. If you then do complicated stuff inside that affects a lot of tables it can have a dramatic effect on your performance.
Because of that risk it is not recommended to use databaseManager-stuff inside calculations. If you really want to, you can just type “databaseManager” and it will work, but you have to know what you’re doing.
If you want to show complicated calculations that depend on records of other tables, you should ask yourself if the value is really unique for every record and if it should be updated every time it is shown. It is often enough to update the value with a method when the records from the other table are updated. Table events might be useful in that case.
Kahuna:
What would you see as the most effective way of gathering multiple table column entries for a stored calc. In the past I would have build a custom UPDATE SQL statement with these tables joined, and stored that in the required table.
I think that really depends on the situation. Usually relations and/or aggregates will do the trick.
The point with calculations is that you don’t really control when they are executed. For example when you show a calculation in a list, it get’s executed dozens of times. If you then do complicated stuff inside that affects a lot of tables it can have a dramatic effect on your performance.
Because of that risk it is not recommended to use databaseManager-stuff inside calculations. If you really want to, you can just type “databaseManager” and it will work, but you have to know what you’re doing.
If you want to show complicated calculations that depend on records of other tables, you should ask yourself if the value is really unique for every record and if it should be updated every time it is shown. It is often enough to update the value with a method when the records from the other table are updated. Table events might be useful in that case.
Thanks Joas - I kind of expanded this in another question thread and got some great feedback from Marc too.
This makes it a lot clearer though, and I can now envisage how I might use the table events too, which I had’nt brought into the equation before!
Joas:
… you should ask yourself if the value is really unique for every record and if it should be updated every time it is shown. It is often enough to update the value with a method when the records from the other table are updated. Table events might be useful in that case.
I’m a little confused as to when calculations are fired then Joas - I thought they were fired only when one of the dataproviders referenced withing the calculation were changed! Are you saying that the calculation is fired every time the record with that column is viewed (in a table or form)?