The Omni Group Forums - Select all Omnioutliner rows which match certain criteria

[I]Script to [B]select all OmniOutliner 3 rows[/B] which match a set of filtering criteria[/I]

(A [URL="http://forums.omnigroup.com/showthread.php?t=16392"]related script[/URL] makes a filtered copy of an OO3 document)

(I tend to use this script in combination with [URL="http://forums.omnigroup.com/showthread.php?t=16397"]another simple script[/URL] which duplicates all [B]selected[/B] rows, including their column data, into a fresh document).

OmniOutliner 3 has an excellent Batch Search function (Command-Shift-F) for finding rows with particular strings in any rich text column or note text.

It does not, however, support searching by values in non-text columns, or by checkbox status or outline level.

Here is a script which will select all rows which match a set of user-specified criteria, which may include values (or ranges of values) in numeric and date columns, checkbox states and outline levels, as well as search strings.

(Matching rows and their ancestors will be visible, and all other rows will be collapsed. You can use View > Expand All (Cmd-Ctrl-9) to re-expand all rows)

It is a simple variant of the code [URL="http://forums.omnigroup.com/showthread.php?t=16392"]posted in another thread[/URL] for generating a new document which contains only the specified rows.

Before running the script, Command-Click the headers of any columns on which you wish to search for particular values or ranges of values. The script will also offer to search by the state of the left-hand status checkbox, by outline level, and/or by note text. (You can use Command-Click again in the Applescript selection list to make multiple selections of columns and properties to filter by).

(The phrase [I]missing value[/I] may be entered in place of dates numbers or durations, to search for rows which in which no date or number has been added for a particular column.)

[CODE](*
SELECTS ALL ROWS MATCHING A SET OF USER-SPECIFIED CRITERIA
(AND/OR) MAKES A FILTERED COPY OF THE FRONT OMNIOUTLINER DOCUMENT
*)

-- Ver 0.9

-- Global functions determined by these settings
property pblnSELECT_ROWS : true
property pblnMAKE_FILTERED_COPY : false
property pblnPlaceQueryInClipboard : true

(*
(PROMPTS THE USER FOR DESIRED (RANGE OF) VALUES FOR ANY COLUMNS

*** WHOSE HEADERS ARE SELECTED ***

(APPLE/CMD-Click on one or more column headers before running script )

AND ALSO OFFERS TO FILTER ROWS BY:
- STATUS CHECKBOXES,
- NOTE TEXT,
- OUTLINE LEVEL
)

NOTE - in response to prompts for column values or ranges, the string:

missing value

may be entered in place of numeric or date values, in order to search
for rows for which no numeric or date value has been entered for a
specified column.

RobTrew

-- Version History
-- VER 0.1 Global behaviour determined by settings of pblnSELECT_ROWS and pblnMAKE_FILTERED_COPY
-- Ver 0.2 Exits quietly in absence of front document, suggests column selections if no criteria specified
-- Ver 0.3 Bypasses previous use of list and record functions .osax
-- Ver 0.4 Field selector lists ALL columns in the document - not only those selected in OO GUI
-- (still preselects names of any columns that *are* selected in the GUI)
-- Ver 0.41 Legible version of Query displayed if now rows are matched.
Global property allows for legible version of query to be placed in clipboard
-- Ver 0.42 Fixed a bug which ignored columns selected in Field selector but not selected in GUI
-- Ver 0.43 Fixed a bug involving searches in the topic and rich text cells
-- Ver 0.5 Simplified text searches to a single step ("contains" and "is" buttons added below the text field)
-- Ver 0.6 Adjusted Run Script handling to allow launching from OO3 toolbar buttons
-- Ver 0.7 Corrected a bug in the handling of dates
-- Ver 0.8 Changed handling of dates for compatibility with 003 toolbar
-- Ver 0.9 Further adjusted date handling so that date range searches start
by default with time 00:00 and end by default with time 23:59:59

*)

