custom sort

Classic Servoy
1

Hello all. I’m new to Servoy (from FM, mainly) and am finally able to do lots of things I only dreamed of in FM, so perhaps I’m getting greedy…

I have a valuelist of all values from table B that I access in a form belonging to table A (to establish the relationship between A and B). I’d like to sort that valuelist by the distance between the thing in table B and the thing in table A. Both tables have latitude and longitude fields, and I have a global function which caldulates the distance between two lat, long pairs.

I tried to establish a calculation in table B based on the currently selected record in table A

return global.latLongDistance(latitude, longitude, forms.tableA.latitude, forms.tableA.longitude);

but Servoy doesn’t like this and anyway, I’m not sure if you can sort on calculations.

Any other ideas about how to do this?

Thanks all.

2

Hi,

I have feeling you have to store the calculation to be able to sort.

Now there are other more un-FileMakerish ways of doing this. The best method depends on the likely number of values in your valuelist.

If you know some SQL you can very easily create your own value list. To give you an idea, here is some code I use to look at a list of purchase orders and get a list of suppliers…

var query = "SELECT purchase_order_supplier_name, min(purchase_order_supplier_id) FROM acc_purchase_orders ";
query += "WHERE purchase_order_status = 'Sent' GROUP BY purchase_order_supplier_name ORDER BY purchase_order_supplier_name";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 500);

application.setValueListItems('acc_stock_in_suppliers',dataset);

Once the user has selected a supplier (stored in location_current_supplier_id) I create a list of Purchase Orders…
This is done in an ondatachange on the dropdown menu.

var query = "SELECT purchase_order_number, purchase_order_id FROM acc_purchase_orders WHERE ";
query += "purchase_order_supplier_id = ? ORDER BY purchase_order_number DESC";
var args = new Array();
args[0] = location_current_supplier_id;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, args, 500);
lapplication.setValueListItems('acc_stock_in_pops',dataset);

Now you don’t need to know much SQL to use Servoy, but if you do, it will open up a new world. I have used FileMaker for 10 years - I thought I could build anything with it - now a realize there was a high fence around my world, obscuring a lot of things - after 18 months with Servoy I have broken down that fence - and I can really build anything I like :slight_smile:

TIP: If you want to learn SQL, you may find it much easier to install and learn PostgreSQL or mySQL than Sybase. Sybase is good if you are aiming for larger more corporate customers - but you said you came from a FileMaker background :wink:

3

Ok. Thanks, I’ll consider learning SQL then. Before I embark on that project, I see how I can use SQL to populate the valuelist myself but can I define a SQL function (for the order by clause) to calculate the distance between the person in table A and the one in table B? Perhaps something like:```
var query = 'SELECT personB_id, first_name, last_name, latitude, longitude FROM tableB ORDER BY distance(latitude, longitude, " + tableA.latitude + ’ ’ + tableA.longitude + ‘)’;


Thanks again.
4

Ok. I think I’ve got it with two methods in table A:

var query = 'select id, firstname, lastname, latitude, longitude FROM tableB';

var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, 500);

var queryArray = new Array();
for(var i = 0; i < dataset.getMaxRowIndex(); i++)
{
	queryArray[i] = dataset.getRowAsArray(i+1);
}

queryArray.sort(latLongCompare);

var displayValArray = new Array();
var idArray = new Array();

for(i = 0; i < queryArray.length ; i++)
{
	displayValArray[i] = queryArray[i][1] + ' ' + queryArray[i][2];
	idArray[i] = queryArray[i][0];
}

application.setValueListItems( 'the_list',  displayValArray,  idArray);

where latLongCompare is another method in tableA:

var firstRow = arguments[0];
var secondRow = arguments[1];

var firstDistance = globals.latLongDistance(latitude,longitude,firstRow[3],firstRow[4]);

var secondDistance = globals.latLongDistance(latitude,longitude,secondRow[3],secondRow[4]);

return(firstDistance - secondDistance);

I then added this to the method which is run onRecordSelect as well as those which run when the latitude and longitude are changed.

This seems terribly inelegant and I’d love for someone to point out a better way but, at least for now, woo hoo. I could never do this with filemaker!

Thanks again swingman for setting me on the right track.

5

aUser:

var query = 'SELECT personB_id, first_name, last_name, latitude, longitude FROM tableB ORDER BY distance(latitude, longitude, " + tableA.latitude + ' ' + tableA.longitude + ')';[/quote]

Yes, you can use any SQL function in the query, including ones you have written yourself. Or you can process the data in Javascript.

By the way, PostgreSQL has extensions for dealing with geographic objects – try googling PostGIS.