Prompt for a sort field from fields of mixed data types:
It is fairly easy to use a prompt to select a sort or group field, if all of the fields you are choosing from have the same data type. You simply create a parameter prompt called {?Sort Choice} with three default choices (Cust, State, City) and set the parameter so that they must pick one of these three. Then write a formula field called {@Sort Field } that selects the appropriate field based on the user's choice. You use {@Sort Field } as your sort or group field.
If, however, the three
fields don't all have the same data type you will need to convert numeric and
date values into strings using the ToText() function. This is because the
Then and Else values in a formula must have the same data type. Then, to
assure that the values sort correctly once converted, you have to use format
strings within the ToText() function. A numeric needs to have spaces or
zeros on the left to keep numbers of varying lengths sorted in the proper order.
A date will need to start with a 4 digit year to sort correctly. The
following formula shows both format strings. It chooses between a
character, a date or a numeric as the sort, based on {?Sort Choice}.
In the third line, the numeric format string should have as many pound signs (#)
as there are digits in the highest value for that field.