Hi All,
I have a foundset. And in it a couple of calculation fields. I want to sort the foundset by these calculation fields.
foundset.sort('foundset.calc_field1 asc, foundset.calc_field2 desc')
So far this does not work. Is there any other way of sorting the foundset by calculation fields?
Regards,
Hareendra
You can ONLY sort on stored calculation! (so columns that are IN the database)
Because Servoy just uses plain SQL to sort, so the column must be there.
Hope this helps
Is there any workaround? any other method you can use?
Regards
Hareendra
You can use our Table-Bean. That allows in-memory sorting of foundsets.
in servoy 6 there will be a sort(function) on foundset.
Johan, will this also solve this issue? http://forum.servoy.com/viewtopic.php?f=8&t=15088#p80638
you then sort in memory, so i guess even new records that are not in the database can then be sorted, so you dont have to save.
So this is just a different solution, but do remember that the whole foundset is read into memory! So don’t use this in 6 on foundset that do have many many records…
Hi again,
I checked the link given and it was all to do with adding a new record and then trying to sort. So I guess there is no practical way to do this until they come up with Servoy 6?
Regards
Hareendra
jcompagner:
in servoy 6 there will be a sort(function) on foundset.
Hi Johan,
Just to make sure, the sort on foundset will eliminate the problem with sorting on a calculation, is this correct?
Can we have an estimated time of Servoy 6 release?
Cheers,
Maria
Convert the foundset to a dataset (include your PK and the columns you want to sort) and then sort the column(s) in the dataset. Once you have done that do a foundset.loadRecords(_ds) to repopulate your foundset. It’s not pretty, but it works for now until Servoy 6…
For example this is a global method that can be placed on the onSort event of a table form to sort a calculated column.
function onSort(dataProviderID, asc, event) {
var _formName = event.getFormName();
var _jsTable = databaseManager.getTable(forms[_formName].foundset);
var _pk = _jsTable.getRowIdentifierColumnNames()[0];
var _ds = databaseManager.convertToDataSet(forms[_formName].foundset,[_pk,dataProviderID]);
_ds.sort(2,asc);
forms[_formName].foundset.loadRecords(_ds);
}
maria:
Just to make sure, the sort on foundset will eliminate the problem with sorting on a calculation, is this correct?
Can we have an estimated time of Servoy 6 release?
yes because you sort directly on the record data that’s in memory so the calculated unstored calc.
But let me warn again, be careful with this with foundsets that has thousands of records! Everything will be pulled in.
Come to ServoyWorld and all will be revealed…
But currently because we are open source, eveyrbody that has some java experience with eclipse can already use and test Servoy 6.
see http://wiki.servoy.com/display/public/DOCS/Setting+up
jcompagner:
Come to ServoyWorld and all will be revealed…
Ha! I wish I could
servoydevguy:
Convert the foundset to a dataset (include your PK and the columns you want to sort) and then sort the column(s) in the dataset. Once you have done that do a foundset.loadRecords(_ds) to repopulate your foundset. It’s not pretty, but it works for now until Servoy 6…For example this is a global method that can be placed on the onSort event of a table form to sort a calculated column.
function onSort(dataProviderID, asc, event) {
var _formName = event.getFormName();
var _jsTable = databaseManager.getTable(forms[_formName].foundset);
var _pk = _jsTable.getRowIdentifierColumnNames()[0];
var _ds = databaseManager.convertToDataSet(forms[_formName].foundset,[_pk,dataProviderID]);
_ds.sort(2,asc);
forms[_formName].foundset.loadRecords(_ds);
}
Thanks mate.
So, will this approach also allow sorting via an aggregation?
Scenario, we’re looking for hot sellers. We want to find every item sold between 2 dates, and then sort them so that:
This is what we’re trying to do now… but it doesn’t sort by trans_qty_total (which is an aggregation; sum).
forms[rptForm].controller.loadRecords(found)
forms[rptForm].controller.sort('site_id asc, trans_qty_total desc, trans_lines_to_inventory_master.item_id asc')
The result will show all matching items on the same line and the qty’s sold are totaled, but the item with the most items sold does not show up at the top of the list.
Does that make sense?
-Chico
If you are using Version 4.7 then I think the only way to sort would be to put the result of your aggregation in a stored calc. I believe stored cals are sortable (I don’t use them much). In the new Servoy 6 you could place it in a calculation instead and calculations are supposed to be sortable. In Servoy 5 and on, you could also use SQL to create a custom datasource and bind that to a form via solution model. That would also make the column sortable.
There may be some other more creative solutions, but I’m only half way through my morning coffee
servoydevguy:
In Servoy 5 and on, you could also use SQL to create a custom datasource and bind that to a form via solution model. That would also make the column sortable.
solutionModel has been available since 4.1, this is definitely an option to consider.
It’s some work to get started, but it’s almost unlimited…
Interesting,
Could You give a bit more detail how to do this ?
Regards,
mboegem:
solutionModel has been available since 4.1, this is definitely an option to consider.
It’s some work to get started, but it’s almost unlimited…
Yeah. But I don’t think ds.createDataSource() was around back then? Maybe it was.
Anyway, if you look under Database Manager > JSDataSet and if you have createDataSource() then you are good to go. You can create a dataset using ds.getDataSetByQuery() which is under the Database Manager. Then once you get your dataset in a local variable (var ds = getDataSetByQuery()), then use var uri = ds.createDataSource() to turn this dataset into a foundset. To bind the new foundset to a form you can use solution model. Get the form as a JSForm with var jsForm = solutionmodel.getForm(). Then change the datasource of the form with jsForm.dataSource = uri; Next, recrateUI on the form to complete the change.
Hopefully that’s clear
servoydevguy:
Yeah. But I don’t think ds.createDataSource() was around back then? Maybe it was.
I’m pretty sure, we started using it quite soon after the solutionModel had become available.
Can anybody confirm if sorting of Calculation is already working? I am using Servoy 6.0.0 and I can’t sort non-stored calculations. Thanks.