Jump to content

This topic is 1637 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

  • Like 4
  • Downvote 1
Link to post
Share on other sites

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.

Link to post
Share on other sites

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).

Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites

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...

Link to post
Share on other sites

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

  • Like 2
Link to post
Share on other sites
  • 1 month later...

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.

Link to post
Share on other sites

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.

  • Like 2
Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 2 weeks later...

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?

Link to post
Share on other sites
  • 4 months later...
  • 1 month later...
  • Newbies

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 by andre.filemaker
Link to post
Share on other sites
  • 2 months later...

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?

Link to post
Share on other sites

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

Link to post
Share on other sites
  • 2 months later...

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 )

Link to post
Share on other sites

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
)
  • Like 1
Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 3 weeks later...

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.

  • Like 3
Link to post
Share on other sites

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.

Link to post
Share on other sites

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 by jbante
Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites

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

Link to post
Share on other sites

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.

Link to post
Share on other sites
  • 3 weeks later...

*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?

Link to post
Share on other sites

*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.

  • Like 1
Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

  • Like 2
Link to post
Share on other sites

Thanks!
 

 

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

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

  • Like 1
Link to post
Share on other sites
  • 2 months later...

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!

Link to post
Share on other sites
  • Similar Content

    • By Richard Carlton
      FileMaker Coaches' Corner-Tip 8-Converting from fp7 to fmp12-FileMaker-FileMaker Experts
      Download the free PDFs to see FileMaker’s documentation: http://bit.ly/2nSg7BM
      Get up to speed with the FileMaker Pro 16 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton.  
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 27 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference ,Richard will teach you all the ins and outs of building FileMaker Solutions.  The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker 
      developers and supporting web designers. He has offices in California, Nevada, and Texas.

      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving 
      FileMaker for Startups and Entrepreneurs, and client server integration.

      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.

      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.

      RCC and LearningFileMaker.com are headquartered in Santa Clara, CA.

      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      If you want to explore building I.O.S apps for I Phone or I Pad and deploying those out to the Apple App Store.
       
      Here is a video introduction to our iOS App Training https://www.youtube.com/watch?v=cVxQe_yAshw
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      For More Free FileMaker Videos Check out Http://www.filemakerfree.com
      Visit http://www.learningfilemaker.com for all facets of FileMaker Award Winning Video Training.
      Please Visit Our Channel: https://www.youtube.com/user/FileMakerVideos Please Subscribe While There.

      Please Comment, Like & Share All of Our Videos.

      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      https://www.facebook.com/FileMakerVideos
      https://twitter.com/filemakervideos
      https://plus.google.com/+FileMakerVideos/videos

      Filemaker Pro 16 Training Videos
      FileMaker 16 Videos
      Filemaker Pro 16 Video Course
      #FileMakerVideos
      #FileMakerTrainingVideos
      #WhatisFilemaker16
      #FilemakerPro16Training
      #Filemaker16VideoTutorial
      #FilemakerPro16Videos
    • By Richard Carlton
      For this Coaches’ Corner tip, Jonathan Lai demos a simple technique for creating a hierarchical portal i.e. a portal with dynamic sub-summaries. You can actually display or hide sub-summary groups! Very cool technique.
      Link to the demo file used in the video: http://bit.ly/2zaI9zo
      FileMaker Coaches' Corner - Tip 2 - Portal Filtering - Portal Sorts
      https://www.youtube.com/watch?v=JJiQ-50Hps8
      www.fmcoachescorner.com
      Most Recent Upload https://goo.gl/Dbn9fm
      Get up to speed with the FileMaker Pro 16 Video Training Course! 
      Top Rated Course by FileMaker Expert, Richard Carlton. 
      http://learningfilemaker.com/fmpro16.php
      Experience Richard's dynamic and exciting teaching format, while learning both basic, intermediate, and advanced FileMaker development skills. With 26 years of FileMaker experience and a long time speaker at FileMaker's Developer Conference, Richard will teach you all the ins and outs of building FileMaker Solutions. 
      The course is 50 hours of video content!
      Richard has been involved with the FileMaker platform since 1990 and has grown RCC into one of the largest top tier FileMaker consultancies worldwide. 
      Richard works closely with RCC's staff: a team of 28 FileMaker developers and supporting web designers. 
      He has offices in California, Nevada, and Texas.
      Richard has been a frequent speaker at the FileMaker Developers Conference on a variety of topics involving FileMaker for Startups and Entrepreneurs, and client-server integration.
      Richard is the Product Manager for FM Starting Point, the popular and most downloaded free FileMaker CRM Starter Solution.
      Looking for FM Starting Point free software download: http://www.fmstartingpoint.com
      Richard won 2015 Excellence Award from FileMaker Inc (Apple Inc) for outstanding video and product creation, leading to business development.
      RCC, Filemaker Videos, and LearningFileMaker.com are headquartered in Santa Clara, CA.
      http://www.rcconsulting.com/
      Please feel free to contact us at support@rcconsulting.com
      FileMaker Pro is simply a powerful software used to create custom apps that work seamlessly across iPad, iPhone, Windows, Mac, and the web
      Transform your business with the FileMaker Platform
      Free FileMaker Training Videos Channel https://www.youtube.com/user/FileMakerVideos
      50 Hour FileMaker Pro 16 Video Training Course-FileMaker 16 News-Online FileMaker 16 Training Videos
      https://www.youtube.com/watch?v=KpQqLLDcZ8I
      Playlist https://www.youtube.com/watch?v=KpQqLLDcZ8I&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_&index=13
      Top 10 New Features in FileMaker 16-FileMaker 16 News-FileMaker 16 Instructional Videos-FileMaker 16
      https://www.youtube.com/watch?v=urh8iHOCxkg
      Playlist https://www.youtube.com/watch?v=urh8iHOCxkg&t=130s&index=1&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Sharing your Database with Other Devices and Users-FileMaker 16 News-FileMaker 16 Database Sharing
      https://www.youtube.com/watch?v=GF82vkYtCtA
      Playlist https://www.youtube.com/watch?v=GF82vkYtCtA&index=8&list=PLjTvUZtwtgBTMCfjM6LLwBAwGf_yXfvd_
      Introduction to FileMaker WebDirect 16-FileMaker 16 News-Online FileMaker 16 Training Videos
      https://www.youtube.com/watch?v=uaZKIpBjMAM
      Playlist https://www.youtube.com/watch?v=uaZKIpBjMAM&list=PLjTvUZtwtgBSVV1-4pFG4SHAhCIP3Yy-I&index=17&t=10s
      A database management system (DBMS) is a computer software application that interacts with the user, other applications, and the database itself to capture and analyze data
       
      Official site provides the SDK, Developer's Guide, Reference, and Android Market for the open source project
      http://learningfilemaker.com/FIAS.html
      https://sites.google.com/site/filemakerprotrainingd2n/home/filemaker-training
      https://drive.google.com/file/d/0ByHcthu5EL9NSmowQm9OUDdZZ1k/view?usp=sharing
      Free FileMaker videos check out ...http://www.filemakervideos.com
      Download the FileMaker Pro 16 & FileMaker GO 16 for mobile devices training videos at http://www.learningfilemaker.com
      Download FileMaker Go 16 video training at http://learningfilemaker.com/FMGO-16/fmgo16.php
      Download FileMaker 16 Full Video Training Bundle at http://learningfilemaker.com/subscription.php
      FileMaker Video Training Review-FileMaker 16 Video Course Review-FileMaker Pro 16 101 Course Review
      https://www.youtube.com/watch?v=mF6Uor0KmKo
      Playlist https://www.youtube.com/watch?v=mF6Uor0KmKo&list=PLjTvUZtwtgBT8tNHuzF6cOKC_37zCTQl6&index=18
      Use FileMaker to create an app with the FileMaker Training Series
      FileMaker Pro is a cross-platform relational database application from FileMaker Inc.
      Comment, Like & Share Our Videos.
      Feel Free to Embed any of Our Videos on Your Blog or Website.
      Follow Us on Your Favorite Social Media
      https://www.facebook.com/FileMakerVideos
      https://twitter.com/filemakervideos
      https://plus.google.com/+FileMakerVideos/videos
      #FileMakerCoachesCorner
      #FileMakerPro16Training
      #FileMakerPro16Videos
      https://www.youtube.com/watch?v=4ozLZaRUgZ0&feature=youtu.be
    • By Kishan_Canoo
      I have a car rental solution and for rental of short term one to two weeks i just need to create a new record one by one which will appeared in my calender View layout via Portal.
      In case, the customer decided to take the car for one year, i can't create 365 new records, It their any possibility to create 365 records automatically by selecting Start Date to End Date?
      Thank you.
    • By Kishan_Canoo
      I have 3 fields and the first field is Amount the second field is Deposit and the third field is Balance.
      Generally my calculation should be Balance = Amount - Deposit
      If i insert the Amount, example $100 and the Balance automatically changed to $100.
      I need the Balance remain empty unless i insert a number in the Deposit Field.
      In my outstanding due report, if their is no Deposit, the Balance field should remain empty.
      Any help.
    • By jduncan71
      Hello All,

      Recently, my company installed FM server 15 after much begging. The database that my group uses (built in FM12)is hosted on a laptop (running FM12) and I'd like to upload it to the server using FM . I cannot figure out how do do it; there is no "Upload to Server" option, and I'd rather not do it manually. If anyone has some guidance, I'd really appreciate it...
  • Who Viewed the Topic

    8 members have viewed this topic:
    mr_vodka  bcooney  Ocean West  Alamgir Hossain Alamgir  comment  jlefevre  Josh Ormond  Steve Martino 

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.