The Omni Group Forums

The Omni Group Forums (http://forums.omnigroup.com/index.php)
-   OmniOutliner 3 for Mac (http://forums.omnigroup.com/forumdisplay.php?f=9)
-   -   Selecting 003 rows with 'command-line' queries (http://forums.omnigroup.com/showthread.php?t=16652)

RobTrew 2010-07-02 01:43 AM

Selecting 003 rows with 'command-line' queries
 
[URL="http://forums.omnigroup.com/showthread.php?t=16396"]Another draft script[/URL] sketches a simple GUI approach to selecting a subset of OO3 rows which match certain criteria.

A limitation of that script is that while it can concatenate conditions simply linked by [B]and[/B], it doesn't allow for anything more complex, such as the use of [B]or[/B] or the application of [B]not[/B] to nested sub-clusters of conditions.
Another limitation is that it only gives access to a subset of oo3 row properties.

In cases where slightly more complex selection criteria would be helpful, or access to less-visited row properties is needed, I use a (very) quick and dirty piece of code which enables me to type and apply a query in an applescript syntax (to select a subset of rows) and then re-use the query later by selecting it from a Most Recently Used list.

Only useful to users who are fairly familiar with applescript, and with the properties and quirks of the 003 row class in the applescript library, but here it is, as a minor curiosity ...

(Note that the names of any user-defined columns must be entered in [square brackets]. There are some other comments on the query language in the script itself. In particular the possible values of the default status checkbox differ from those of user-defined columns of the checkbox type - this is just a quirk of the 003 applescript library ...)

(On my system this script runs as happily from the OO3 toolbar as from the OO3 script menu - by default it stores the MRU list in the user's Documents folder).

[IMG]http://farm5.static.flickr.com/4142/4754031991_39a4b4bff9_o.jpg[/IMG]

[CODE]-- QUERY ROWS
-- Ver 0.7b provides a multi-choice list of row properties and user-defined fields from which
-- a re-usable query can be built

-- Select OO3 rows by entering applescript-like command line queries
-- The syntax is that of an Applescript WHOSE / WHERE clause,
-- (The words "whose"/"where" should NOT be included)
-- but the names of user-defined columns must be entered in square brackets
-- (they are internally translated to a "value of cell id ..." format)
-- A most-recently-used list enables reuse of recent queries

-- In addition to square-bracketed name of user-defined columns,
-- the name of any row property listed in the applescript library may be used.
-- Note that the possible values for the state property of a row are the unquoted constants:
-- -- checked
-- -- indeterminate
-- -- none
-- -- unchecked
-- whereas the possible values of a user-defined checkbox column are strings and need to be quoted:
-- -- "checked"
-- -- "indeterminate "
-- -- "none"
-- -- "unchecked"

-- 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 plngMRU : 10
property pOpen : "["
property pClose : "]"
property pTopic : "topic"
property pNewQuery : "ENTER NEW QUERY"
property pSpacer : "-------------------------------------------------------"
property pSeparator : " - "

property pstrObjFile : "OO3MRUQueries"
property pstrSuffix : ".scpt"
-- property pstrScriptPath : (path to temporary items folder as string) &
-- pstrObjFile & pstrSuffix
property pstrScriptPath : (path to documents folder as string) &
pstrObjFile & pstrSuffix


-- SCRIPT OBJECT FOR HOLDING MRU queries BETWEEN RUNS
-- (PATH AS ABOVE)
script sMRUQueries
property plstQueries : {}
end script

on run
-- EXIT IF NO DOCUMENT IS OPEN IN 003
tell application id "com.omnigroup.OmniOutlinerPro3"
if (count of documents) < 1 then return
set oDoc to front document
end tell

-- CHOOSE A SAVED QUERY, OR WRITE A NEW ONE
set lstSavedQueries to GetSavedQueries()
set strLegibleQuery to ChooseOrWriteQuery(lstSavedQueries)
if length of strLegibleQuery < 1 then return

-- TRANSLATE THE FIELDS OF THE LEGIBLE QUERY TO
-- APPLESCRIPT CELL ID REFERENCES
set strMachineQuery to TranslateFields(strLegibleQuery)

-- RUN THE TRANSLATED QUERY, TO OBTAIN
-- A REFERENCE TO A SUBSET OF ROWS
tell application id "com.omnigroup.OmniOutlinerPro3"
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 where " & strMachineQuery & "
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 "query language error in:" & return & return & quote & strMachineQuery & quote
return
end try

try
set refRows to GetRows() of oScript
on error
display dialog "query language error, or field not found, in:" & return & return & quote & strMachineQuery & quote
return
end try

-- IN THE USER INTERFACE, SELECT ANY MATCHING ROWS
set blnFound to false
try
set blnFound to (count of refRows) > 0
on error
display dialog "query language logic error in:" & return & return & quote & strMachineQuery & quote
set selected of rows of oDoc to false
return
end try
if blnFound then
set expanded of rows of oDoc to false
set refParents to a reference to ancestors of refRows
set expanded of refParents to true
select refRows without extending
activate
-- IF ANY ROWS WERE MATCHED, STORE THE LEGIBLE VERSION OF THE QUERY
my SaveQuery(strLegibleQuery)
else
-- IF *NO* ROWS WERE MATCHED, ALERT THE USER
display dialog "No rows found in " & name of oDoc & return & return &
"with the query:" & return & tab & strLegibleQuery
end if
end tell

--store script sMRUQueries in file pstrScriptPath replacing yes
end run

on QueryDraft(varSelns)
set strDraft to ""
set blnFirst to true
if varSelns is not false then
set lngFields to length of varSelns
repeat with oField in varSelns
set text item delimiters to " - "
set strField to first text item of oField
set strClass to text item 2 of oField
if blnFirst then
if lngFields > 1 then set strDraft to strDraft & "("
set blnFirst to false
else
set strDraft to strDraft & " and ("
end if
set strDraft to strDraft & strField
if strClass contains "text" then
set strDraft to strDraft & " contains "
else if strClass is "date" then
set strDraft to strDraft & " ≥ (today )) and (" & strField & " ≤ ((today ) + 1 * day) -1"
else
set strDraft to strDraft & " is "
end if
if lngFields > 1 then set strDraft to strDraft & ")"
end repeat
end if
set text item delimiters to space
strDraft
end QueryDraft


-- Build a list of the names and classes of the properties of an object
on PropList(oObj)
set recProps to (properties of oObj)

-- AND LIST THE CLASSES OF EACH PROPERTY
set lstProps to recProps as list
set lstClass to {}
repeat with oValue in lstProps
set end of lstClass to (class of oValue) as string
end repeat

-- EXTRACT THE NAME OF EACH PROPERTY
-- BY CONVERTING THE PROPERTY LIST TO A STRING
-- AND TRANSLATING EACH 4-LETTER CODE TO AN ENGLISH PROPERTY NAME
set strDelim to text item delimiters

-- Convert the record to a string (by decoding an error message :-)
set strProps to my Rec2Str(recProps)
set text item delimiters to ", "
set lstItems to text items of strProps
set text item delimiters to ""
set first item of lstItems to (characters 3 thru end of first item of lstItems) as text

set lstPropName to {}
repeat with oItem in lstItems
-- Extract the class property from the start
set text item delimiters to ""
set lstParts to text items of oItem
set strClass to "" & first item of lstParts & ""
set oClass to run script strClass
set end of lstPropName to (oClass as string)

-- Look for any other properties in the rest of the string
set strRest to item 2 of lstParts
set text item delimiters to ", "
set lstRest to text items of strRest
repeat with strPart in lstRest
set text item delimiters to space
set strPossible to first item of text items of strPart
set text item delimiters to ":"
set lstCommaParts to text items of strPossible
if length of lstCommaParts is 2 then
set strPropName to first item of lstCommaParts
if length of strPropName > 0 then
set end of lstPropName to strPropName
end if
end repeat
end repeat

-- COMBINE THE PROPERTY NAMES WITH THE LIST OF CLASSES
-- (ALLOWING FOR ALTERNATIVE CLASSES IN THE CASE OF THE 'PARENT' PROPERTY)

repeat with iProp from 1 to count of lstPropName
set (item iProp of lstPropName) to (item iProp of lstPropName) & pSeparator & item iProp of lstClass
end repeat

set text item delimiters to strDelim

-- SORT THE LIST ALPHABETICALLY
set lstPropName to ShellSort(lstPropName)
return lstPropName
end PropList

-- Convert a record to a string by stealing the interesting part of an error message ...
on Rec2Str(rec)
-- trigger the simplest error to get the measure of its message
set recDummy to {a:1} as record
try
recDummy as string
on error strShortMsg
set strDelim to text item delimiters
set text item delimiters to {"{a:1}"}
set lngStart to (length of first text item of strShortMsg) + 1
set lngEnd to length of last text item of strShortMsg
set text item delimiters to strDelim
end try
-- trigger an informative error and grab its string representation
-- of the record
try
rec as string
on error strRecordMsg
set lngEnd to ((length of strRecordMsg) - lngEnd)
set strRecord to text lngStart thru lngEnd of strRecordMsg
end try
return strRecord
end Rec2Str

-- use a shell script to sort a list
on ShellSort(lst)
set strDelim to text item delimiters
set text item delimiters to {ASCII character 10} -- linefeed
set strList to (lst as string)
set strNew to do shell script "echo " & quoted form of strList & " | sort -f"
set lstNew to (paragraphs of strNew)
set text item delimiters to strDelim
return lstNew
end ShellSort


on SaveQuery(strLegibleQuery)
if (plstQueries of my sMRUQueries) does not contain strLegibleQuery then
set lngMax to plngMRU - 1
if length of (plstQueries of my sMRUQueries) > lngMax then
set (plstQueries of my sMRUQueries) to
items 1 thru lngMax of (plstQueries of my sMRUQueries)

set beginning of (plstQueries of my sMRUQueries) to strLegibleQuery
end if
try
store script my sMRUQueries in file pstrScriptPath replacing yes
end try
end SaveQuery


on TranslateFields(strLegibleQuery)
tell application id "com.omnigroup.OmniOutlinerPro3"

-- list bracketed versions of user-defined column names
set oDoc to front document
tell oDoc
set refCols to a reference to (columns where (name is not "") and (name is not pTopic))
set lstColNames to name of refCols
set lngCols to length of lstColNames
repeat with iCol from 1 to lngCols
set item iCol of lstColNames to pOpen & item iCol of lstColNames & pClose
end repeat
set lstColId to id of refCols
end tell
end tell

-- translated bracketed field names to cell id references
set strTransln to strLegibleQuery
repeat with iCol from 1 to length of lstColId
set strCellValue to "(value of cell id " & quote & item iCol of lstColId & quote & ")"
item iCol of lstColNames
strCellValue
strTransln
set strTransln to my FindReplace(strTransln, (item iCol of lstColNames), strCellValue)
strTransln
end repeat
strTransln
end TranslateFields

on ChooseOrWriteQuery(lstSavedQueries)
set blnNewQuery to true
if length of (lstSavedQueries) > 0 then
set varChoice to choose from list {pNewQuery} & lstSavedQueries default items {pNewQuery} with title "Select rows by querying"
if varChoice is false then return ""
set blnNewQuery to (varChoice is {pNewQuery})
end if

if blnNewQuery then
-- Offer a prompt to build a draft query
tell application "OmniOutliner Professional"
set oDoc to front document

-- GET A PROPERTIES RECORD FOR THE SAMPLE OBJECT
set oRow to last item of rows of oDoc

set lstProps to my PropList(oRow)

-- ADD THE [SQUARED BRACKETED] NAMES, AND COLUMN TYPES, OF ANY USER-DEFINED COLUMNS
set strID to id of topic column of oDoc
set lstCols to (columns of oDoc where (name is not ""))
set lstFields to {}
repeat with oCol in lstCols
set end of lstFields to "[" & name of oCol & "]" & pSeparator & type of oCol
end repeat
end tell

set varSelns to choose from list (lstFields & lstProps) with title "choose properties to filter on" with prompt
"select multiple properties with Cmd-Click" with multiple selections allowed and empty selection allowed

set strDraft to QueryDraft(varSelns)

-- Get a query with user-defined fieldnames in square brackets from user
set recResponse to display dialog "rows where:" default answer strDraft buttons {pSpacer & " Run query " & pSpacer}
if recResponse is not false then
set strQuery to text returned of recResponse
else
return
end if
else
set strQuery to varChoice
end if
strQuery
end ChooseOrWriteQuery

on GetSavedQueries()
-- RETRIEVE ANY STORED QUERIES FROM RECENT USE
try
set sMRUQueries to load script file pstrScriptPath
on error
store script my sMRUQueries in file pstrScriptPath replacing yes
end try
return plstQueries of my sMRUQueries
end GetSavedQueries

on FindReplace(strText, strFind, strReplacement)
set {strText, strFind} to {strText as string, strFind as string}
set AppleScript's text item delimiters to strFind
set strText to text items of strText
set AppleScript's text item delimiters to strReplacement
tell strText to set strText to item 1 & ({""} & rest)
set AppleScript's text item delimiters to space
strText
end FindReplace
[/CODE]

RobTrew 2010-07-08 01:46 AM

Ver 0.7 (see updated post above) now prompts the user (when "Enter New Query" is chosen) with a multi-choice list of row properties and user defined fields, from which a re-usable query can be built.

Date/times are a bit tricky to do this way (though the term 'today' may be used in queries [current date with time set to 00:00]) and I personally tend to use my [URL="http://forums.omnigroup.com/showthread.php?t=16396"]GUI script[/URL] when dates are involved.

[IMG]http://farm5.static.flickr.com/4098/4774348760_65677b1a13_o.jpg[/IMG]


All times are GMT -8. The time now is 10:07 PM.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2019, vBulletin Solutions, Inc.