I was trying to do a work around to make a portal sortable by different fields.  I created a field in my related table that is a calculation which is Get (RecrodNumber), then a script that will go sort that table how I want it to sorted.  It is correctly sorting the related table, and it is showing the record numbers in order as it should on that page.  But when I set the portal to sort by that field, it won't do it.  I can specify any other field and it will display them in the order of that field, but not the record number field..

What's even stranger is I put that record number field on my portal, and it shows the numbers in order, even though it doesn't match what's in that field in the actual table.  In other words, on the portal, the first row has a 1, second has 2 and so on, but in the related records, it's not the same number.

Filemaker handles the related set separately from the found set in the related table. Each set has its own records and its own sort order. The Get ( RecordNumber ) function returns two separate results for the two sets*. You cannot use the result of a sort performed in the layout of the related table to sort the portal  - at least not directly (you could use it indirectly, e.g. by capturing a list of the sorted records' unique IDs, then sorting the portal by a calculation field that calculates the position of the record's ID in the list).

---
(*) In fact, if you sort a portal in a different sort order than the relationship, you will see that the Get ( RecordNumber ) function will return a separate third result for that portal.

 

Edited March 30, 20232 yr by comment

That makes sense.  So I added a second field called capture, and added a loop to my script to set that field to the sort field.  Now it works as it should.

Thank You.

3 minutes ago, Tpaairman said:

So I added a second field called capture, and added a loop to my script to set that field to the sort field. 

That's a problematic solution because (a) it necessitates modifying all records every time the script runs - thus rendering any LastModified fields useless, and (b) in a multi-user scenario the last user to perform the script imposes their sort order upon all other users.

 

I'll keep that in mind in the future.  In this case, it won't be a problem because I don't have a need for any LastModified fields, and I'm the only one who will ever use this particular database.

15 hours ago, comment said:

That's a problematic solution because (a) it necessitates modifying all records every time the script runs - thus rendering any LastModified fields useless, and (b) in a multi-user scenario the last user to perform the script imposes their sort order upon all other users.

 

I've been giving this some thought.  I agree that in those cases, this would be an issue, but I don't see it as a blanket statement problem.  If I have a case where multiple people are using a file, then one user entering data would negate the LastModfiedfield for user number two.  There are also times when a user needs to change the sort order, which would have the same effect.  I understand that we try to set things up so that one things doesn't interfere with another, but this is one of those cases where it won't hurt to go against those guidelines a bit.

2 hours ago, Tpaairman said:

this is one of those cases where it won't hurt to go against those guidelines a bit.

It's your call. I try to avoid these issues as a matter of good practice. It eliminates the need to rewrite in case the solution becomes successful...