Tips and Tricks: Auxiliary Field Index

[Jay Ashworth]

So I had this interactive process where I needed the user to see the
records sorted in an unusual order:

I needed all records with no Student ID sorted first, in descending order
by Match Percentage, then all records *with* a Student ID, in descending 
order by Match Percentage.  That is: I did *not* want them sorted by what
the Student ID *was*, just that there was one present.

(If you're thinking that once the problem is well defined in a single 
paragraph, most of the corresponding implementation becomes fairly obvious,
you have much to go on. :-)

Because their system is on 4.8, I didn't have descending indexes, though I
did have multi-field ones.  

So I built 2 auxiliary fields in the record, down at the end:

"Match Percent Descending", which is "100"- the match percentage field, and

"Student ID Assigned", which is set to Y if you put anything in the Student
ID field at all.  Since it's the same value no matter what, all records 
without it will sort first, all with, later... but the 2 groups will still
sort internally by the other field.

As with all Auxiliary fields, this does place the onus on your code to
maintain them, and there's no good place to document that, except *within* 
the code itself, which doesn't stand out, but such are the vagaries
of maintenance programming in a 30 year old 3.5GL.  :-)

Cheers,
-- jra
-- 
Jay R. Ashworth                  Baylink                       jra at baylink.com
Designer                     The Things I Think                       RFC 2100
Ashworth & Associates     http://baylink.pitas.com         2000 Land Rover DII
St Petersburg FL USA               #natog                      +1 727 647 1274