how to sort a table frame by iser selected column 2

[GerryGoldberg]

I have a table frame that contains records filtered by a date range. I would like to be able to select a sort sequence for these records by simply clicking on the header of the column on which to sort. Do I need a secondary index on each of the displayed columns or is there some other way to do this?

Thanks,

Gerry Goldberg

 

[footpad]

Gerry,

No, you need a secondary index for each change in sorting that you want the user to be able to choose from. For example, if you want to sort a date field in ascending and descending order, you'll need two indexes.

If this is a master table or the only table on the form, you can present a list of names using a PopupMenu and then choose the target index based on the value the user selects. Here's a rather involved example:

method pushButton(var eventInfo Event)
var
   pmSortOrd   PopupMenu  ; The Sort By options.
   strChoice   String     ; The user's response to the menu.
   strIndex    String     ; The active index of the table.
   tcMaster    TCursor    ; Variable pointing to form's master table.
endVar

   ; First, determine the active index.  We need this to know which
   ; menu item to check in the Sort Order menu.
   strIndex = ""                      ; Initialize to known value.
   tcMaster.attach( EMPLOYEE )        ; Synchronize to screen data.
   tcMaster.getIndexName( strIndex )  ; Get name of the current index.
   tcMaster.close()                   ; Explicitly close TCursors.
   strIndex = Upper( strIndex )       ; Convert to upper case

   ; Now, build the Sort Order menu.
   If strIndex = "DEPT" then
      pmSortOrd.addText( "&Department", menuEnabled + menuChecked )
   else
      pmSortOrd.addText( "&Department" )
   endIf

   If strIndex = "" then
      pmSortOrd.addText( "&Employee ID", menuEnabled + menuChecked )
   else
      pmSortOrd.addText( "&Employee ID" )
   endIf

   If strIndex = "LASTNAMEFIRSTNAME" then
      pmSortOrd.addText( "Employee &Name", menuEnabled + menuChecked )
   else
      pmSortOrd.addText( "Employee &Name" )
   endIf
   If strIndex = "JOBCODE" then
      pmSortOrd.addText( "&Job Code", menuEnabled + menuChecked )
   else
      pmSortOrd.addText( "&Job Code" )
   endIf

   ; Now, display the menu and wait for the user to respond to it.
   strChoice = pmSortOrd.show()

   ; Evaluate the user's response.  If the user cancelled then menu,
   ; then strChoice contains the default value ("").  Otherwise, it
   ; contains the text of the menu command that the user selected.
   ;

   If strChoice <> &quot;&quot; then
      switch
         case strChoice = &quot;&Department&quot;    : setIndex(&quot;DEPT&quot;)
         case strChoice = &quot;&Employee ID&quot;   : setIndex(&quot;&quot;)
         case strChoice = &quot;Employee &Name&quot; : setIndex(&quot;LASTNAMEFIRSTNAME&quot;)
         case strChoice = &quot;&Job Code&quot;      : setIndex(&quot;JOBCODE&quot;)
         otherwise : libApp.menuNotDefined( strChoice )
      endSwitch
   endIf

endmethod

I should note that while this approach is fine for small uses, it's not an ideal one for large projects because it's rather maintenance heavy. If you're planning on incorporating this technique into sevral forms, then it may be wiser to create a table holding the name of the form, the labels for the menu, and the names of the indices.

If you tackle that approach, then you'll want to:

1. Use a tCursor to open the table.
2. Use SetRange to limit the TCursor to the values matching the form name.
3. Scan through the restricted (ranged) view and add the menu item names and the index names to a dynArray.
4. Close the TCursor.
5. Loop through the dynarray to populate the popup menu.
6. Show the menu and wait for a reponse.
7. Evaluate the response with the tags in the dynarray.
8. If you find a match, attach the Tcursor to the table frame or other UIObject, use switchIndex to change the active index (as shown above), and then resync the TCursor to the UIObject.

I know it seems like a lot of work, but the latter approach is actually more portable than the example I posted. Also, it's easier to maintain, for you only need to add one record to the underlying table to incorporate a new secondary index.

Now, if you need this for linked, detail tables, it's a little more difficult, but not that much so. You need two basic things:

1. A set of secondary indexes that sort the data in the desired order while retaining the ability to link to the master record. This usually means your linking field(s) will appear first in the secondary index definition.

2. Use dmUnlink and dmLinkToIndex to change the active index for the detail table.

It's a bit more work and this is already a long message, so I'll put this on my list of articles to write up for the site.

Hope this helps...

-- Lance

 

[footpad]

Gerry,

Oops, I forgot to include the code for the setIndex() custom method:

[method setIndex( strNewIndex String )
; -------------------------------------------------------------------------
; This routine switches the active index of the form's master table to the
; index whose name is specified in the strNewIndex parameter. This
; implementation demonstrates how you can accomplish this in a generic
; manner, one that doesn't require foreknowledge of the objects on the
; form. However, it does require a couple of key design choices to work
; correctly:
;
; -- The master table must have a table alias assigned in the data model.
; -- The master table must have a repeating object placed on the form,
; such as a table frame or an MRO.
; -- The name of the MRO must be the same as the table's data model alias.
; -- There must be no named object between the form and the table's
; repeating data object.
;
; These conditions are valid for this form, which also shows how careful
; design choices can let create ObjectPAL routines that can be more easily
; copied between applications.
;
; For clarity, only general error checking is performed.
; -------------------------------------------------------------------------

var
tcActive TCursor ; Pointer to the table underlying the active object
fmActive Form ; Pointer to the current form.
uiTarget UIObject ; The repeating object bound to the master table.
strValue String ; A string value used different ways.
endVar

fmActive.attach() ; attach to the form
strValue = fmActive.tableName ; get name of master table
strValue = dmGetProperty( strValue, "Name" ) ; get master table alias
uiTarget.attach( strValue ) ; attach to object so named
tcActive.attach( uiTarget ) ; attach to data for object
tcActive.switchIndex( strNewIndex, Yes ) ; switch the index
uiTarget.resync( tcActive ) ; update the screen
tcActive.close() ; formally close tcursor

endMethod[/code]

Sorry...

-- Lance