-- Copyright © 2010, Robin Trew
-- All rights reserved.
--
-- Redistribution and use in source and binary forms, with or without modification,
-- are permitted provided that the following conditions are met:
--
-- - Redistributions of source code must retain the above copyright notice,
-- this list of conditions and the following disclaimer.
-- - Redistributions in binary form must reproduce the above copyright notice,
-- this list of conditions and the following disclaimer in the documentation
-- and/or other materials provided with the distribution.
--
-- THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS
-- "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
-- THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
-- IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR
-- ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
-- (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;
-- LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY,
-- WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
-- ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

property popIS : "is"
property popNOT : "is not"
property popGE : "≥"
property popLE : "≤"
property popCONTAINS : "contains"
property pstrMissingValue : "missing value"

property pNullString : ""
property pTopic : "topic"
property pNote : "note"
property pCellValue : "value of cell id "
property pByLevel : "Filter by outline level"
property pByChecked : "Filter by left-hand checkbox"
property pZero : "0"

property pChecked : "checked"
--property pIndeterminate : "indeterminate"
property pUnChecked : "unchecked"
property pLevel : "level"
property pState : "state"
property pDate : "date"
property pSkip : "skip"
property pOK : "OK"

-- PROMPT USER FOR FILTER CONDITIONS
-- AND SELECT MATCHING ROWS

tell application id "com.omnigroup.OmniOutlinerPro3"
if (count of documents) < 1 then return
set oDoc to front document

-- GET A LIST OF CONDITIONS FROM THE USER
set lstFilterConditions to my ConditionList(oDoc)
if (count of lstFilterConditions) > 0 then

-- TRANSLATE THE CONDITIONS INTO AN APPLESCRIPT "WHERE" STATEMENT
set strWhere to my WhereString(lstFilterConditions, false)
set strTranslation to my WhereString(lstFilterConditions, true)

else
display dialog "No conditions specified" buttons {"OK"} with title "Filter Omnioutliner"
return
end if
end tell

-- PATCH THE "WHERE" CONDITION INTO THE TEXT OF A SIMPLE SCRIPT
set strScript to "
script
on GetRows()
set today to today()
tell application " & quote & "OmniOutliner Professional" & quote & linefeed & "
set oDoc to front document
tell oDoc
return a reference to rows " & strWhere & "
end tell
end tell
end GetRows

on today()
set dteToday to current date
set hours of dteToday to 0
set minutes of dteToday to 0
set seconds of dteToday to 0
dteToday
end today
end script
"

-- GET A REFERENCE (FROM THE PATCHED SCRIPT)
-- TO THE ROWS WHICH MATCH THE "WHERE" FILTER
try
set oScript to run script strScript
on error
display dialog "Problem in parsing query:" & return & return & quote & strWhere & quote
return
end try
set refRows to GetRows() of oScript

if (count of refRows) < 1 then
display dialog "No rows matching:" & return & return & strTranslation & return & return & ¬
"found in " & name of oDoc buttons {"OK"} with title "Filter Omnioutliner"
return
end if

if pblnSELECT_ROWS then
-- SELECT MATCHING ROWS
-- (Matching rows and their ancestors will be visible, and other material will be collapsed.
-- You can use View > Expand All (Cmd-Ctrl-9) to re-expand everything)
tell application "OmniOutliner Professional"
set expanded of every row of front document to false
set refParents to a reference to every ancestor of refRows
set expanded of refParents to true
select refRows without extending
end tell
end if

if pblnMAKE_FILTERED_COPY then
-- MAKE A NEW DOCUMENT CONTAINING ONLY
--A FLAT (AND COLLAPSED) LIST OF THE FILTERED ROWS
tell application "OmniOutliner Professional"
set oDoc to front document
set docFiltered to make new document with properties {name:"FILTERED"}
set width of topic column of docFiltered to width of topic column of oDoc

-- COPY ADDITIONAL COLUMNS FROM ORIGINAL DOCUMENT
repeat with iCol from 3 to count of every column of oDoc
set oCol to column iCol of oDoc
tell oCol
set {varName, varType, varWidth} to {name, type, width}
end tell
tell docFiltered
set oNewCol to make new column with properties {name:varName, type:varType, width:varWidth}
if varType is popup then
set refEnums to (a reference to every enumeration of oCol)
duplicate refEnums to end of every enumeration of oNewCol
end if
end tell
end repeat

-- COPY FILTERED ROWS INTO NEW DOCUMENT
duplicate refRows to the end of the every child of docFiltered
set expanded of every row of docFiltered to false
activate
end tell
end if

----------END OF SCRIPT-------------------------------------------------------------------------------------------------

----------FUNCTION DEFINITIONS--------------------------------------------------------------------------------------

-- PROMPT USER FOR DESIRED (RANGE OF) VALUES OF ANY COLUMNS WHOSE HEADERS ARE SELECTED,
-- AND OFFER TO FILTER BY STATUS CHECKBOXES,
-- (if status checkboxes are not hidden in the front window)
-- View > Hide Status Checkboxes
-- View > Show Status Checkboxes
-- BY NOTE TEXT,
-- (if at least one note is expanded in the front window)
-- AND BY ROW LEVEL
-- (if at at least one parent row is collapsed in the front window)

-- RETURN A LIST OF CONDITIONS
-- LIST: {CONDITION, CONDITION, ...}
-- CONDITION: {FIELDNAME, {{OPERATOR, VALUE}, {OPERATOR, VALUE} ...}}

-- BEHAVIOUR:
-- If the user cancels at any prompt skip on to the next prompt
-- If the input is unparsable (generates an error)
-- Issues a warning, and skips on to the next prompt
on ConditionList(oDoc)
using terms from application "OmniOutliner Professional"
set refSeldCols to a reference to every selected column of oDoc
set refAllCols to a reference to every column of oDoc

-- Get a list of the columns to filter on
set lstSeldCols to name of refSeldCols
if length of lstSeldCols is 0 then ¬
set lstSeldCols to {name of (topic column of oDoc)}
set lstAllCols to name of refAllCols
set lstAllCols to my removefromlist("", lstAllCols)
set lstDefaultProps to {"Status checkbox", "Outline Level", "Note text"}
set {strStatus, strOutline, strNote} to lstDefaultProps
set varReply to choose from list lstDefaultProps & lstAllCols with title ¬
"Columns & properties to filter by" with prompt "Make multiple selections with" & return & ¬
"Cmd-click" default items lstSeldCols with multiple selections allowed

if varReply is false then return {}

set {lstPropFields, lstColFields} to SeparateLists(varReply, lstDefaultProps)

-- GET CRITERIA FOR THE COLUMNS WHICH THE USER HAS SELECTED
set lstWhere to {}
repeat with strField in lstColFields
set lstCols to (every column of oDoc where name is strField)
repeat with oCol in lstCols
tell oCol
set {strName, cType, strID} to {name, type, id}
end tell

set strCell to pCellValue & quote & strID & quote

-- where state is checked, state is unchecked
if cType is checkbox then
set recReply to display dialog "Select rows with " & strName & ":" buttons {pSkip, pUnChecked, pChecked} with title "Filter on checkbox state"
set btnChosen to button returned of recReply
if btnChosen is not pSkip then
set strCellState to "state of cell id " & quote & strID & quote
set end of lstWhere to {{strCellState, strName}, {{popIS, btnChosen}}}
end if
else if cType is rich text then
set varCondition to TextFieldCondition(oDoc, oCol, strName)
if varCondition is not missing value then set end of lstWhere to varCondition
else if cType is date then
set strNow to short date string of (current date)
set lstOpVals to GetDateRange(strNow)
if lstOpVals is not missing value then ¬
set end of lstWhere to {{strCell, strName}, lstOpVals}
else if cType is duration then
set lstOpVals to GetRangeOpValues("Duration", pZero)
if lstOpVals is not missing value then ¬
set end of lstWhere to {{strCell, strName}, lstOpVals}
else if cType is number then
set lstOpVals to GetRangeOpValues("Number", pZero)
if lstOpVals is not missing value then ¬
set end of lstWhere to {{strCell, strName}, lstOpVals}
else if cType is popup then
set lstOptions to name of every enumeration of column id strID of oDoc
set lstPop to choose from list lstOptions with title strName
if lstPop is false then return
set varPop to quote & (first item of lstPop) & quote
set lstOpVals to GetOpForValue("Popup", varPop, {popIS, popNOT})
if lstOpVals is not missing value then ¬
set end of lstWhere to {{strCell, strName}, lstOpVals}
end if
end repeat
end repeat

-- GET CRITERIA FOR ANY NON-COLUMN OO ROW PROPERTIES THAT THE USER HAS SELECTED
-- Status, Level, Note cell
tell oDoc
repeat with strField in lstPropFields
set strField to strField as string
if (strField as string) is strStatus then
set recReply to display dialog "Select rows with " & strStatus & ":" buttons {pSkip, pUnChecked, pChecked} with title "Filter on status checkboxes"
set btnChosen to button returned of recReply
if btnChosen is not pSkip then ¬
set end of lstWhere to {{pState, pState}, {{popIS, btnChosen}}}
else if strField is strOutline then
try
set lngDeepest to my MaxDepth(oDoc)
set lngTop to (text returned of (display dialog "From outline level" default answer 1)) as integer
set lngBottom to (text returned of (display dialog "To outline level" default answer lngDeepest)) as integer
if lngTop is not lngBottom then
set end of lstWhere to {{pLevel, pLevel}, {{popGE, lngTop}, {popLE, lngBottom}}}
else
set end of lstWhere to {{pLevel, pLevel}, {{popIS, lngTop}}}
end if
on error
display dialog "invalid outline level - skipping outline filtering" buttons {"OK"} ¬
with icon 2 with title "User entry error"
end try
else if strField is strNote then
set varCondition to my TextFieldCondition(oDoc, note column, pNote)
if varCondition is not missing value then set end of lstWhere to varCondition
end if
end repeat
end tell
end using terms from
lstWhere
end ConditionList

-- Get a condition list for a Topic, Note, or user-defined Rich Text field
on TextFieldCondition(oDoc, oCol, strFieldName)
using terms from application "OmniOutliner Professional"
set recReply to display dialog strFieldName & ": search term" buttons {pSkip, popIS, popCONTAINS} default answer "" with title "Filter on search string"
set strID to id of oCol
if strID is id of topic column of oDoc then
set strTextCell to pTopic
else if strID is id of note column of oDoc then
set strTextCell to pNote
else
set strTextCell to pCellValue & quote & strID & quote
end if

set strButton to button returned of recReply
if strButton is not pSkip then
set strTerm to (text returned of recReply)
if length of strTerm > 0 then
set strTerm to quote & strTerm & quote
return {{strTextCell, strFieldName}, {{strButton, strTerm}}}
else
return {{strTextCell, strFieldName}, {{popIS, quote & quote}}}
end if
end if
missing value
end using terms from
end TextFieldCondition

-- Translate a list of conditions into an Applescript WHERE statement
on WhereString(lstWhere, blnTranslation)
set lngClauses to length of lstWhere
if lngClauses > 0 then
set strWhere to "where "
set lstClause to first item of lstWhere
set strWhere to strWhere & WhereClause(lstClause, blnTranslation)
repeat with iClause from 2 to lngClauses
set lstClause to item iClause of lstWhere
set strWhere to strWhere & " and " & WhereClause(lstClause, blnTranslation)
end repeat
else
return pNullString
end if
if pblnPlaceQueryInClipboard then tell application "Finder" to set the clipboard to strWhere
strWhere
end WhereString

-- Translate a single condition into an Applescript WHERE clause
-- (used by WhereString)
on WhereClause({{strRef, strTrans}, lstOPValuePairs}, blnTranslation)
if blnTranslation then
set strField to "[" & strTrans & "]"
else
set strField to strRef
end if
if (length of lstOPValuePairs > 1) then
set {{strOP1, strVal1}, {strOp2, strVal2}} to lstOPValuePairs
"(" & strField & space & strOP1 & space & strVal1 & ") and (" & strField & space & strOp2 & space & strVal2 & ")"
else
set {{strOP, strVal}} to lstOPValuePairs
"(" & strField & space & strOP & space & strVal & ")"
end if
end WhereClause

-- Get a range of numeric values from the user
on GetRangeOpValues(strTitle, strDefault)
try
set strFrom to (text returned of (display dialog strTitle & " range: FROM " default answer strDefault with title strTitle))
if strFrom is not pstrMissingValue then
set rTest to strFrom as real
set strTo to (text returned of (display dialog strTitle & " range: TO " default answer strFrom with title strTitle))
if strFrom is not pstrMissingValue then set rTest to strTo as real
if strTo is not strFrom then
{{popGE, strFrom}, {popLE, strTo}}
else
{{popIS, strFrom}}
end if
else
{{popIS, pstrMissingValue}}
end if
on error
display dialog "invalid number - skipping numeric value filtering" buttons {"OK"} ¬
with icon 2 with title "User entry error"
missing value
end try
end GetRangeOpValues

-- Get a range of dates from the user
on GetDateRange(strDefault)
try
set strFrom to (text returned of (display dialog "date range: FROM " default answer strDefault))
if strFrom is not pstrMissingValue then
set dteFrom to cDate(strFrom)
set strTo to (text returned of (display dialog "date range: TO " default answer strFrom))
set dteTo to cDate(strTo)
{{popGE, WrapDate(dteFrom, false)}, {popLE, WrapDate(dteTo, true)}}
else
{{popIS, pstrMissingValue}}
end if
on error
display dialog "invalid date - skipping date value filtering" buttons {"OK"} ¬
with icon 2 with title "User entry error"
missing value
end try
end GetDateRange

on cDate(strDate)
date strDate
end cDate

-- Format date for WHERE clause
on WrapDate(dteAny, blnEnd)
set dteBase to date "00:00" of (current date)
if blnEnd then set dteAny to (dteAny + 1 * days)
set lngDiff to (date "00:00" of dteAny) - dteBase

set strForm to "(today "
if lngDiff > 0 then
set strForm to strForm & "+ ("
else if lngDiff < 0 then
set strForm to strForm & "- ("
else
return "today"
end if

set lngDiff to abs(lngDiff)
set lngWeeks to lngDiff div weeks
if lngWeeks > 0 then
set strForm to (strForm & lngWeeks as string) & " * weeks "
set lngDays to ((lngDiff mod weeks) div days)
else
set lngDays to lngDiff div days
end if
if lngDays > 0 then
if lngWeeks > 0 then set strForm to strForm & "+ "
set strForm to (strForm & lngDays as string) & " * days"
end if
set strForm to strForm & ")"
if blnEnd then
set strForm to strForm & " - 1)"
else
set strForm to strForm & ")"
end if
strForm
end WrapDate

on abs(numValue)
if numValue < 0 then
-numValue
else
numValue
end if
end abs

-- Get a choice of logical operator from the user
on GetOpForValue(strTitle, strValue, {stropDefault, stropAlt})
set {strOpt1, strOpt2} to {stropDefault & space & strValue, ¬
stropAlt & space & strValue}
set lstValue to choose from list {strOpt1, strOpt2} default items {strOpt1} with title strTitle
if lstValue is false then return missing value
if (first item of lstValue) is strOpt1 then
{{stropDefault, strValue}}
else
{{stropAlt, strValue}}
end if
end GetOpForValue

-- Find the deepest outline level of the document
on MaxDepth(oDoc)
tell application "OmniOutliner Professional"
set lngMax to 0
set lstLevels to level of every row of oDoc
-- set lstLevels to «event ScTlLUon» lstLevels with «class FCdp» given «class PL2 »:{}
repeat with lngLevel in lstLevels
if lngLevel > lngMax then set lngMax to lngLevel
end repeat
lngMax as integer
end tell
end MaxDepth

-- Used to divide the list of selected fields between column fields and built-in property fields
on SeparateLists(lstMain, lstSought)
set lstIntersect to {}
set lstRest to {}
repeat with strSought in lstSought
set strSought to strSought as string
if (strSought is in lstMain) then set end of lstIntersect to strSought
end repeat
repeat with strMain in lstMain
set strMain to strMain as string
if not (strMain is in lstIntersect) then set end of lstRest to strMain
end repeat
{lstIntersect, lstRest}
end SeparateLists

-- Delete an element from a list
on removefromlist(oElement, lstList)
set lstRest to {}
repeat with oItem in lstList
set oItem to contents of oItem
if oItem is not oElement then set end of lstRest to oItem
end repeat
contents of lstRest
end removefromlist
[/CODE]