This is a bit long, but bear with me.

I have done this sort of thing a few times, but in this case it's a bit different. Here's
the scenerio.

I allow the user to create custom list sorts. This is accomplished by allowing the
user to to pick fields in any order they want. They get a list of plain text field names ,
which I conver to actual field names.

Each field they select is stored in a table called 'ListSortDetails', which has a sequence
column to specify the order.

So they could pick 'Customer Name', 'Order Date' and 'Total Amount' from the list of
available columns. I then write out one record each for 'CustName, OrdDate and TotAmount'.

When the want to sort by a column they can simply click on a column header. The column
header has a property storing the column's associated field name, which is then passed to
the LoadList method.

To select a custom, multi-column sort they pick it from a list, which runs the following
code:

IF NOT USED("ListSortDetails")
  SELECT 0
  USE (oApp.cDataPath + "ListSortDetails")
ENDIF

SELECT FldName;
  FROM ListSortDetails;
  WHERE Parent_Id = nSortId;
  ORDER BY Sequence;
  INTO ARRAY Temp1
	
FOR nField = 1 TO ALEN(Temp1)
	
  cSort = cSort + ALLTRIM(Temp1[nField]) + ","

ENDFOR
	
cSort = ALLTRIM(cSort)
cSort = LEFT(cSort, LEN(cSort)-1)

ThisForm.LoadList(cSort)

In either case, the single column header or a custom sort, the field name or sort order
string is passed to LoadList. When a sort value is passed in, the SQL's
SORT BY clause has the parameter value appended to it.
Here's the code:

LPARAMETERS cSortOrder

LOCAL cListItem, nItemIndex

WITH .lstData

  .ClearList()

  IF EMPTY(cSortOrder)
    cSortOrder = "e.internal_id"
  ENDIF

  select e.id,;
      e.caption,;
      e.internal_id,;
      l.Caption as location,;
      e.cur_meter,;
      e.model,;
      e.serial_no;
    from equipment e;
    left outer join locations l on l.id = e.location_id;
    order by &cSortOrder;
    into cursor temp1

   IF _TALLY > 0

      SELECT Temp1
      SCAN
		
        && ... Add column info here
		
      ENDSCAN
	
   ENDIF

ENDWITH

The problem now is the JOIN. When this code is run against only one table, all
ListSortDetails needs to store is the column name.

In the case of the JOIN, I now need to store the table alias also.

This seems like it could be real cumbersome.

Anyone have a better idea?