Subject Re: order by on different way From Dirk <non@non.com> Date Wed, 17 Nov 2021 05:58:22 +0100 Newsgroups dbase.getting-started Op 16/11/2021 om 8:57 schreef Mervyn Bick: > On 2021/11/15 22:17, Dirk wrote: >> >> hello, >> >> i need some explanation about :order by >> >>     this is placed in open :  form.vraagbak_sort = >> form.vraagbak_firma.sql >> > .... >>         cSort.sql = form.vraagbak_sort+[order by name] > ..... >>   1 ) when i create a query with a join between  firma data and >> contact   data  i can order by name or whatever >> >>   2)  when i use 2 queries and conected with masterfields .. >> >>       get message invalid use of keyword By > > You can't use both an index and an ORDER BY clause to order a rowset. It > has to be one or the other. > > When you use masterfields/masterrowset to link a child table to a parent > the child table MUST have an active index on the linking field.  You can > use a compound index but the first field in the index expression must be > the link field.  If you want to change the order of the child rowset you > will need to change the index of the child query to a different index > which would, of course, need to be a compound index.  You can't simply > tack an ORDER BY clause on to the end of the SELECT statement. > > If you change to using mastersource to link the child table to the > parent the child table must NOT have an index.  dBASE uses a parameter > driven query behind the scenes to select the child records for the child > rowset.  For this rowset you can add an ORDER BY clause which, if > necessary, can include multiple fields for which the order can be set > individually to ascending or descending.  (Something that can't be done > with a dBASE index. :-) ) > > You would also need to add the link code to form.vraagbak_sort > > cSort.sql = form.vraagbak_sort+[ where fieldname = :fieldname] > or > cSort.sql = form.vraagbak_sort+[ where fieldname = :fieldname order by > name] > > This assumes that form.vraagbak_sort contains a simple SELECT without > any conditions.  If this is not so then life will be a bit more > complicated. :-( > > The parent rowset can be in natural order or it can be ordered by using > either an index or an ORDER BY clause irrespective of whether > masterfields/masterrowset or mastersource is used to link the tables. To > my mind though mixing indexes and ORDER BY clauses in the same program > is not good practice. > > Mervyn. >    Mervyn, thank you for the point of view, i just renew a form with several filters and these i want to replace with param. the form itself can create basic info as name and so, even filter on place postcode modifying the add function to a separate form to add new info will help. something else : what's your opnion about datalink, i know it's make live easier, but is using the field value not better ? regards Dirk