Get sort order via parameter, with mixed field types

[amoeba102]

My financial users would like to be able to sort their reports various ways. Some of the fields are string, some date and some numeric.

I've made a parameter {?sortfield}with a drop box with the field names.

I have a formula called {@sortby}that gets the correct field, using "name" as a default:

if {?sortfield} = "Tran Date" then totext({LEDGER.ENTRY_DATE})else
if {?sortfield} = "Tran Type" then {LEDGER.LEDGER_TYPE}else
if {?sortfield} = "Status" then {LEDGER.STATUS}else
if {?sortfield} = "Amount" then totext({LEDGER.AMOUNT}) else
if {?sortfield} = "Tran Description" then {LEDGER.DESCRIPTION}else
if {?sortfield} = "Name" then {@name} else
{@name};

Then I use {@sortby} as my sort order.

My problem, obviously is the date and amount fields being non-string. the date sorts alpha and the amount ignores negatives.

Any ideas on how to get around this?

 

[mbarron]

Create a second formula - use this one to sort the records, and use your current formula to display.

stringvar sort;

if {?sortfield} = "Tran Date" then
(stringvar dater:=totext({LEDGER.ENTRY_DATE});
sort:=right(dater,4)+left(dater,2)+mid(dater,4,2))
else
if {?sortfield} = "Tran Type" then sort:={LEDGER.LEDGER_TYPE}else
if {?sortfield} = "Status" then sort:={LEDGER.STATUS}else
if {?sortfield} = "Amount" then sort:=totext(1000000+{LEDGER.AMOUNT},"000000000.##&quot else
if {?sortfield} = "Tran Description" then sort:={LEDGER.DESCRIPTION}else
if {?sortfield} = "Name" then sort:={@name} else
sort:={@name};
sort

For the line in blue above - You would need to set your default date format to mm/dd/yyyy so that the formula can sort it correctly as yyyymmdd.

For the line in red - if your lowest Amount is less than -1000000, change this number to something bigger and add more zeroes to this "000000000.##"

Mike