Script: Filtering and searching by column values, notes and states

A first draft of an experimental filtering script. Tested on El Capitan, but may still have rough edges.

(I understand that built-in filtering is planned, at some point)

-- Ver 0.3
-- Extends date format to some more informal English dates using NSDate.dateWithNaturalLanguageString
-- Ver 0.2
-- Accepts full or partial date times in the format yyyy-mm-dd [HH:MM]
-- (Simplified ISO 8601 https://xkcd.com/1179/)
-- The datetime must start with a year, but can stop at month day hour or minutes
-- Topic searches can start with an operator like ! or < 

-- Should also be slightly faster

-- Defaults to topic search
-- Named columns notes or checkbox states can be searched instead using 
-- case-insensitive queries of the form  
-- 
--  FILTER SYNTAX:  FIELD NAME + OPERATOR + VALUE
-- 
-- FIELD NAMES
-- 
-- Field names are case-insensitive, they can be 'note', 'state' or a column name
-- if the field name is omitted, a topic field search is assumed
-- 
-- 
-- OPERATORS
-- 
-- For textual fields:
-- 
--      =       contains
--      !=      does not contain
--      ==      exactly matches the whole field
-- 
-- For numeric fields:
-- 
--      =       equals
--      !=      does not equal
--      <       less than
--      <=      less than or equal to
--      >       more than
--      >=      more than or equal to
-- 
-- For date fields:
--
--      <       before
--      >       after
--      =       (limited value as the time and date must match to the second)
-- 
-- VALUES
-- 
-- Missing values:
-- 
--      (leave empty)
--      e.g.    started=   (rows in which the Started column is empty or indeterminate)
-- 
-- Any value:
-- 
--      *
--      e.g.    my rating=*     (My Rating rows in which the pop-up value is not empty)
-- 
-- 
-- Strings:
--      with (=)    any substring that you want to search for
--      with (==)   the full and exact contents of the text cells to be matched
-- 
-- Dates: 
--      yyyy-mm-dd [HH:MM]  (Simplified ISO 8601 https://xkcd.com/1179/)
--      Full or partial date times in the format yyyy-mm-dd [HH:MM]
--      The datetime must start with a year, but can stop at month day hour or minutes

-- Checkboxes:
--      (use only = or !=)
--              0 or false (for unchecked)
--              1 or true (for checked)
--      e.g.    state=1
-- 
-- EXAMPLES (tested with the OO4 Resource Browser Book List doc)
--  Quixote
--  author=twain
--  year < 1900
--   started < 2013-08
-- 
--   ! quixote (does *not* include 'Quixote')
--  < middleMarch (before 'MiddleMarch' alphabetically - case insensitive)
--  origin=france
--  owned=0
--  read=1
--  started=*
--  started >= 2012-01-01     (use default local date string format)
--  finished < 2015-12-31 17:00
--  my rating=
--  my rating = *

property pTitle : "Search OmniOutliner front document"
property pstrQuery : "search term"

property pUnixEpoch : missing value

property pstrPrompt : "Column searches:" & linefeed & linefeed & tab & "author=flaubert" & ÂŹ
    linefeed & tab & "finished < 2015-12-31 17:00" & linefeed & linefeed & "topic searches:" & ÂŹ
    linefeed & linefeed & tab & "quixote" & ÂŹ
    linefeed & tab & "! quixote (does not include quixote)" & ÂŹ
    linefeed & tab & "< middlemarch (alphabetically lower)"

on run
    tell application "OmniOutliner"
        activate
        set docs to (documents as list)
        if (length of docs) > 0 then
            
            activate
            set oo to it
            set strQuery to text returned of ((display dialog pstrPrompt default answer pstrQuery with title pTitle with icon path to resource "OmniOutliner.icns" in bundle (path to oo)))
            
            set blnFound to false
            if strQuery is not "" then
                set pstrQuery to strQuery
                set doc to item 1 of docs
                
                set refRows to my matchingRows(doc, my queryParse(doc, strQuery))
                set blnFieldUnKnown to (refRows is missing value)
                
                if ((not blnFieldUnKnown) and ((count of refRows) > 0)) then
                    tell refRows
                        collapseAll rows of doc
                        
                        set expanded of ancestors to true
                        set expanded to true
                        select
                    end tell
                else
                    tell doc to select {}
                    if blnFieldUnKnown then
                        set strMsg to "Column not known in "
                    else
                        set strMsg to "No matches found for: "
                    end if
                    display dialog strMsg & strQuery with title pTitle with icon path to resource "OmniOutliner.icns" in bundle (path to oo)
                end if
            end if
        end if
    end tell
end run

on matchingRows(doc, recQuery)
    set lngIndex to colIndex of recQuery
    if lngIndex = 0 then return missing value
    
    set strValue to trim(colValue of recQuery)
    set strOp to trim(op of recQuery)
    
    tell application "OmniOutliner"
        tell doc
            set op to "=" -- DEFAULT
            set val to ""
            
            if lngIndex = -1 then
                if strOp contains "!" then set op to "is not"
                set strScript to my queryScript("state " & op & " " & my checkStateString(strValue, false))
            else
                set cType to column type of column lngIndex
                if strValue is not "" then
                    if strValue is not "*" then
                        if {styled text, popup} contains cType then
                            if strOp contains "!" then
                                set op to "does not contain"
                            else if strOp = "=" then
                                set op to "contains"
                            else if strOp = "==" then
                                set op to "="
                            else
                                set op to strOp
                            end if
                            set val to "\"" & strValue & "\""
                            
                        else if {numeric, duration} contains cType then
                            if strOp contains "!" then
                                set op to "is not equal to"
                            else
                                if strOp contains "==" then
                                    set op to "="
                                else
                                    set op to strOp
                                end if
                            end if
                            set val to (strValue as number) as string
                            
                        else if cType = checkbox then
                            if strOp contains "!" then set op to "is not"
                            set val to my checkStateString(strValue, true)
                            
                        else if cType = datetime then
                            
                            set dte to my parseDateTime(strValue)
                            if dte is not null then
                                set op to strOp
                                set val to ("date \"" & (dte as string)) & "\""
                            end if
                        end if
                    else
                        set op to "is not"
                        if cType = styled text then
                            set val to "\"\""
                        else
                            set val to missing value
                        end if
                    end if
                else
                    if cType = styled text then
                        set op to "="
                        set val to "\"\""
                    else
                        set op to "is"
                        set val to "missing value"
                    end if
                end if
                
                set strScript to my queryScript("(value of cell " & lngIndex & ") " & op & " " & val)
            end if
            log strScript
            return rowSet(it) of (run script strScript)
        end tell
        
    end tell
end matchingRows

on parseDateTime(strDT)
    if pUnixEpoch is missing value then set pUnixEpoch to UnixEpoch()
    return pUnixEpoch + (((do shell script ÂŹ
        "osascript -l JavaScript -e 'Number(ObjC.unwrap($.NSDate.dateWithNaturalLanguageString(\"" & strDT & "\")));'") ÂŹ
        as number) div 1000)
end parseDateTime

on UnixEpoch()
    tell (current date)
        set {its year, its day, its time} to {1970, 1, 0}
        set its month to 1 -- set after day for fear of Feb :-)
        return (it + (my (time to GMT)))
    end tell
end UnixEpoch


-- String -> String
on checkStateString(strValue, blnCol)
    if strValue is not "" then
        set chk to missing value
        try
            set n to (strValue as number)
            set chk to (n > 0)
        end try
        if chk is missing value then
            try
                set chk to (strValue as boolean)
            end try
        end if
        if chk is not missing value then
            if chk then
                set val to "checked"
            else
                set val to "unchecked"
            end if
        else
            set val to "indeterminate"
        end if
    else
        set val to "indeterminate"
    end if
    
    if blnCol then
        return "\"" & val & "\""
    else
        return val
    end if
end checkStateString

-- ooDoc --> Text --> (strColID, strOp, strColValue)
on queryParse(ooDoc, strQuery)
    using terms from application "OmniOutliner"
        tell ooDoc
            set lngIndex to index of topic column
            set strValue to strQuery
            set lstParts to my reduce(characters of strQuery, my parseChar, {})
            set lngParts to length of lstParts
            
            if lngParts > 1 then
                set strKey to my trim((item 1 of lstParts))
                if strKey = "note" then
                    set lngIndex to index of note column
                else if strKey = "state" then
                    set lngIndex to -1
                else if strKey is not "topic" then
                    set lngIndex to my indexOf(strKey, (name of columns))
                end if
                
                if lngParts > 2 then
                    set strValue to item 3 of lstParts
                else
                    set strValue to ""
                end if
                
                return {colIndex:lngIndex, op:item 2 of lstParts, colValue:strValue}
            else
                -- Default: substring search in topic
                return {colIndex:lngIndex, op:"=", colValue:strValue}
            end if
        end tell
    end using terms from
end queryParse


-- Accumulator -> Current character -> Current index -> Whole character sequence -> Tokens
-- [Text] -> Text -> Int -> [Character] -> [Text]
on parseChar(lstAcc, c, i, lstChars)
    set strOps to "=!<>"
    
    if i > 1 then
        -- Is this char in same set (syntactic vs data) as the previous char ?
        -- (in JS we would just be using a regex)
        if ((offset of (item (i - 1) of lstChars) in strOps) > 0) = ((offset of c in strOps) > 0) then
            set item -1 of lstAcc to (item -1 of lstAcc) & c -- no change: append
        else
            set end of lstAcc to c -- change : start new token
        end if
        return lstAcc
    else -- if the first token is an op, the elided key is intepreted as 'topic'
        if (offset of c in strOps) > 0 then
            return {"topic", c}
        else
            return {c}
        end if
    end if
end parseChar

on queryScript(strQuery)
    "script
    on rowSet(doc)
        tell application \"OmniOutliner\"
            tell doc to return a reference to (rows where " & strQuery & ")
        end tell
    end objectSet
end script"
end queryScript

-- foldLeft / reduce
-- [a] -> (a -> b) -> c -> b
on reduce(xs, f, a)
    set mf to mReturn(f)
    
    set v to a
    set lng to length of xs
    repeat with i from 1 to lng
        set v to mf's call(v, item i of xs, i, xs)
    end repeat
    return v
end reduce

-- An ordinary AppleScript handler function
-- lifted into a script which is a first-class object
on mReturn(f)
    script
        property call : f
    end script
end mReturn

-- a -> [a] -> Int
on indexOf(x, lst)
    set {dlm, my text item delimiters} to {my text item delimiters, return}
    set strParas to return & lst & return
    set my text item delimiters to dlm
    try
        (count (paragraphs of (text 1 thru (offset of (return & x & return) in strParas) of strParas))) - 1
    on error
        0
    end try
end indexOf

-- Text -> Text
on trim(str)
    set strBlank to " " & tab
    set lngChars to length of str
    set iFrom to 0
    set iTo to lngChars
    
    repeat with iFrom from 1 to lngChars
        if (offset of (text iFrom of str) in strBlank) = 0 then exit repeat
    end repeat
    
    repeat with iTo from lngChars to 1 by -1
        if (offset of (text iTo of str) in strBlank) = 0 then exit repeat
    end repeat
    
    if iFrom > 0 and iTo ≄ iFrom then
        return text iTo thru iFrom of str
    else
        return ""
    end if
end trim

2 Likes

Updated above to Ver 0.2

  • date queries can now include full or partial dates in the yyyy-mm-dd (HH:MM) format https://xkcd.com/1179/
  • plain topic queries can now start with operators like ! (read as “does not include”) or (for alphabetic order) <, or >
  • search is slightly faster

#####EXAMPLES (tested with the OO4 Resource Browser Book List doc)

  • Quixote

  • author=twain

  • year < 1900

  • started < 2013-08

  • ! quixote (does not include ‘Quixote’)

  • < middleMarch (before ‘MiddleMarch’ alphabetically - case insensitive)

  • origin=france

  • owned=0

  • read=1

  • started=*

  • started >= 2012-01-01 (use default local date string format)

  • finished < 2015-12-31 17:00

  • my rating= (unrated)

  • my rating = * (rows which do have some rating entry)

  • >= M (M and upward alphabetically)

  • note=* (rows which do have notes)

  • note= (rows which do not have notes)

The script looks neat, but if fails on my machine (OSX 10.11.6) (OmniOutliner Pro 4.6 v177.7.11 r268172). After entering any search term, I’m getting a dialog with error:

The operation couldn’t be completed. /Users/user/Library/Application Scripts/com.omnigroup.OmniOutliner4/Filter.scpt:11950:11951: execution error: Can’t set «class OSvv» to {}. (-10006)

The dialog has “Edit script” button, which opens the script in Script Editor app, when I run it from there, it works fine - all matched rows are beign selected.

@draft8 Can you help me with debugging this?

It’s working here with OO 4.6 (v177.7.11 r268172)

You say it works in Script Editor for you – what is the context in which it is not working ?

i.e. how are you launching/running it when you get an error message ?

It wasn’t working when run from OmniFocus toolbar icon.

I’ve managed to debug this, theres some issue with assigning a value to v variable in reduce function:

-- foldLeft / reduce
-- [a] -> (a -> b) -> c -> b
on reduce(xs, f, a)
    set mf to mReturn(f)
    set v to a
    set lng to length of xs
    repeat with i from 1 to lng
        set v to mf's call(v, item i of xs, i, xs)
    end repeat
    return v
end reduce

I’ve renamed v to z and now it’s working fine.

1 Like