Thank you for your post.
The fastest solution, but the most time consuming, would be a sort for each of the permutations.
Another option would be to have one sort on three temporary fields, TEMP1, TEMP2 and TEMP3.
If the person chose Last Name, City, and Zip Code, you could replace TEMP1 with Last Name, TEMP2 with City and TEMP3 with Zip Code. Then, perform the sort. However, this could definitely take a long time, especially if you have a lot of records.
Anybody else out there with a viable solution?
I use your solution, but there is a bit of work involved. What you need is three globals:
gSortOption1, gSortOption2, and gSortOption3 that display as popup menus with value lists containing the 8 sort choices.
Then, you need three calculated text fields that take, for example, the LastName and enter it into the calculated field if gSortOptionX has a certain value.
This is where it gets tricky. You now have 3 text fields to sort by. But, what if one of my options is a number? Surely, we don't want 155 to show up before 20. Dates will also be a problem.
So, your calculated fields need to convert any date fields into a format sortable as text. The easiest is to code it to convert into 20090122 (YYYYMMDD) format.
Numbers are a bit tougher. You need to take into account the highest possible value that might be entered into that field, determine how many decimal places to the left of the decimal are needed, and then take every number and append the appropriate number of zeros to it. In that way, 155 in the real field might come over into the calculated text field as 00000000000155 so that it will sort after 00000000000022, which has one additional zero.
It may be a pain to set up, but you'll save time down the line when you don't have to teach your clients how to use the built-in sort (which can include hundreds of fields that have no real relevance to the end user.)
...and a scaled-down variation of jsalzer's idea is to use his 'Sort on global field' bit to answer the often-related question: "How can I let the user change the sort criterion for a portal?"
Define the portal to display sorted according to the field 'gPortalSort', and set that global field using the same ValueList method suggested.
Numbers are a bit tougher. You need to take into account the highest possible value that might be entered into that field, determine how many decimal places to the left of the decimal are needed, and then take every number and append the appropriate number of zeros to it.
Actually, numbers are a lot more tougher than that: values can go up to 10^400. In addition, they can be negative, or fractions (or both). By the time you accomodate all possible values, it's going to get quite slow (not that sorting on an unstored calculation is ever quick).
> I want users to be able to sort on up to three categories
Why don't let your user sort using a Table View ?
Remember that you can:
1) Sort by clicking the name field
2) Sort multiple columns by Shift-Clicking them
3) Change the sort order of multiple fields by reordering columns so that the first on the left will sort for first.
I have a similar situation.
Over 1,500.000 records and I don't want to deal with the user frustration when they sort before a find or sort in error by clicking a column heading.
For data display only, I plan to pre-sort the data for the know requirements and then creat a seperate Sort_Key field that is sorted by a script.