Sorting portal records dynamically

[hamelekim]

I want to be able to sort columns in a portal by clicking on the headers. I have read some of the portal search threads in here and they are no help. It's just a bunch of calculations and the actual implimentation is never discussed. How does it work? Why isn't there a sort portal records script step in Filemaker? So you can select the field to sort by in the portal?

[hamelekim]

I'm having real trouble understand this.

I read a pdf file on dynamic portal sorting but they don't explain how you relate fields.

I have a field in the parent table that contains a dropdown with a list of all the field names in the child table. then I have a calculated field in the child table that gets the field from that global field in the parent table. So what do I do to get that to sort my fields? No explination seems to cover this... Please help. I need to do this for every single field (14 of them) in the portal. The client has to be able to sort by multiple fields...

[hamelekim]

ok. This is what I followed I'm not sure what he means by the relationship between the two tables though. And when I put the dynamicsortvalue field on the layout to see what's in it, it's the first value for the table that is selected in the gsortkey field... No sorting has occured at all. What am I doing wrong?

Here's a fairly straightforward method to achieve dynamic re-sorting of related values

(ie in a portal), achievable in four easy steps:

1. Create a value list in the main file called 'Sort_Key' which lists the names of the

different fields in the related file that you wish to be able to sort by.

2. Create a global text field in your related file called 'gSortKey', place it on the

layout in your main file above your portal, with a label that reads 'Sort related

records by:', then go into 'Field Format' and define gSortKey field as a pop-up

menu based on the Sort_Key value list.

3. Create a calculation field in your related file called DynamicSortValue, with the

formula: GetField(gSortKey).

4. Define the relationship in the main file to sort on the 'DynamicSortValue' field.

That's it - you're in business. The gSortKey in your main file layout will operate as a

menu of sort options and the portal will re-sort dynamically when you select a new

option.

Edited March 9, 2006 by Guest

[hamelekim]

Could someone at least explain how changing the value in a field sorts a portal? I don't see the link. I have no clue how this is done...

[aldipalo]

Try this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=558&mode=thread&order=0&thold=0

AND:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=537&mode=thread&order=0&thold=0

[hamelekim]

That helps, sort of. I am still missing something though. I have the three required fields and all the proper functions, the SortValue field actually changes numbers to the matching numbers depending on the column header and the values " 1234etc..." I click on as in that example, but nothing happens on the screen in terms of the values changing order on the screen. My SortControl field in my main table changes, as does the SortDirection. So all three of the important fields do change values based on clicking on the buttons I have. I have a relationship between the two tables set to sort on the SortValue field. But like I said nothing is actually happening in the portal... what could I be missing? I copied all the relevant custom functions and scripts over and changed the fields to the matching ones in my database....

[hamelekim]

Actually I figured it out. I had to go into the portal and set it to sort by the SortValue field besides doing the same in the relationship, seems to work now. thanks for the help. Now I need to figure out how to sort on multiple fields...

[aldipalo]

Let's say you want to sort by LastName and Date in ascending order. In the sort panel you set up your predefined sort as

LastName > Ascending

Date > Ascending

Your portal will show all records in the found set by

LastName a to z

and the date

01/01/YYYY - CurrentDate.

If you play around with your criteria you'll get the hang of it. Of course you could also sort by:

Company > Ascending

LastName > Ascending

DateContacted > Descending

So you get Company and contact a to z and last date contacted.

Good luck.

Al

[hamelekim]

Well, I want to be able to sort by two more more fields. The way I did it there is some number play involved you have

" 0123456789abcdefghijklmnopqrstuvwxyz" each represented by a numbers up to 37 for z. Then you have the one field that has a number for each letter or character in a field. so you could have aba so you would have 11 for a 12 for b 11 for a so 111211, then you sort by that field. So to switch it around you subtract by 37 and it switches the sort order. I am thinking that I might have to do this for multiple fields? So if I want to be able to sort by 3 or more fields I have to create a sort field in the database for two more, and then set those as sort fields as well? I'm not sure how that would even work considering you would have to track which fields are currently being sorted on. It's kind of a key feature for this project to be able to sort by at least two fields, if not three. It seems like an almost impossible task. Are there anymore videos or examples out there for filemaker that explain how to do multiple field dynamic sorting?

[aldipalo]

I just saw something related to this on ISO magazines site. Go here and search "Portal Sorting." You should be able to find it.

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=index&catid=1&topic=

**************************

Ooops, I already referred you to ISO magazine, but, if you didn't sign up for the 3 month subscription you will not get access to all of the tutorials. I know Matt was talking about something very close to what you are talking about.

I wasn't that interested because I don't currently have those challenges and this old brain can just process so much at any one time. :)

Edited March 16, 2006 by Guest

[hamelekim]

Someone should suggest to the makers of filemaker that they should incorporate a way to dynamically sort multiple fields natively in Filemaker, without all the work arounds...

[Lee Smith]

check out the Table View

[LaRetta]

Table view won't work with portals - it will only display and sort ONE related record for each main record.

IMO, table view is worthless for Users. I would rather script the requirements in List View because only List View allows button activiation within rows, portal display, etc.

