Any way to build sort criteria programmatically?

[kennedy]

Rather than using the Sort dialog to specify the sort criteria, is there anyway to build up the sort criteria programmatically?

I'd like to implement a button on each column header that *adds* that column to the top of the existing sort order. In that way, you can build up a three-field sort by simply clicking on the three column headers in the opposite order that you want them sorted. Any way to do that short of having a separate script for each combination of columns?

[LiveOak]

The only way I can think of is pretty ugly. You can sort on a field which you change based upon the sort order desired. The only problem is that you must change this field in all records (or all found records).

-bd

[harryk]

It might be an idea to use a calculation field especially used for the sorting. You could string (is the english word concenate..I'm not native english) the one, two or three fields to sort on together in that calculation field in the order you specify with buttons. This involves some neat scripting, with a global field keeping track of the size of the sort pile, and some sort of identification of the sort-order momentarily chosen.

Disadvantage is the extra calculation field taking up space, but the fields to concenate in do not have to be complete. A left(field,10) is often enough for sort purposes.

The actual making of this, with the scripting, is a nice left-brain challenge..if I was unemployed, I would set it up for you..something for the weekend..)

Harryk

[kennedy]

Ahh, yes... didn't think of that. Building on that idea...

If I wanted to support a stack of up to N sorts, then I could define a Global Number "SortCount" that gets incremented each time someone clicks a sort button. Then I need a Global Number "Sort [fieldname]" for each field that gets assigned the value of the just incremented Sort Count whenever its button gets clicked. An Unsort button could be done by simply setting SortCount back to zero.

With that done, I add a Calculation Text field that concatenates together the results of N Case statements. Each Case statement is identical except the first uses SortCount, the next SortCount-1, the next SortCount-2, and so on. N=3 is probably big enough for most situations. Beyond that, they can use the Sort dialog.

Inside the Case statement, we'll need to convert numbers to padded Text and format Dates YYYYMMDD. It'll be a HUGE case statement! User-defined functions that take parameters would be nice.

Then you just Sort on the computed Text field.

Questions:

Will FileMaker cache the results of an unstored calculation during the sort, or will it recompute it each time it hits it in the sort???

Just how slow is this solution likely to be?

Any ideas on how to simplify, speed up, or otherwise improve this?

Thanks.

[GlV]

Hello,

I would go for three globals and three calculation fields. The calculation should be the getfield function for each global. Then you need have a script that sorts the calculation fields.

Now by filling in the globals with the fieldsnames that you want to sort and running the script the problem solved.

Things to keep in mind: this will work for text fields you would need to expand it if some of the fields that you want to sort are number ,date or time fields.

Glenn.

[harryk]

Right you are..I fiddled around a bit with my idea, and it works. For numbers you'll use NumtoText but remember to use pre-zero's in some way..else the sort fails on the numbers. For date fields you might use in some way the internal number used for the date, not the YYYYMMDD thing. Remember it's not for people, it's for the FM sort-manager.

For the ORDER you can use a global repetition field, but I used a global text field, that looks like "132" when you have 3 fields concatenated in the order 1, 3,2. It involves some left() middle() and right() scripting

The Sortcount as you name it, adds up: 1,2,3,1,2,3 etc.

The text calculation field can be made up using three choose() options concatenated together.

A huge case statement? From what I tried, no it's not huge. When my example is finished, I'll upload it here.

Your question about caching or recomputing the sort, I've no idea. The sortfield changes each time a column header is clicked, I think it cannot be stored.With very huge databases postponing the actual sort until all choices are made, might be an option; in my example the sort is done as soon as a column-header is clicked. What clicking does in my fast-as-macdonalds-example is:

1 high up the sortcount

2 setting the sort-order

3 perform the sort

the calculation fields #sortfield and #sortIDtext do their work automatically..

Harryk

[kennedy]

Ahh, yes! Building on Glenn's suggestion, you could define a "Sort [fieldname]" calculation that computed a sortable Text for each non-Text field. Or, much more robustly... avoiding padding and such...

