Portal Sorting

[Jimjmn]

I have a portal with about 8 fields and I want to be able to sort the columns by clicking one of the 8 column headers. I have tried to make the header a button and use the sort record function to no avail. What am I doing Wrong?

[Ender]

It's not a simple one-step thing. See here for a couple ways to do it:

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

[Stuart Taylor]

portal sort orders a set when you define the portal or the relationship.

2 options (i can think of).

1. use the GetField function and sort the portal by that field. Use a global field to specify the name of the field to get. (can be slow)

2. Create multiple portals sorted in different orders then use a tab object to switch between the portals. (quicker performance)

[Stuart Taylor]

hey Ender,

good work, had forgotton about that.

[Ender]

Yeah, now that I finally got all my users running FM8/8.5, I'm finally getting around to using some new tricks, like the tabs for portal sorting buttons. Although this technique requires a little futzing to position everything right, the performance is super fast. When you compare that to the overhead and performance of the unstored calc technique, it's a clear winner.

[Genx]

Really now? Must try it out then!

What sort of volumes of records are we talking though for a noticable change in speed?

[Stuart Taylor]

Hey Genx,

Volume don't matter

Check out the example. With any volume its the difference between thunder and lightning for the user.

<--- never noticed the Borg before is he/she/it new?

[Genx]

LOL! I think so, but yeh will do.

[Stuart Taylor]

:borg:

You will use tabs for sorting portals ... Resistance is futile

[mr_vodka]

I moved to using sorted portals on multiple tabs on some of the ones with large amounts of records and what a difference it made. On a portal displaying roughly 70K records, what used to take about 20 seconds on the initial sort with an unstored calc now can be completely done with the initial sort by 5 seconds.

[Genx]

You have portals that display 70,000 records? I was thinking more like 15 records lol

[mr_vodka]

Haha. Well the thing is that for portals that I know will have only end up having a small amount of records, I still use an unstored calc. This is because if you have many fields you want to sort by (let's say like 10) then purely from a maintenance perspective, as Ender pointed out, it gets tedious and annoying to change all 10 tabs to perfectly align up etc when making a design change.

Therefore, my personal preference has been to not change all sorted portals across the board in my solution; but rather use a hybrid system. With the portals that display a smaller amount of records, I still use an unstored calc as a sort key and use predetermined sorted portal on multiple tabs for large records amounts.

[David Jondreau]

John,

How do you account for different field types in your unstored calc? And how do you sort the text fields descending?

Thanks,

DJ

[Søren Dyhr]

I would use the tabbed even with a small number of records:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000691

--sd

[comment]

I agree, although the example you pointed to requires more than "a little futzing to position everything right". But now (v8.5) that tab panel selection can be controlled by buttons, I believe this limitation is removed.

However, to satisfy David's curiosity:

All numeric type data (number, date, time, timestamp) must be converted to text, and modified to ensure proper sorting. This is easy with dates, times and timestamps - you just pad them with zeros - but it can get very complex with numbers, because to do it right the full range of numbers, incl. very large numbers as well as very small fractions, positive as well as negative and zero, must be processed.

Regarding ascending/descending see here:

http://filemakertoday.com/com/showthread.php?p=31584#post31584

[David Jondreau]

I registered, but I still can't download that file from the Cafe. Interesting site though, lots of familiar faces. I'll have to look into why I can't grab that file.

I did find this technique with a nice custom function.

[comment]

Try it with a number field, or with non-English text, and you'll see what I meant.

[David Jondreau]

I understand the limitations, or at least, the difficulties of the technique. But for a solution I'm working on right now, the calc seems perfect. I never produce non-English text and the client doesn't need to sort by a number field. And there's never more than 300 records in the portal.

[comment]

I can't seem to explain myself properly. If you only want to sort on text fields, then you don't need a custom function, or translate to code, or reverse the text or any other fancy stuff. All you need is two global fields - gSortField and gSortOrder - and TWO calculation fields.

Both calcs get their data from the field indicated by gSortField, but one of them is always empty. Which field is empty is determined by gSortOrder. The portal is sorted by both calc fields, one in ascending order, the other in descending. That's it. And this will also work with text in any language and any alphabet.

Even so, multiple portals in a tab control object are a much better choice, IMHO. For example, you can include sub-sorts in your pre-defined sort orders (e.g. FirstName, LastName or LastName, FirstName or Company, LastName, FirstName). This would be rather complex to achieve with a calculation, esp. if the number of sub-sorts is not constant.

[Søren Dyhr]

I never produce non-English text

To me is it still wishfull thinking!

--sd