fmp12 Portal Sorting with FM12

[mr_vodka]

Here is a demo of how easy portal sorting can be done in FM12. This demo uses the calculation that Tom Fitch provided a while ago with his portal sorting technique but one can use the PositionValue custom function as well.

Portal_sort_fast12.zip

[mr_vodka]

I forgot to mention. This is set up to keep the sort order as you scroll through the records. You can easily turn this off or set it to a default sort each time a record loads.

[Fitch]

Nice job, John! Perfect use of ExecuteSQL.

[Ocean West]

:yay: very nice

[David Wikström]

This is indeed a great technique. I've been using it for a while, executing SQL with a plugin in FileMaker 11.

One way to speed it up even further is if you can be sure of the length of the ID key. In my case, they were always 13-digit ISBNs, but any key can of course be tweaked to always have a known length. Get (UUID ) is the obvious way in 12.

In any case, with a known key length, you can simply use a formula like

Position ( $$ID_list ; ID ; 1 ; 1 )

.

This evaluates very quickly even with "many" records, which I'm not sure is the case with the technique described above.

I actually use the same basic technique as a convenient way of sorting a found set of records - it's a very convenient way of dynamically defining the sort order since you can't set sort order by calculation. I haven't tested extensively, but I also think it can be faster for many cases where you sort on fields from multiple table occurrences (or with multiple joins, as we're using SQL).

[Fitch]

Good point, UUIDs and fixed-length IDs can work with Position() alone. I imagine there would be a performance improvement, but I'm guessing not a huge one. Would be interesting to test.

Let me reinforce your point, though (I know you know this, David, but for others reading):

Position() alone will NOT work for standard serial numbers using this technique.

[David Wikström]

Yes, some actual testing would be a good idea to see how much of a difference Position() actually makes with different record counts. I'll see if I can't do at least a quick test for this over the next few days...

[David Wikström]

A quick test (attached) indicates that the Position method is just a little hint faster with 500 related records, and noticeably - but perhaps not dramatically - faster with 5000 related records.

The experience will vary from case to case, but I've at least convinced myself that I'll stick to the "Position" method whenever I can.

PortalSort_5000records.zip

[adatasol]

I'm confused on the ExecuteSQL statement - I think the "states::abbreviation" part at the end is critical, but I can't figure out how; when trying to move the code to my solution it's not working. My portal relationship is somewhat complex - based on two "status" values, so not a simple "state abbreviation" so I wonder if that's why it's not working.

[mr_vodka]

Multiple keys on the relationship should not affect the results. In this example, I only have only parameter passed through for the WHERE clause to have the same match criteria for the portal relationship.

You can pass multiple parameters to have further restrictions on your where clause.

For example, try this in the data viewer with the sample file.

ExecuteSQL( "SELECT ID from Contacts WHERE State = ? AND ID < 30"; ""; ""; States::abbreviation; States::ID )

So in your case, you just need to add the second criteria that matches your portal relationship.

[adatasol]

Hi John -

Thanks for responding. Your response is confusing because the new statement you suggested differs significantly from the original. Realize that we dufus' don't get SQL statements, so changing it just confused me further. Especially because you took out the $field reference. Can I suggest you consider commenting exactly what the statement is doing for us dunderheads ? Here's your original:

ExecuteSQL( "SELECT ID from ContactsByState WHERE State = ? ORDER BY " & $field & Case ( $$sort_desc; " DESC" ); ""; ""; States::abbreviation )

And here's my attempt to make it work with my solution:

ExecuteSQL( "SELECT IDLead from Leads2 WHERE LeadState = ? ORDER BY " & $field ; ""; ""; Sessions::Z_TextYesCalc;Sessions::Z_TextDeadCalc)

Where the two Sessions fields are the end are the two parent key fields.

Am I close?

Thanks.

[mr_vodka]

Sorry I was out of town for a while and just now getting to these older threads.

My previous post was just to demonstrate that you could pass in multiple criteria to further adjust your WHERE clause to match the multiple keys that you are using for your parent / child relationship that mimics your portal. ( At this point though I am not even sure that is what you have meant ).

As for the original SQL statement, the ORDER BY is to sort by a certain field. The field name is passed as a parameter when the column header button is pressed. From the script simply parses out the field name without the table occurrence name and then toggles the sort direction flag variable if it was sorted already by the same field previously.

Then as part of the ExecuteSQL calc, it chooses the IDs of the child table where it matches the filter criteria of FileMaker join and sorts it simply by the field name, last set in the variable $field and if the descending flag variable is active, then sort it descending rather than the default ORDER BY ascending sort.

Does this help clarify?

[liltbrockie]

Wow this was one of the hardest things I've ever had to try and get working in filemaker but I did it and it's great, thanks Mr vodka!

[andre.filemaker]

Hi! Thank you for the demo file. Excellent technique!

 

I was trying to make it work with a cartesian join but SQL is not my field of expertise. Can you tell me how I should adapt the SQL script step?

 

---

 

Silly me. This does the trick just fine:

 

ExecuteSQL( "SELECT ID from Contacts; ""; "" )

Edited January 31, 2013 by andre.filemaker

[Rich S]

Thanks! Great script...before I broke it. :S

 

For grins, I changed the table names to something else (and made the change of respective table names in the "setPortal_refresh [field]" script) but now it won't sort. (The ascending/descending arrows work, though.)

 

So, I'm curious: Why did changing the table names break the script...or am I missing something?

[mr_vodka]

Did you update the SQL statement? Did you double check the script parameter on the button to point to each field?

[Steverino]

Can the table name have a space?  Something isn't working with my SQL statement:

 

ExecuteSQL( "SELECT BookingID from Booking 3 WHERE ClientRID = ? ORDER BY " & $field & Case ( $$sort_desc; " DESC" ); ""; ""; Clients::ClientID )

[jbante]

To have a space in a table name in an ExecuteSQL query, the table name needs to be wrapped in quotes. This is best handled by wrapping all ExecuteSQL query references to fields and tables in custom functions that handle the quoting for you. This way, the query doesn't break if you change any table or field names, as so often happens in FileMaker.

ExecuteSQL (
	"SELECT "
	& SQLField ( Booking 3::BookingID )
	& " FROM "
	& SQLTable ( Booking 3::ClientRID )
	& " WHERE "
	& SQLField ( Booking 3::ClientRID )
	& " = ? ORDER BY "
	& $field
	& If ( $$sort_desc ; " DESC" ) ;
	"" ;
	"" ;
	Clients::ClientID
)

[mr_vodka]

Everything Jeremy said. I will add this though, with typical naming standards you wouldn't have a space in your table names or field names.

[jbante]

I just finished wrapping-up the demos for an extension to John's technique that I've been working on. I'm calling it Virtual Sort. It handles all the SQL for you — just tell it what you're sorting, and it takes care of the rest (mostly). If you aren't comfortable working with GitHub, you can download the demo file directly. I'm eager to hear all of you tell me anything wrong with it so I can fix it.

[Lee Smith]

Hi Jeremy,

 

Please post your files here, to a reply. Is this the same file that you posted Here?

[bruceR]

1. The found set sorting feature doesn't work, error 10.

2. I was able to kill the demo by renaming the NameLast field to NameLast DESC. I think it would understandable if your reply is "Well duh. Don't do that." Would there ever be a case where that kind of field name had an actual purpose?Like setting a default sort order for a field? Well; probably not.

[jbante]

Thanks for catching that, Bruce! The sort buttons were using a custom function that I removed from the file. Works for me, now (and presumably should work for everyone else, but maybe not).

 

"Well duh; don't do that," is a reasonable enough argument for code I keep to myself, I think modules for public distribution should be held to a higher standard. Appending "DESC" to a field name is something I think the module should be able to handle. It's a less trivial fix, so I'll get back to you when it's that's fixed.

 

... Fixed now!

Edited July 17, 2013 by jbante

[jbante]

VirtualSort.zip

 

Hi Jeremy,

 

Please post your files here, to a reply. Is this the same file that you posted Here?

 

To appease Lee, here's a copy of the demo file for FMForums to host for posterity's sake in the unlikely event that GitHub shuts down before the module ceases to be useful.

 

I strongly recommend accessing the version on GitHub, though. (This is the direct link to the GitHub-hosted file if you don't want to deal with the repository.) The copy on GitHub will have any updates with enhancements and bug fixes I make in the future (and the repository makes all historical versions available, too); I will not be updating the copy hosted by FMForums.

[bruceR]

I note that you can't have different sorts in different windows. Maybe there is even a use case for that?

[jbante]

I left the SortID mechanism open ended to allow developers with needs like that to devise their own numbering schemes to account for things like sorting the same TO in the same layout in different windows.

[Rich S]

*blush* A well-deserved "D'oh!"

 

Here's a silly question: Obviously, using Unsort Records isn't the answer to unsort the sorted portal records; Is there an easy Set Field script step I can use to return the sort to unsorted, or should I continue down my usual, merry path of finding the most complicated way of doing things in FMP for the simplest of commands/scripts?

 

 

 

Did you update the SQL statement? Did you double check the script parameter on the button to point to each field?

 

 

Thanks! Great script...before I broke it. :S

 

For grins, I changed the table names to something else (and made the change of respective table names in the "setPortal_refresh [field]" script) but now it won't sort. (The ascending/descending arrows work, though.)

 

So, I'm curious: Why did changing the table names break the script...or am I missing something?

[jbante]

*blush* A well-deserved "D'oh!"

 

Here's a silly question: Obviously, using Unsort Records isn't the answer to unsort the sorted portal records; Is there an easy Set Field script step I can use to return the sort to unsorted, or should I continue down my usual, merry path of finding the most complicated way of doing things in FMP for the simplest of commands/scripts?

 

Clear the global variable storing the order of the portal records' primary keys, then refresh the window.

[truelifeajf]

Awesome job Jeremy... I've implemented this in my solution.

 

However, I'm also using a dynamic portal filter setup where the match field on the parent side is a mult-line field... "0¶namezzzz"... a solution I think first introduced by NightWing (http://www.nightwing.com.au/FileMaker/demosX/demoX03.html)

 

This is resulting in the $whereClause in your script failing and producing an error.

 

I can't think of a way around this problem.

[jbante]

Multi-line keys are something I didn't think to account for when designing the Virtual Sort module. I might account for that in a future version.

 

Most folks wouldn't filter a portal that way anymore. (The demo you link to was designed for FileMaker 10, which did not have portal filters.) If the total set of valid related records that might show up in the portal is not too large (dozens is fine, perhaps not hundreds), FileMaker's own portal filters can be a good option. If you're filtering from a larger set of records, you might be better off with a pop-up picker opening a list view in a new window with something like Auto Quick Find.

[Rich S]

Thanks!
 

 

Clear the global variable storing the order of the portal records' primary keys, then refresh the window.

[truelifeajf]

Hi Jeremy,

 

I also tried it with Portal Sorting but it was too slow.

 

I also tried QuickFind.

 

Actually, your method was the LAST method I used after trying all other methods.

 

I can't use quickfind as quickfind only searches on fields on the layout. I don't display the actual fields in the portal... I display calculated (and unstored) versions of the fields in the portal (as I apply formatting and other stuff to them for display).

 

One option is to put the required fields I need for quickfind also on the layout, in the portal, but hidden behind another field I suppose) so quickfind could do it's thing on the stored fields instead of the unstored calc fields.

[jbante]

truelifeajf, if you're trying the Quick Find method I suggested, there shouldn't be a portal anymore. Your form layout would have a button that opens a new window with a list view layout; the new list view layout would mirror and replace the current portal. Also, if you want Quick Find to match on fields you don't want to display, you can put those fields outside (to the right of) the visible layout area (in FileMaker version 12). If you're displaying unstored calculations for the sake of disallowing users from entering the data fields, there are better ways to do that. You can disable entry for the base fields using options in the Inspector palette in layout mode, and you can use the file security options to disable edit privileges on those fields for appropriate records.

[no23rman]

As for the original SQL statement, the ORDER BY is to sort by a certain field. The field name is passed as a parameter when the column header button is pressed. From the script simply parses out the field name without the table occurrence name and then toggles the sort direction flag variable if it was sorted already by the same field previously.

 

That was one amazing technique. Thanks!

 

Though, I can't seem to put my finger on how you do the dynamic flag/ sort icon.

 

I found " $$sort_icon <> 1 " under the conditional formatting

and <<$so>> on the layout

 

if you don't mind sharing that with us

 

Thanks again!

[mr_vodka]

The variable is being set when the parameter is being passed, when the button is pressed.