Portal Sorting

[Anuviel]

Basically I have a portal in Order layout which displays records from Line Items. I would like to be able to sort the portal by each field that is in portal (I have field labels above the portal identifying each field) - for example:

       Item     Cost     Total

Row 1  IT001    25.00    25.00

Row 2  IT002    35.00    35.00

So when Item is clicked it would sort by item, when Total is clicked it would sort by Total.

I have searched here on forums and have found some methods on how to accomplish this. One involved creating relationships and setting the sort orders there. That would mean that I would have to create as many relationships as I have fields in a portal and then when Item is clicked I would be taken to a layout where the portal was sorted by that relationship, when Total was clicked I would be taken to a duplicate of the layout where portal was sorted by Total in the relationship. I have also found a portal sorting method by using tabs, described here:

http://www.fmforums.com/forum/showpost.php?post/224333/

I did not like this method as it results in resetting, going back to first tab, when user switches layouts and for me to make the navigation remember what tab was selected when returning to the layout is too much work (I am still a newb).

I also found some other methods which used calc field to determine sort order and so on.

Having studied all of the examples I have made the following for my portal sorting needs.

I have added a Sort_By field in Line Items table. I set the portal to sort records Ascending by Sort_By field. I have a script which loops through portal rows and sets Sorted_By field to a value by which I wish the portal to sort. It works really fast as far as I can see but then again my portals will rarely have over 30 rows in them. To sort by Descending I only need to duplicate the layout once and that is it.

Does anyone see a problem with this, such as a performance or potential issues down the road? I am still working on a file and will post example when I get it to be a little nicer.

Hope I made sense in the post, let me know if I did not.

Sorry for the length...

Thanks.,

[David Jondreau]

I think you'll find strong advocates of the tab sorting method here, but if you're set on doing it by calc (and it sounds like you've thought it through)...

What not have the Sort_by field in the Line Items table be an unstored calculation referencing a field in the Orders table. You set the Orders::Sort By via script. Click on the Item button, and the script sets the Orders::Sort By to "Item".

The Line Items::Sort By field would be a calc like:

Case(

Orders::Sort By = "Item"; Line Items::Item

Orders::Sort By = "Price"; Line Items::Price

)

[comment]

Does anyone see a problem with this

Yes. The field to sort by (which can be a calculation field, as DJ says) must have a type, most likely Text. If the values are coming from a Number or a Date field, they will not sort correctly. It's possible to make the calculation adjust the data accordingly to the type, but it is a rather complicated formula. That's why tab sorting is the winner.

[Anuviel]

Thanks, realized that as I was making an example file so I adjusted by adding sort by Number field and expanding to four layouts total. My portals will never contain date and I am looking for portal sort particular for my solution, not a universal one. I am almost done with the file but might post it later tonight ot tomorrow as I need to leave office now. I do hope it will work nicely. I really appreciate the input on the matter and once I post the file I would really appreciate comments on it, good or bad, as I want to learn.

Thank you.

[David Jondreau]

It is not much more difficult to remember which layout has been navigated away from than which tab. How do you plan on remembering which sort layout was last used?

[Anuviel]

I use script parameters and layout names for my navigation. That is how I remember what layout the user was after leaving it, so they can go back to it from any other layout.

Here is the file, I wrote some explanations in it. It turned quite complicated I should say but it works nicely so far, not the best solution but I like it.

Please take a look at the file and comment on it, good or bad, let me know so that I can get a better understanding of FM.

Thank you very much.,

Example.zip

[mr_vodka]

:badidea:

This is a very bad idea. If your solution is a multi-user system, it will lock the entire record until your loop is completed just for a simple sort.

You should go with the standard methods of using an unstored calc or tabs (which are faster).

As DJ said, your script can store the last sort field and direction so that when you go back to the layout (with a script) you should be easily able to go the the last tab. Just store it in global variables...

[comment]

it will lock the entire record

I think the worse part is that the sort order is changed for all users.

[mr_vodka]

That too. B)

[Anuviel]

Wow, did not know the above. Learning every day. I guess I will go with the tab sorting method after all. Thanks for the help and tips.

:thankyou: