Subject Re: order by on different way From Mervyn Bick <invalid@invalid.invalid> Date Tue, 16 Nov 2021 09:57:09 +0200 Newsgroups dbase.getting-started 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.