phone: (540)338-0194


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 {?Sort Choice} = 'Cust' then {Customer.Customer Name} else
if {?Sort Choice} = 'State' then {Customer.Region} else  {Customer.City}



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.

if {?Sort Choice} = 'Cust' then {Customer.Customer Name} else
if {?Sort Choice} = 'Date' then ToText({Orders.Order Date},"yyyy-MM-dd") else
if {?Sort Choice} = 'ID' then ToText({Customer.Customer ID},"#######") else
{Customer.Customer Name}