Virtual Sort

by Jeremy Bante

Virtual Sort Screen Shot

Overview

Virtual Sort lets users sort records in FileMaker portals and found sets by any combination of fields they choose.

This module includes custom functions that require a copy of FileMaker Pro Advanced to install in another FileMaker file. The functions are not absolutely necessary, but I do strongly recommend them.

Credits

This module is based on a demo by John Ahn.

License

Anyone may do anything with this software for any purpose. There is no warranty.

Download

Download from GitHub. If you are not comfortable working with GitHub, you can download the repository directly.

17 responses to “Virtual Sort”

  1. Greg says:

    I have duplicated the 500 records in your demo for a total of 1000.
    Sorting more than 997 records returns:
    “there was an error sorting records.
    Error: 1202”

    I am trying to incorporate your module into other files and have found the same error.

    Any suggestions?

    Greg

  2. Jeremy Bante says:

    Greg, when sorting a found set of records (rather than records in a portal), Virtual Sort is limited by the number of parameters the ExecuteSQL function can handle, which happens to be 1000 (1 query + 1 column delimiter + 1 row delimiter + 997 arguments). There’s not much we can do about that beyond sorting smaller found sets.

  3. Matt says:

    It is strange I think if a UI portal is showing more than 1000 records…….

  4. Jeremy Bante says:

    Matt, the 997 record limitation does not apply when sorting a portal, only when sorting a found set. However, I do agree that if a portal has 1000 records in it, it’s worth reconsidering the interface design first.

  5. Joseph San Laureano says:

    Hi
    I am trying to implement this portal sorting technique on a portal which has more than 1000 records and I am getting an error 1202.
    Any ideas please

    Joseph

  6. Joseph San Laureano says:

    I just tried it on a portal with 5 records visible and I still get the same error message

  7. Jeremy Bante says:

    Joseph, I haven’t seen that one before. Do you mind if I email you about it?

  8. Joseph San Laureano says:

    Yes please.
    Keen to work this out
    Joseph

  9. Julio Toledo says:

    My first try at implementing this is to sort a portal field from a table occurrence three (3) hops away from the portal (4 hops if you count the starting layout). I keep getting error 8310. Can’t figure out what I’m doing wrong.

  10. Jeremy Bante says:

    Julio, that’s a new one to me. FileMaker doesn’t have a documented 8310 error code. Do you mind if I email you about this for more details and possibly a sample of your file demonstrating the issue?

  11. Julio Toledo says:

    Not at all. Please do email me, my guess is there may be some peculiarities about my solution that could be tripping up the SQL piece of this.

  12. Julio Toledo says:

    Jeremy, thanks for developing such an elegant technique that fills a real gap in FileMaker’s core functionality – the ability for users to interactively sort portals. It’s simple to implement, takes advantage of “ExecuteSQL” to handle complex sorts, and… it’s modular! What more can you ask for?

    My only request would be to implement additional error trapping to avoid presenting the user with unfamiliar SQL error messages.

    For instance, shift-clicking on the first sort field (which could happen by mistake) will generate an “8309” SQL error. Another example, when browsing through master records you arrive at a record with an empty portal (no related records to sort) the On Record Load trigger will also generate an SQL sort error.

  13. Ruud Natenstedt says:

    Has anyone made this sorting function for an multifile solution, because the $$iVars are not global to ALL files,
    Or has some one some suggestions to deal with in a separation model.

    Thanks

  14. Pedro says:

    Is there a way to pass two $sortFieldName programmatically? Meaning, can the Shift functionality be reproduced without user interaction?

  15. john renfrew says:

    Jeremy
    Is this going to work if I use Hyperlist 2 with it?? or is there some tweaking required?

  16. Jeremy Bante says:

    John, HyperList 2 should work fine.

  17. Dale Long says:

    This is a brilliant module, far beyond anything I would be able to come up with on my own, so thank you for sharing!

    I think I am finding a potential scenario that produces an ‘8309’ error when trying to sort on multiple columns. I’ve implemented this technique on a particular portal in our solution that has 8 fields. 2 of these fields are 2 TOs away from the portal’s base table, and 2 of them are 2 TOs away from the base table on a separate chain. So something like this

    Portal references Table A
    Table A is related to Table B, which is related to Table C
    Table A is also related to Table D, which is related to Table E

    Most multi-column sorts appear to work. However, if I try to do a shift-click multi-column sort where one of the columns references Table C, and another one of the columns references Table E, an error (8309) is returned.

    Not sure if it matters or is part of the problem, but just in case the directions of the relationships make some sort of difference here, both table B and table D are join tables in this example. I’m not completely sure if I’m doing something wrong, or if the module is legitimately bugged in this fringe scenario.

    Just thought I’d share this bit of a problem I came across. It’d be nice if there were a simple fix for this but otherwise this module will still be tremendously useful for my development anyway. Thanks again!

Leave a Reply

Your email address will not be published. Required fields are marked *