Tiered Sort with Popup Field Choices

[Ranger John]

Hey, Folks - I've run into a problem I can't solve.

Background - I have a huge solution file with a full user interface used by multiple organizations.

The problem - Presently, when users are in a given module (say, "Workshops"), they click a "sort" button to pull up the FileMaker sort dialogue. Unfortunately, this gives the user thousands of fields, most of which are used for the UI and mean nothing to the end user.

The goal - I want to set it up so that when the user clicks the "sort" button, he is brought to a layout that contains 3 popups (gSortField1, gSortField2, and gSortField3) and 3 radio button sets (gSortField1AD, gSortField2AD, and gSortField3AD).

I want to have about 10 pre-determined field names that users commonly need to sort by showing in the popups. The user would be able to select:

Sort by - (gSortField1 - Select field name from popup) (gSortField1AD - Select Ascending or Descending from radio button)

Then by - (gSortField2 - Select field name from popup) (gSortField2AD - Select Ascending or Descending from radio button)

Then by - (gSortField3 - Select field name from popup) (gSortField3AD - Select Ascending or Descending from radio button)

A "sort" button would then do the tiered sort.

Unfortunately, the "Sort" script step does not allow for a calculation to determine the name of the field to be sorted.

I could program each possible sort, but that would require coding for all 10 x 2 x 10 x 2 x 10 x 2 = 8000 permutations. For each of a couple of dozen modules. Not gonna happen.

I thought I might be able to get away with 3 unstored calculation fields such as:

CalculatedSortField1 = Case ( gSortField1 = "Title"; Title; gSortField1 = "Start Date"; StartDate; gSortField1 = "Participant Count"; ParticipantCount)

The problem is, if I save this calculation as a container field, I can't sort by it. If I set it as a text field, numbers and dates don't sort correctly.

Thoughts? Any help would be appreciated.

Edited July 21, 2007 by Guest
Accidentally typed "search" instead of "sort".

[comment]

I don't know of a really good solution for this. See also this thread:

http://fmforums.com/forum/showtopic.php?tid/188402/

That said, how many of the theoretically possible permutations are likely to be required in practice?

[Ranger John]

Hi, Comment,

I had actually read that post before posting myself. Unfortunately, it doesn't let the user choose a secondary or tertiary sort.

Your point about "in practice" is well taken. I had hoped to avoid having to limit the end user to what I think they need, but I can probably do a fairly good job of anticipating 80% of their likely sorts and have a screen with a button for each of those.

For example, in the Registrations module, a common sort would be:

Workshop Number > Employer > Name

I'll definitely send FileMaker a request to add the ability to calculate the name of a sort field in the Sort() script step (to be implemented similar to the GoToLayout() script step. I'd be amazed if I were the first person to have this problem.

Thanks for helping me to bounce around ideas. I'll head this direction for now, but if anyone has a less restricting solution, please jump in. :

[comment]

Well, you could do this with a very complex (and slow) calculation. See here for a starter:

http://fmforums.com/forum/showtopic.php?tid/186468/

[Ranger John]

Wow! Here I'm playing around with all sorts of mathematical fanciness trying to figure out how to get numbers to sort appropriately as text, and you had such an easy solution - pad the number with leading zeros so that all results have the same number of digits.

And convert all dates to 20070721 format.

That should allow me to just use the 3 calculated sort fields.

You are indeed a brilliant and dashingly handsome man.

:

Thanks!

[comment]

Unfortunately, it's not that simple.

It *is* simple with dates, times and timestamps because they are always positive integers (or almost always, as it is possible to record negative times). So with a date field, for example, you could do simply:

Right ( "0000000" & GetAsNumber ( Datefield ) ; 7 )

But when it comes to numbers, simple padding will not be enough to deal with the full range, which includes "values from 10^-400 up to 10^400 and the negative values of the same range". Some sort of 'mathematical fanciness' is required.

[Ranger John]

Yup. Though I'm going to take your earlier advice and stick to reality. As long as I account for 1 billion to negative 1 billion, with 2 decimal points (for money fields), it'll suit my needs.

I'll do a full writeup when I have it set up.

Thanks, bud!