Dynamic Sorting?

[Royce]

Morning All,

I've automated just about everything in FM8...but dynamically sorting (on the fly in a nice elegant "parameter-passing script") doesent seem available yet, unless maybe if I set up a detailed alphabetical Value List, to wit...(a...aa...aaa...aaaa...b...bb...bbb...bbbb...c, etc.).

Am I missing something, maybe really obvious, that will do this? Or is there a custom function that someone has done?

Thanks for the time and for indulging my lack of knowledge.

Cheers,

Royce

(Somebody please pass the coffee....)

[comment]

Dynamically sorting what?

[Royce]

Michael,

Sorting any field - dynamically (on the fly). I can sort manually. I can sort through a script with PREDEFINED sort parameters. Is there any way to sort on the fly...so I can use one script and pass the info to the sort so I don't have to set up lots of predefined sorts. The sort function does not take any calculation to define sort parms (you can use a value list, or a summary but no other criteria).

I can't choose which field to sort on the fly...it must be predetermined in the sort, eg. I have a field called Song Title - how do I get the SORT function to receive parameters to sort based on logic I pass it. I can predefine a script (Sort Song Title in Ascending) and then call the script with a button, even do some logic to determine which Sort script to run, but each Sort has to be predefined - I cannot tell it to ... "run a sort on this field name I am passing you and make it descending this time." (if you will). I must first setup a sort on a specific field and in a specific order (descending). Or is there a way around this?

Thanks,

Royce

[comment]

It's sort of possible, but not efficient for what you describe. You could define a calculation field as GetField(gSortField). Then you'd set gSortField to the field you want to sort by, and sort by the calculation field. It needs to be a bit more complex than that in order to do ascending/descending and to sort correctly on any data type, but that's the principle. The problem is, the calculation field cannot be stored - so your sorts will be slow.

I think the best way is to make a general sorting script, storing all anticipated sort scenarios in the form of:

If [ Get(ScriptParameter) = "Title, Ascending" ]

Sort {Restore; No Dialog]

Else If [ Get(ScriptParameter) = "Title, Descending" ]

Sort {Restore; No Dialog]

Else If [ Get(ScriptParameter) = ...

etc.

[Royce]

Yea, I'm currently doing it the way you suggest...with 30 Sorts (15 ascending, 15 descending)...I was hoping there was a more elegant solution...but... at the current FM moment......maybe version 9.0.......

Thanks for the time.

Royce

[LaRetta]

Hi Royce,

One method:

Since vs. 8, requesting a Sort Records script-step or button will open only displaying fields on the current layout. This is a boon considering prior versions - which displayed every field in the table. Users could get in trouble! But since it only displays what is on the current layout, when user selects Sort button, I don't check 'Perform without Dialog' and don't 'Specify Sort Order'. This opens the sort box to Users. It is true that they could select a different table (or all of the table's fields) from the popup but they don't. If they did, they get the long wait they deserve while it sorts on an unstored or a related field 5 levels away. Ha ha.

Another method I've used:

When User clicks Sort button, I take them to a table layout with only a DONE button in the header. This layout contains all the fields I would allow them to sort by. They click the header to select the sort they wish (and resort to their heart's content) then I return them to the original layout (which if same table occurrence will maintain the record set and sort order). If you open another window to sort, simply switch layouts in the open window (back to the original layout) and close the original window. But this only provides one-level sorts (as you know).

I could never specify all the sort possibilities (more than one level) my Users wanted so I gave up. I usually use the first method now. I hope these alternate approaches are worth considering.

LaRetta

Edited March 6, 2007 by Guest
Changed some wording :^)

[Royce]

Good Morning LaRetta,

I like your second example, albeit, I agree with your conclusion and hence, think it might be more productive to perpetually TEACH them how to use it properly and choose what they want vs. just coding for dummies (so-to-speak) all the time.

A large button called "show me just one more time how to choose what I want to sort and then let me at it" with a layout showing them, once again, how to yada, yada, yada....

Thanks for your time...I appreciate the way you hang back and then, after thoughtfully considering all the input, give a well calculated response.

Have a wonderful day.

Royce [8^)>

[BruceJ]

One method for "dynamically" sorting.

In the table to be sorted create a global (SortField.g)

Also

In the table to be sorted create a calc field... I can't remember the calc, but it populates w the contents based on the field name.

Sort the portal on the SortField.g

Then in the starting layout, create a script that Sets the Field (SortFieldName.g) with various FieldNames via parameter on various buttons along the top of your portal.

I got this from here on the forum someplace, a quick search would turn this up. It also gave an example on how to get more complicated and have multiple fields and varry the sort order.

[LaRetta]

What surprises me, is that User-ability to sort (on multiple criteria) is important (most times) and it seems we give them a few feeble options; thinking they are incapable of handling the sort box. It's not like they are changing data! I tightly control sorts on pre-defined reports or processes but staff's ability to manipulate their own lists provides depth of vision and critical data-modeling views.

Most Users are not stupid and are quite capable of selecting their own sort criteria - the sort dialog is simple! The only problem (pre-vs 8) was that 190 fields would be presented (IDs, calculations, Developer fields, aggregates and other such 'junk'). Pre-8, I wouldn't open dialog to that mess and I too implemented processes (such as described by Bruce and Comment); in fact, I still have one in the current solution built in vs 7. But it was time-consuming to build and it is slow and I STILL found it lacked everything Users wanted. Nothing says we have to keep the User on the same layout to provide their sort options - jump to a layout with the fields (usually indexable) so only THOSE fields appear in the dialog, open the dialog for them, then switch them back (all under a Freeze Window - they won't know they were switched to another layout). Why do we go through hoops just to keep Users from sorting their own lists?

Just my opinion ...

LaRetta :wink2:

[comment]

Every situation is different. I am not too happy with letting users get a glimpse of my structure (by being able to peek at a list of ALL fields in the table). But if custom-defined sorts were required, I would certainly consider it.

[BruceJ]

Interesting! I hadn't bothered to check it out beofre - I guess I was stuck in old versions in my head. That's cool if the sort dialogue box only brings up fields in the current layout! I'm goiong to try this. Of course, I'll have to be careful of my field names as some are pretty counter intuitive.

I like the idea of switching to a layout after a fereeze window in order to draw from a cleaned up list of fields to sort from.