[Lee Smith]

I did NOT say USE a PORTAL in the TABLE VIEW. The poster said, and I quote "Someone should suggest to the makers of filemaker that they should incorporate a way to dynamically sort multiple fields natively in Filemaker, without all the work arounds..."

my response was Check out the Table View.

Frankly, I don't care what you prefer, but I do wish you would Read what I posted, or put me in your Ignore list.

Lee

[LaRetta]

Table view doesn't allow multiple column sorting either and this post was about sorting portals but Lee ...

Jumping on me was totally uncalled for ...

If you wish to be on my ignore list, I will do so. Then you won't have a reason to jump on me because if I don't APPEAR to pay attention to your responses, you'll know it's because I can't see you any more.

[hamelekim]

I still haven't found any useful information on sorting multiple portal rows. Are there any good tutorials or articles on this subject out there?

[Ender]

Check this Dynamic Report demo, which includes a dynamic portal sort choice. It only sorts ascending, but adding the descending condition isn't too hard.

DynamicReport.fp7.zip

[BFrost]

When in doubt, Cheat!

I liked hamelekim's global field method, pretty slick.

Here's one that works reasonably well in lesser versions of FileMaker and in FMP8, too, if there aren't too many portal records to deal with.

In the table/file with the portal records create a number field (indexible is OK). Create a bunch of sort scripts to satisfy your sorting needs in the same table/file.

In the portal to that table/file, specify a sort order where the first priority is the number field and the additional sort priorities should be limited but the second (and additional sort priorities) could be something useful with regard to the data. The number field will do the work, as you probably see already.

Create buttons at the portal end which goes to the related portal records, activates one of the predefined sorts in the portal record's table/file, and, as a last script step in the related records table/file, replace the contents of the number field with serial numbers. The 'Freeze Window' step is good way to start.

Another method (in FMP 8) is to set up a portal so it's just the way you want it for one of the sorts. Each field in the portal placed to correspond to the sort order for that portal.

When your happy, duplicate the portal layout as many times as you require and hide the clones from the layout menu. Change the sort priorities for each clone and reorder the fields in the portals to appropriately reflect the sort priority for each layout clone. This is easy and yields quick results without workarounds.

If you've done it right, every element except the reordered fields will be in the same position among the clones. Click a column header label to change clones. A 'Freeze Window, go to layout by number, get(script parameter)' script makes the change look magical and you're only using one relationship to make it work.

I recommend reordering fields because we westerners comprehend from left to right, top to bottom. Different sorts facilitate comprehension better when the data sequence is viewed in its intended priority.

Caution, the magic part can be distracting. You'll find yourself leaving the mouse in one place in the header row and clicking your little heart out just to watch the data columns appear to dance before your very eyes. Don't say I didn't warn ya.

[Ender]

Create buttons at the portal end which goes to the related portal records, activates one of the predefined sorts in the portal record's table/file, and, as a last script step in the related records table/file, replace the contents of the number field with serial numbers. The 'Freeze Window' step is good way to start.

This method is not very efficient, and is not safe in multi-user solutions.

[BFrost]

This method is not very efficient, and is not safe in multi-user solutions.

I believe you are mistake in two ways.

1. Switching layouts is a local function.

2. Multi-user environments has been mentioned only by you but I can tell you that even without using server, switching portals in an LAN environment is not a problem. In fact it is probably the most efficient solution because no records are being edited just to be viewed in a different sequence.

Any solution that involves changing any value in any record is an opportunity to have an editing conflict which Filemaker will not allow. When a proposed solution presents a requirement that all records (in the process) reevaluate themselves, the possibilty of an editing conflict approaches certainty.

On the other hand, if you can demonstrate the truth of what you claimed, I'm ready to learn.

[comment]

no records are being edited

I believe replacing the contents of a number field with serial numbers qualifies as editing. Let's say User A sorts the records by criteria A. Next, User B sorts the records by criteria B, replacing the values in the number field. When User A's screen refreshes, the portal is going to be sorted by criteria B.

Any solution that involves changing any value in any record is an opportunity to have an editing conflict which Filemaker will not allow.

Exactly. Let's say User A wants to sorts the portal records. At the same time, User B is editing a record in the related table. So this record's number is not going to get replaced, and the record will appear in User A's portal out of order.

Edited April 3, 2006 by Guest

[BFrost]

Comment, you're right about that. I wasn't clear but I was referring to switching portals in my hurried reply, not the numerical replacement.

Switching poertals is quick, easy to set up, and involves no editing at all.

My bad on the confusion. I shoulda, woulda, coulda, etc. been more attentive.

Cood catch.

Edited April 4, 2006 by Guest

[FileMaker Magazine]

If anyone is looking for one of the best methods for sorting portals then you'll find it here.

http://www.filemakermagazine.com/Article618.html

This is an updated version of what is provided for free at this link.

http://www.filemakermagazine.com/Article537.html

While the article is part of a paid subscription to the online magazine, I can guarantee the small amount for a subscription will pay off - unless you already know EVERYTHING about FileMaker. :wink2: