Sorting a foundset with a calculation field

Classic Servoy
21

In 6 you can only sort on none stored calculations if you use a function object as the argument to foundset.sort()
see the sample code.

Database sorting can’t be done.

22

Hi Erik,

Did you just foundset.sort() ? That doesn’t work. You need to use foundset.sort(yourJSSortFunction), that will sort things in memory instead of using a SQL query.

Hope this helps.

23

I used the sample code below:

foundset.sort(mySortFunction);
function mySortFunction(r1, r2)
{
var o = 0;
if(r1.id < r2.id)
{
o = -1;
}
else if(r1.id > r2.id)
{
o = 1;
}
return o;
}

How can I access the dataProviderID from the calling onSort method? If I am able to get the dataProviderID, I can change the expressions to (r1[dataProviderID] < r2[dataProviderID]) and r1[dataProviderID] > r2[dataProviderID]. Is there a way to pass the field that is being sorted through a parameter in the method?

[Added] Can I also know if the UI requested for ascending or descending order?

24

the sort callback function is called just with 2 records as param,
so you need to store in a variable the dataproviderID and sortOrder
that you got on the onSort callback on a form, if you want to use them

you may create a feature request to extend the sort callback parameters
with the sorted dataproviderID and the sort order

erikd:
I used the sample code below:

foundset.sort(mySortFunction);

function mySortFunction(r1, r2)
{
var o = 0;
if(r1.id < r2.id)
{
o = -1;
}
else if(r1.id > r2.id)
{
o = 1;
}
return o;
}



How can I access the dataProviderID from the calling onSort method? If I am able to get the dataProviderID, I can change the expressions to (r1[dataProviderID] < r2[dataProviderID]) and r1[dataProviderID] > r2[dataProviderID]. Is there a way to pass the field that is being sorted through a parameter in the method?

[Added] Can I also know if the UI requested for ascending or descending order?
25

or:

function onSort(dataProviderID, asc, event)
{
	foundset.sort(function(r1,r2) {
		if (asc) 
			return r1[dataProviderID] - r2[dataProviderID]
		else 
			return r2[dataProviderID] - r1[dataProviderID]
	});
}

or

function onSort(dataProviderID, asc, event)
{
	foundset.sort(new sorter(dataProviderID,asc).sort);

}
function sorter(dataprovider,asc) {
	this.sort = function(r1,r2) {
		if (asc) 
			return r1[dataprovider] - r2[dataprovider]
		else 
			return r2[dataprovider] - r1[dataprovider]
	}
}
26

jcompagner:
or:

function onSort(dataProviderID, asc, event)

{
foundset.sort(function(r1,r2) {
if (asc)
return r1[dataProviderID] - r2[dataProviderID]
else
return r2[dataProviderID] - r1[dataProviderID]
});
}




or

function onSort(dataProviderID, asc, event)
{
foundset.sort(new sorter(dataProviderID,asc).sort);

}
function sorter(dataprovider,asc) {
this.sort = function(r1,r2) {
if (asc)
return r1[dataprovider] - r2[dataprovider]
else
return r2[dataprovider] - r1[dataprovider]
}
}

Thanks Johan! This is exactly what I was looking for. I can already sort calculations.

27

jcompagner:
or:

function onSort(dataProviderID, asc, event)

{
foundset.sort(function(r1,r2) {
if (asc)
return r1[dataProviderID] - r2[dataProviderID]
else
return r2[dataProviderID] - r1[dataProviderID]
});
}




or

function onSort(dataProviderID, asc, event)
{
foundset.sort(new sorter(dataProviderID,asc).sort);

}
function sorter(dataprovider,asc) {
this.sort = function(r1,r2) {
if (asc)
return r1[dataprovider] - r2[dataprovider]
else
return r2[dataprovider] - r1[dataprovider]
}
}

Is there a way to make it faster? We have thousands of records already and calculation sorting runs very slow. Is there a way to make the sorting faster without saving the calculation as a column?

28

so you have a foundset with thousands of records that you want to sort in memory?
That will never be really fast because to do that sort everything must be loaded in the client, so you will have many queries getting the data.

29

erikd:
Is there a way to make it faster? We have thousands of records already and calculation sorting runs very slow. Is there a way to make the sorting faster without saving the calculation as a column?

Load your form with a SQL query that has your sort on your calculation. Have to do the calculation in the SQL query.