Dynamic Sort Script?

[Dan Rademacher]

I am working on a fairly complex donation tracking database (a few dozen tables, lots of table instances, etc). Fairly complex for me, anyway...

I am trying to figure out a way to have my Reports area allow the user to select, from a preset group of fields, up to seven sort criteria and ascending or descending for each one. This will determine the appearance of Subsummaries and order of each subsum's members (like, group by year descending, then by quarter ascending). Each set of preferences can be saved and reused.

The ui is working fine, but I can't figure out a way to do the scripting without a mind-boggling number of conditionals that lead to dozens of preconcieved sorts. [E.g., years down, qtrs up, mnths up ; years up, qtrs down ; yrs up, months up ; campaign name up, yrs down ; etc, etc, etc.]

Has anyone come up with a way to feed sort preferences from user interface into a sort script?

Thanks!

[Ender]

This should be possible using the GetField() function to dynamically choose which fields to sort on.

Check out my Dynamic Reports example (attached.)

The ascending/descending thing requires an additional calc field for each sort level, but it can be done.

DynamicReport.fp7.zip

[bruceR]

If you and your users are on a Mac you can do dynamic sorts by Applescript very easily.

[Dan Rademacher]

We are on Macs, so AppleScript is an option; and I have done some reasonably involved scripting btwn FM and Quark, though that was about 10 yrs ago...

Ender's dynamic reports solution is pretty nice (thanks!!), and got me going fairly well. But the ascending and descending issue is a little bit of a bear, since I have 7 possible levels of categorization, and each one can be either "ascend" or "descend." Accounting for that in an FM script would take a lot of conditionals (1.0.0.0.0.0.0; 1.1.0.0.0.0.0; ...).

I came up with some calc fields to invert values so all sorts can be "ascending" as far as FM is concerned, based on values that are either left as entered or inverted. Easy for numbers and dates. Can be done by parsing text fields to, a=Z, b=Y, ... But any field that combines letters and numbers is a problem.Solvable, but with a fair amount of overhead.

Maybe many-variable dynamic sorts can be done more elegantly with Applescript? Probably too long an answer to go into much detail, but a couple of hints would be great!

[Ender]

Another thought for the ascending/descending thing. Suppose the fields look like this:

gSortChoice1 (text, global)

gSortAscDesc1 (text, global)

cSortAscending1 (calculation, text result) = Case(gSortAscDesc1="Ascending"; getfield(gSortChoice1))

cSortDescending1 (calculation, text result) = Case(gSortAscDesc1="Descending"; getfield(gSortChoice1))

gSortChoice2 (text, global)

gSortAscDesc2 (text, global)

cSortAscending2 (calculation, text result) = Case(gSortAscDesc2="Ascending"; getfield(gSortChoice2))

cSortDescending2 (calculation, text result) = Case(gSortAscDesc2="Descending"; getfield(gSortChoice2))

...

Then in the Sort dialog, you would sort by each of the calc fields, with the respective ascending/descending choice:

Sort By:

cSortAscending1 (Ascending)

cSortDescending1 (Descending)

cSortAscending2 (Ascending)

cSortDescending2 (Descending)

...

I don't know how well this would perform, probably not very well with lots of records (because the globals in those calcs make them unstored.)

[Ender]

With these dynamic sorts, you should also be aware of issues between field types. Using a date, time, or number field as one of those dynamic fields probably won't give you the result you expect.

I think this can be fixed by padding the numbers (dates and times are numbers too.) with leading zeros.

[Dan Rademacher]

Thanks for the further tips. Things are _almost_ working splendidly. But I'm afraind the last 5% might do me in...

I now have dynamic sorting and report headers, thanks to Ender's suggestions. But the killer is that the subsummaries have to depend on a single field.

So when the script sorts by SortChoice1.Ascend, then SortChoice1.Descend -- if the user has selected "Descend," the sort is right (since Ascend is blank by calc). But the summary part doesn't work. You get one part, for the single blank value in SortChoice1.Ascend.

The only solution I can think of is this:

1. Create duplicate parts, one based on Ascend, the other on Descend.

2. Make _everything_ in them calc fields that populate based on a conditional. Then set them to slide and also reduce size of enclosing part. Then, if everything there is blank, the part should disappear.

Do you think this would work? Already I can imagine some torture related to Summary fields that I'd have to turn into calc fields with Summaries nested in conditionals. But that would mean a separate set of summary fields for each part. Oy!

Maybe I should just scrap the idea of dynamic reports...

[Ender]

I'd go with option 1.