Subject Re: Reporting on current orders only From Mervyn Bick <invalid@invalid.invalid> Date Tue, 13 Feb 2024 11:33:52 +0200 Newsgroups dbase.getting-started On 2024/02/12 14:45, Craig wrote: > Thanks for the help I have been able to get it to filter using the Where clause. I have also used the Order By clause to order the the report alphabetically. > > For the orders though we don't just want to sort them alphabetically but we want to group the orders that have a due date and those that don't. Then have these each sorted alphabetically. This is so that they are grouped together. I have tries to use the Group sql clause but I can't get that to work. > Neither SQL nor localSQL has a "group" clause.  They each have a "group by" clause.  This is, however, meant to be used where an aggregating function i.e. sum(), count(), etc is used to create a calculated field in a SELECT statement. An instance of a group object in a report has a "group by" property which requires the rowset to be ordered on the fieldname assigned to the property. I assume you are simply printing a list of orders (without any details of items ordered which is where a group object and it's group by property would come into play) so neither of these options is applicable. If you wanted the orders without a due date listed first the solution would be simple.     select * from whatever order by due_date,company You want the orders without a due date at the end of the list with the oldest order first.  This would be easy enough with "proper" SQL but localSQL isn't up to the task.  You will, therefore, need to create a temporary table for use in the report.  The fieldnames in the temporary table will, apart from an extra field used to order the rowset, be the same as in the main table.  It will, therefore, not be necessary to make any changes to the report other than to change the table name in the query's SELECT statement. You could put the following code in a separate .prg file but the best place is in the header section of your report i.e. above ** END HEADER -- do not remove this line This will ensure that the temporary table is created and is, therefore, up-to-date, every time the report is run *-------- Start of code ---------- cSafety = set('safety')  //The default is OFF but may have been changed set safety off //We need safety OFF to suppress "table exists" message select * from whatever save to whatever_temp // Will overwrite table alter table whatever_temp add due_date_char char(8) //Will add a field use whatever_temp //Switch from localSQL to DML to be able to use DTOS() replace all due_date_char with dtos(due_date) // yyyymmdd in char field //Where due_date is blank, due_date_char will be left blank. replace all due_date_char with '99999999' for empty(due_date) use set safety &cSafety  //Leave things as we found them. *--------- End of code --------------- The "legal eagles" in our midst have a Latin term that's applicable here. "Mutatis mutandis" i.e. "With the necessary changes."  Use the appropriate table and field names. :-) In the report's query    sql = 'select * from whatever_temp order by due_date_char,company' Mervyn.