Jump to content
Sign in to follow this  
Ranger John

Tiered Sort with Popup Field Choices

Recommended Posts

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 by Guest
Accidentally typed "search" instead of "sort".

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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. :

Share this post


Link to post
Share on other sites

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/

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.