Sorting a foundset with a field from a relation

Classic Servoy
1

Hi,

I need to sort a foundset by a field from another foundset. There is a relation from the first foundset’s table to the other foundset’s table.

E.g. there is a foundset A and foundset B
foundset A has the fields a1, a2, a3 and foundset B has the fields a1, b1, b2
there is a relation from foundset A to foundset B
Is it possible to sort (foundset.sort()) foundset A by the field b1?

Thanks in advance

Hareendra

2

Hi,

Yep, foundset_a.sort(relation_a_to_b.b1 asc)

I use it all the time…

Regards,

3

just one tip (if you’re already aware it may not be necessary at all :) )
when using this code, make sure the relation is of type ‘left outer join’
if the relation is of type ‘inner join’ and you sort foundset A on a field in foundset B when foundset B does NOT contain a related record, this specific record(s) will be omitted…

4

Hi all,

I have the same case, my two tables are orders and customers bounded by the customer’s code and there’s a relation from orders to customer to show its name instead its code. I show orders in table view but when trying to sort by customer’s name it doesn’t work, it does nothing at all.

I have to create a simple sort function:

var field = arguments[0];
var dir = (arguments[1]) ? ' asc' : ' desc';

// When trying to sort customer's name column, foundset.sort is like this: foundset.sort(orders_to_customers.name asc)	
foundset.sort(field + dir);

With that sort function, the column of customer’s name do the sorting but wrong, I get orders sorted by customer’s code instead of customer’s name , and that’s not the wanted result.

Is this the normal behavior or there’s another way to do this?

Thanks in advance!

OS: Windows 7
Servoy: v4.1.7
DB: Postgres v8.3

5

DannyC,

No, if you call foundset.sort(‘orders_to_customers.name asc’) then it should sort on the name field of the related customer, not on the code.

Can you try it on a small sample?

Rob

6

Thanks Rob, I tried a small sample in a test workspace and it worked, but when I tried again in the main solution it didn’t. But this time I realize what’s the problem.
The two tables, orders and customers, are in two different databases but if I copy customers table into the database of orders and create a new relation for testing, the sorting works as expected. When the two tables are in the same database it’s not necessary to set the onSortCmd property of the form with the sort function, in this case Servoy performs the sorting normally.

Is it possible that this can be a Servoy limitation? I mean the possibility to sort a foundset by a related column if tables of the relation are on different databases.
If it’s so, why Servoy does nothing when trying to sort that column when onSortCmd is not set, but when it is set Servoy does the sorting by the binding field (customer’s code) ?