Define 12 globals and 12 GetField calculations: SortText1-3, SortDate1-3, SortNumber1-3, and SortTime1-3. The PushSort script does 12 assignments. And then you set the new field into the appropriate #1 field, based on type. Your sort sorts on all twelve... but of course, only one of each number are non-empty.

Simple calc fields will allow you to display the sort number in the column header. And unsort button is simply 12 assignments.

Any reasons that won't work? Will Sort work fine with 12 calculated keys, 9 being empty?

Any other, even better ideas??

[GlV]

Hi,

Well there is a better way...

If you want to make the sort for all type off field you must make 2 calculation fields...

1: text as result

If(PatternCount(FieldType(Status(CurrentFileName), globalfield) , "text") , GetField(globalfield) , "")

2: number as result

If(PatternCount(FieldType(Status(CurrentFileName), globalfield) , "text") , "" , GetField(globalfield) )

If you globalfield refers to a Text then the first calculation will be sorted in the other cases the second field will be sorted.

Because that time and date fields can be treated as number fields. We can get them into the second calculation.

Remember to modify the sort script on both calculations.

Glenn

[kennedy]

Okay, I am writing this up anyway, might as well post it for the next reader:

Global Text SortOn1

Global Text SortOn2

Global Text SortOn3

Calculation Text SortText1

= If(Position(FieldType(Status(CurrentFileName), SortOn1) , "Text", 1, 1) , GetField(SortOn1) , "")

Calculation Number SortNum1

= If(Position(FieldType(Status(CurrentFileName), SortOn1) , "Text", 1, 1) , 0, GetField(SortOn1))

Calculation Text SortText2

= If(Position(FieldType(Status(CurrentFileName), SortOn2) , "Text", 1, 1) , GetField(SortOn2) , "")

Calculation Number SortNum2

= If(Position(FieldType(Status(CurrentFileName), SortOn2) , "Text", 1, 1) , 0, GetField(SortOn2))

Calculation Text SortText3

= If(Position(FieldType(Status(CurrentFileName), SortOn3) , "Text", 1, 1) , GetField(SortOn3) , "")

Calculation Number SortNum3

= If(Position(FieldType(Status(CurrentFileName), SortOn3) , "Text", 1, 1) , 0, GetField(SortOn3))

Then you simply Sort on those six fields in the order above and define the script:

SortOnFields

-- Sort[Restore, nodialog]

And then for each field, you need a script:

SortOn[Fieldname]

-- PushSortOn

-- SetField[sortOn1, "Fieldname"]

-- SortOnFields

where:

PushSortOn

-- SetField[sortOn3, SortOn2]

-- SetField[sortOn2, SortOn1]

and then to unsort, you might want:

SortOnNothing

-- SetField[sortOn3, ""]

-- SetField[sortOn2, ""]

-- SetField[sortOn1, ""]

-- Unsort

Then if you add a calculation for each field:

Calculation Number SortOnFieldname

= Case(SortOn1 = "Fieldname", 1, SortOn2 = "Fieldname", 2, SortOn3 = "Fieldname", 3)

You can then have a small 1, 2, or 3 appear in the column header to indicate what order it is in the sort. Put that on top of a small button that invokes the script SortOnFieldname and you have a sweet way to do multi-field sorts without subjecting the user to the Sort dialog!

Thoughts? Issues?

Thanks!

[harryk]

The idea using getfield to fill (more then one) calculation fields to sort on appeals to me.

Do remember that the getfield function is from version 5.5 on.

As I was on the path of the using of one calc-field with the fields to sort on concatenated in, I finished that, and streamlined it. I uploaded it in the section 'Sample files'.

Harryk

[kennedy]

To see that in action, check out the List layout of my Starter Template posted in the Sample Files forum:

http://www.fmforums.com/threads/showflat.php?Cat=&Board=files&Number=46219&page=0&view=collapsed&sb=5&o=&fpart=1

HTH someone.