FM - How to Sort both Ways in a Portal?

In an answer to a previous question, I was shown how to sort in a portal using global and calculation fields (see attached).  However, now my boss wants the option of sorting either ascending or descending.  Of course, the portal only allows one direction, so it seems you would have to create fields that reverse the sort order.  I think I can do that on my RecID and date fields, but not sure how I would do it on text fields.  Any ideas?  Thanks.
-Sortscreen.jpg
LVL 2
rvfowler2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Will LovingPresident, Dedication Technologies, Inc.Commented:
You make a second global to determine sort direction and a second calc field. The calc fields have exactly the same thing in them except that they have one extra condition. If the Sort global is null (empty) then the first calc field is populated and the second is empty. If the Sort Global has a value in it, then the second calc field is populated and the first is empty.

One you have the two calc fields created, you set one to sort Ascending and one to sort Descending in the Sort order. Depending on the value in the Sort global, one of them will be populated and the other will be null. You can just add a checkbox of Sort global or you can get a little fancier and create a calculated container field that references the Sort global and two additional container fields that hold up arrow and down arrow graphics. Add the calculated container field to the screen and attached a Set Field script step to it that simply switches the Sort Global from null to 1.
rvfowler2Author Commented:
I think I get it.  So you have the two calculated fields, let say ascending first and then descending second.  If either one is populated from their respective global fields, the other is null and so a null sort, even if it is first, makes them all equal in sort order so only the second calc field will have any effect.  Correct?
Will LovingPresident, Dedication Technologies, Inc.Commented:
Exactly
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

rvfowler2Author Commented:
Solution works; however, I've timed my selections and there is a full 5 second delay after choose the sort option.  The portal is choosing from a 31,000 rec table, but most of those are already filtered out due to the first join being Prop Code to Prop Code and the second join limiting it to just the past 183 days at the most.  So, the average number of recs before filtering is only 50.  Any ideas?  See attached for sort field calc as well as the Table Join.
-Sortscreenshot.jpg
rvfowler2Author Commented:
P.S. - I put a 1 instead of "", but that didn't speed it up.  Also, one improvement I made is to create a default sort.  I did this by having the second calc field in the sort (the Descending field) when it is empty to sort be my default of Rec ID.  Thus, if both fields are empty, the first will sort by 1 and then the second by Descending Rec ID.  If only the Descending is empty, it won't effect the sort to be RecID because the first ascending sort takes precedence.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Well, one thing that I know affects speed is the order and type of predicates in the relationship. Someone did some testing a while back and found that using  >  <  ≤  ≥  very significantly slows interactions with the related records, so having those first may be hurting your performance. I would suggest re-ordering your predicates so that 1) the  =  predicates come first, and 2) of those  =  predicates, put the  most restricting first, meaning the ones that narrow the scope of records being related to.
rvfowler2Author Commented:
Still testing.  Move the < and > statements lower in the TO link and still have 4-5 second delay.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Hmmm.... well this might be an instance where having two portals on an invisible tab object works better...
rvfowler2Author Commented:
Tried the two portal option, but it didn't significantly reduce the processing time of 4-5 seconds.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Perhaps I didn't mention this but the two portal solution requires that each portal have a separate TO, one set to sort one direction at the relationships level and one set to sort the other direction at the TO level.
rvfowler2Author Commented:
Does the two-portal solution also mean that you need separate global fields for each, otherwise it seems that a change in one fires of the calculation for the other.
Will LovingPresident, Dedication Technologies, Inc.Commented:
Do you mean the global that determines what field you're sorting on? I had forgotten that you were wanting to select the field that the sort is being based on and had a global and calc field for doing that. I would guess that if you are using the same global and calc field to determine the sort order for both portals then yes, both portals would re-sort if you change the sort field.

Ultimately, the way most people handle this is with multiple, fixed sort portals and an invisible tab object.   It's more work to set it up and to modify it down the line, but it eliminates the slowdown of having the portal resorted because the sort order for each portal is fixed. You might be able to do it with one TO and the Sort applied at the portal level. I haven't really tested to compare sorting at the Portal vs the TO level...

You might check out the Virtual Sort option at http://www.modularfilemaker.org/module/virtual-sort/

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rvfowler2Author Commented:
Yes, once I took the calculated sort out of the portal's sort filter, processing time went from 4 seconds to under 2.  This may be in part due to the fact that I have 10 filters already on the portal.  For sorting, I'll use the multiple, fixed presorted portals as you stated.  Thanks for hanging in there on this one.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.