How to make order in grid display correctly ?

Scriptcase 8 Discussion
#1

I have 2 table :

  • Propinsi table

  • Kota table

I create Grid Kota with SQL syntax :
SELECT
kot_sn_pk,
kot_usr_sn_fk,
kot_prp_sn_fk, // this field is linked to propinsi table to get propinsi
kot_kota
FROM
900_kota_kot
WHERE
kot_usr_sn_fk = [usr_sn_pk]
ORDER BY ??? // I want to add ORDER BY Propinsi and then Kota

The result without ORDER BY is like this :

How do I setup ORDER BY that I want ?

Propinsi data.PNG

Kota data.PNG

Grid Kota.PNG

Kota table.PNG

propinsi table.PNG

#2

The result if I sort Propinsi ascending :

The result if I sort Propinsi descending :

SC sort based on the key, not based on display field. How to sort based on display field ? Do I have to change SQL syntax or there is another trick ?

sort descending.PNG

sort ascending.PNG

#3

You can’t sort by something that’s not there, so you have to join the tables to get the fields you need.

SELECT
kot_sn_pk,
kot_usr_sn_fk,
kot_prp_sn_fk,
prp_propinsi, // you need this field in the record set
kot_kota
FROM
900_kota_kot k LEFT OUTER JOIN propinsi p ON k.kot_prp_sn_fk = p.prp_sn_pk //join both tables
WHERE
kot_usr_sn_fk = [usr_sn_pk]
ORDER BY prp_propinsi, kot_kota

That should do it.

jsb

#4

OK. Solved already. Thank you jsb.