Jan Rüter

Q: How can I save a named filter and sorting for later use?

I regularly do several different complex filtering and sorting on large tables. I did not find a way to save them for later one-click reuse. So I always have to do it again, which is time-consuming and error-prone. Hoped to get a solution by AppleScript, but filter and sort is not accessible by scripting.

 

Is there a way or does Apple have it on it´s agenda for future versions of Numbers? 

Mac mini (Early 2009), OS X Mavericks (10.9.4)

Posted on Jan 21, 2015 8:47 PM

Close

Q: How can I save a named filter and sorting for later use?

  • All replies
  • Helpful answers

  • by t quinn,

    t quinn t quinn Jan 21, 2015 10:37 PM in response to Jan Rüter
    Level 5 (5,041 points)
    Mac OS X
    Jan 21, 2015 10:37 PM in response to Jan Rüter

    Hi Jan,

     

    Yes I want. I think the way to do this is to go to Menu>Numbers>Provide Numbers Feedback and make a feature request.

     

    quinn

  • by Jan Rüter,

    Jan Rüter Jan Rüter Jan 22, 2015 5:01 AM in response to t quinn
    Level 1 (18 points)
    iCloud
    Jan 22, 2015 5:01 AM in response to t quinn

    Hello t quinn,

     

    OK, done. Now just wait a while.

     

    Jan

  • by SGIII,

    SGIII SGIII Jan 22, 2015 8:12 AM in response to Jan Rüter
    Level 6 (10,691 points)
    Mac OS X
    Jan 22, 2015 8:12 AM in response to Jan Rüter

    Hi Jan,

    filter and sort is not accessible by scripting.

     

     

    There is some support for sort (and less for filter) in Numbers 3.

     

    Table has a filtered property.

     

    Screen Shot 2015-01-22 at 11.07.46 AM.png

     

    And you can sort.

     

    Screen Shot 2015-01-22 at 11.06.21 AM.png

     

    A sort example can be found at https://iworkautomation.com/numbers/table-sort.html.


    Not sure if this will be sufficient to handle your needs, but just wanted to point out there is some scripting support.


    SG

  • by Jerrold Green1,

    Jerrold Green1 Jerrold Green1 Jan 22, 2015 8:22 AM in response to Jan Rüter
    Level 7 (30,001 points)
    Jan 22, 2015 8:22 AM in response to Jan Rüter

    Jan,

     

    Sorting and Filtering setups are Table Properties. They are specific to the table that was active when they were created and will follow the table if it is Copy/Pasted to another Sheet or Document.

     

    In that sense, they can be given names and can be saved. You can have a Sheet in your document in which you save copies of tables with preferred setups, or you can save a document as a Custom Template with tables set up to your sorting and filtering preferences, for recall and use later.

     

    Jerry

  • by t quinn,

    t quinn t quinn Jan 22, 2015 8:23 AM in response to SGIII
    Level 5 (5,041 points)
    Mac OS X
    Jan 22, 2015 8:23 AM in response to SGIII

    Hi SG,

     

    Before I wrote my script to refresh popups I needed to replace my standard filter with one that made the popups easy to replace. In the script I scripted around the need for a new filter.

     

    Jan,

     

    It is possible that a script might get you a result without changing out the filter. My script turns the filter off before it does its work and then restores it when it is done. It might be possible to write a script that sorts and gathers data and pastes it somewhere- that automates what you are currently doing- without a second filter.

     

    quinn

  • by Badunit,

    Badunit Badunit Jan 22, 2015 4:29 PM in response to Jan Rüter
    Level 6 (11,705 points)
    iTunes
    Jan 22, 2015 4:29 PM in response to Jan Rüter

    One way to do this is to add a new table that has your "named" filters then add one or more extra columns to your original table with some logic in them based on your desired filters. The extra column(s) will be used for a filter for showing only specific rows. Here is a really simple example with really simple logic. The "hide this column" columns are to be hidden after it is all set up. The first screenshot is with the filter off, the second is with it on.

     

    Table 1 cell C2 =IF(B2,A2,"")

    Fill down

     

    Table 2 cell C2 =MATCH(A2,Table 1::C,0)>0

    Fill down

    The result will be TRUE if the row is to be shown. Don't worry about the error triangles.

     

    Set up a filter for Table 2.

     

    Screen Shot 2015-01-22 at 7.22.48 PM.png

     

    Screen Shot 2015-01-22 at 7.23.08 PM.png

  • by Badunit,

    Badunit Badunit Jan 22, 2015 5:11 PM in response to Badunit
    Level 6 (11,705 points)
    iTunes
    Jan 22, 2015 5:11 PM in response to Badunit

    I have a document that has a table with much more sophisticated filters.  I do the logic in several columns of "Table 2". Using the same simple example from above, I might have a "named filter" in Table 1 of "Green or Red and > 3" and a column in Table 2 that says "if Table 1::B4 is true and A is Green or Red and B > 3 then TRUE". And I might have other "filters" of a similar nature. Then I use a final column that ties all those "filter" columns together with "if any of the columns are TRUE then TRUE" and I use this final column for the actual filter. In this case, Table 1 in the document does not require a column C.

  • by t quinn,

    t quinn t quinn Jan 22, 2015 7:07 PM in response to Badunit
    Level 5 (5,041 points)
    Mac OS X
    Jan 22, 2015 7:07 PM in response to Badunit

    Hi Badunit,

     

    This is truly brilliant. For me it is a solution looking for a problem, but I am looking.

     

    quinn

  • by Hiroto,

    Hiroto Hiroto Jan 22, 2015 8:56 PM in response to Jan Rüter
    Level 5 (7,306 points)
    Jan 22, 2015 8:56 PM in response to Jan Rüter

    Indeed you can perform complex sorting and filtering by script.

     

    E.g.,

     

     

    tell application "Numbers"
        tell document 1's sheet 1's table 1
            -- filter (by column 4 = TRUE)
            tell column 4's cells 2 thru -1
                set value to "=AND(B>5, C>\"A\")" -- filtering logic
            end tell
            set filtered to true
            
            -- sort (in reverse significance order of the keys)
            sort by column 2 direction descending in rows cell range
            sort by column 3 direction ascending in rows cell range
        end tell
    end tell
    

     

     

    a.png

     

     

    Tested with Numbers 2.0.5 under OS X 10.6.8. Don't know about later versions.

     

    Regards,

    H

  • by Jan Rüter,

    Jan Rüter Jan Rüter Jan 22, 2015 10:51 PM in response to Badunit
    Level 1 (18 points)
    iCloud
    Jan 22, 2015 10:51 PM in response to Badunit

    Hello BadUnit,

     

    it really looks like a solution for what i´m looking for. Not as comfortable and simple as the new filter- and sort-pane of the new Numbers, but a way to go. I hope the formula still works in the new Numbers. I´ll try it out and will tell here.

     

    Jan

  • by Jan Rüter,

    Jan Rüter Jan Rüter Jan 22, 2015 11:18 PM in response to Hiroto
    Level 1 (18 points)
    iCloud
    Jan 22, 2015 11:18 PM in response to Hiroto

    Hi Hiroto,

     

    i´ll test BadUnit´s formula for my purpose, and surely your tip for AppleSkript, too. The AppleSkript may prevent me from extra tables for views, due the overview of tables got lost a little bit in the new Numbers (supposedly for iOS reason).

     

    My idea was to access the new sort- and filter-pane of Numbers 3 ff directly, which is not possible. It is very comfortable to query your table´s data, but limited to the current and it get lost, if you need another query.

     

    My wish is (better: was) to give a name to current query of the sort- and filter-pane and recall the query by a click on the name. That simple.

     

    This would be nearly as good as a query in the database we lost with PPC and AppleWorks (fast). There is by far no substitute e.g. TapForms (user defined masks, but extreme slow, no serious calculations), Ninox (very fast, but weird iOS user interface an OS X) or much too expensive and extensive FileMaker. So we are left with Numbers 3 (a little bit slow, limited, no database-alike masks, no accurate import of CSV and TSV e.g. special characters umlaut).

     

    But we have Yosemity.

     

    Jan

  • by t quinn,

    t quinn t quinn Jan 23, 2015 11:38 PM in response to Badunit
    Level 5 (5,041 points)
    Mac OS X
    Jan 23, 2015 11:38 PM in response to Badunit

    Hi Badunit,

     

    What your method has opened up for me is a way to switch between filters while running a script. This speeds up the revised script. Thank you!

    Even if I end up with a formula like =IF(OR(AND(OR(,$C2>0,LEFT($A2,4)="week"),Summary::$G$10),AND(ISBLANK($A2),Summa ry::$G$14),AND(Summary::$G$10=FALSE,Summary::$G$14=FALSE)),TRUE(),FALSE())

    woo hoo!

     

    quinn

  • by Badunit,

    Badunit Badunit Jan 24, 2015 5:25 AM in response to t quinn
    Level 6 (11,705 points)
    iTunes
    Jan 24, 2015 5:25 AM in response to t quinn

    My document has formulas that look a little like that. In most cases I used multiple columns to break it up into smaller pieces and I gathered it together in another column, especially if I needed some of the same logic for more than one filter.

  • by t quinn,

    t quinn t quinn Jan 24, 2015 8:00 AM in response to Badunit
    Level 5 (5,041 points)
    Mac OS X
    Jan 24, 2015 8:00 AM in response to Badunit

    Hi Badunit,

     

    I will probably do that in the future. This one was a good puzzler to build.

     

    quinn