Subject Re: order by on different way From Mervyn Bick <invalid@invalid.invalid> Date Thu, 18 Nov 2021 11:46:52 +0200 Newsgroups dbase.getting-started On 2021/11/17 17:53, Dirk wrote: > Mervyn > > that's how i do nowadays, i want to modify to by order and parameters > for searching > > the stammdat.dbf has 6 indexes and want to work with only one on idnummer, The only index I can see on IDNUMMER is used in vraagbak_KONTAKT where this is the linking field to IDNUMMER in the parent table in VRaagBak_FIRMA I don't like having indexes and ORDER BY clauses in the same form so I would change the link to mastersource.  It will, however, not cause any problems if you leave it as it is.  It won't affect any changes to vraagbak_FIRMA.    this.vraagbak_KONTAKT = new QUERY(this)     with (this.vraagbak_KONTAKT)     //   onOpen = class::vraagbak_KONTAKT_ONOPEN     // Calculated field created in Select statement        left = 399.0        top = 66.0        width = 66.0        height = 37.0        database = form.databank_klanten        sql = 'Select c.*,name||" "||voornaam as Voor_en_naam from "communicate" c where idnummer = :idnummer'        params['idnummer'] = "" //don't worry if idnummer is numeric        masterSource = form.vraagbak_firma.rowset        active = true     endwith     with (this.vraagbak_KONTAKT.rowset)        with (fields["task"])           lookupSQL = "select * from job"        endwith        autoEdit = false     endwith If there is more than one contact for a specific idnummer you can order this rowset.        sql = 'Select c.*,name||" "||voornaam as Voor_en_naam from "communicate" c where idnummer = :idnummer order by name' Stammdat.dbf is only used in form.vraagbak_firma as a parent table.  The method used to order this table is quite independent of the method used to order any child table. You can order the rowset by any of the fields in the table.  The table opens in natural order (unless you modify the existing constructor code by adding an ORDER BY clause) but once you have set an order you need to take the ORDER BY clause into account when you change the order. To set the first order, or change the order      fvf = form.vraagbak_firma //to save some typing :-)      fvf.active = false      fvf.sql = substr(fvf.sql,1,at('"stammdat"', fvf.sql)+9)      fvf.sql += " order by fieldname "    //or    //fvf.sql += " order by fieldname1,fieldname2 "      fvf.active = true Mervyn.