The Omni Group
These forums are now read-only. Please visit our new forums to participate in discussion. A new account will be required to post in the new forums. For more info on the switch, see this post. Thank you!

Go Back   The Omni Group Forums > OmniOutliner > OmniOutliner 3 for Mac
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Filtering Omnioutliner rows by values of columns etc. Thread Tools Search this Thread Display Modes
Script to make a filtered copy of an OmniOutliner 3 document

(a related script simply highlights all rows in the current document which match specified filter criteria)

Row filtering for OmniOutliner is a feature which has sometimes been requested in this forum, and I have personally often needed to extract a filtered subset of Omnioutliner rows from a larger document (selecting these rows according to values in columns, outline levels, checkbox status, or simply by search terms occurring in the topic or notes)

Having accumulated, over time, an untidy pile of single-purpose applescript snippets for doing such things, and finding it sometimes inefficient to have to hand-roll code in a hurry, I have sketched a very rough draft of a more general script, which prompts the user, in a few steps, for a definition of which kind of rows in the current document should be copied, and then creates a new document containing copies of only those rows that match the pattern specified by the user.

It's a simple tool, with a user-interface much constrained by the limitations of raw applescript, but ...

I post it here, as is, without any warranty whatsoever,
and you are welcome to experiment and make suggestions.

The usage is something like this:
  1. Make sure that the OO3 document of which you which to make a filtered copy is saved and backed up.
  2. Apple-click (Cmd-Click) on one or more column headers in the document, to select any columns containing values by which you wish to filter.
  3. RUN THE SCRIPT (You will be shown a list of the selected columns, and of the non-column properties of OO3 rows (Status checkbox, outline Level, note text).
  4. Apple-click in the Applescript list to select all the fields on which
    you wish to filter)
  5. Confirm your choice of fields, and respond to prompts on the desired values, ranges, and operators.

If there are any rows that match what you have asked for, a new document, containing copies of those matching rows, will be created and given focus.

(Under the hood, the script simply assembles Applescript WHERE statements, applies them to get a list of matching rows, and duplicates the relevant rows into a new document).

A TIP

The string missing value may be entered in place of date or numeric (including duration) values, in order to search for rows in which no value has been entered in a particular date or numeric column.


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

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

(*
	(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 + whpalmer4

-- 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 Date filtering bug fixed by whpalmer4

*)

-- 	Copyright © 2010, Robin Trew
--	Additional code whpalmer4 2011
--  	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 MAKE A NEW DOCUMENT LISTING MATCHING ROWS

tell application "OmniOutliner Professional"
	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

-- bug fixed by whpalmer4 2011/09/07
-- PATCH THE "WHERE" CONDITION INTO THE TEXT OF A SIMPLE SCRIPT
set strScript to "
script 
	on cDateScriptLiteral(str)
		(date str)
	end cDateScriptLiteral
	
	on GetRows()
		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
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 rows of front document to false
		set refParents to a reference to ancestors 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 columns 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 enumerations of oCol)
					duplicate refEnums to end of enumerations of oNewCol
				end if
			end tell
		end repeat
		
		-- COPY FILTERED ROWS INTO NEW DOCUMENT
		duplicate refRows to the end of the children of docFiltered
		set expanded of rows 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 selected columns of oDoc
		set refAllCols to a reference to columns 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 {}
		
		-- separate the default property fields from the user-definable column fields
		-- 		set lstPropFields to «event ScTlLInt» varReply given «class PL2 »:lstDefaultProps
		-- 		set lstColFields to «event ScTlLDif» varReply given «class PL2 »:lstPropFields
		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 (columns 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 enumerations 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)
			if strTo is not strFrom then
				{{popGE, WrapDate(dteFrom)}, {popLE, WrapDate(dteTo)}}
			else
				{{popIS, WrapDate(dteFrom)}}
			end if
		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(str)
	(date str)
end cDate

-- bug fixed by whpalmer4 2011/09/07
-- Format date for WHERE clause
on WrapDate(dteAny)
	("my cDateScriptLiteral(" & quote & dteAny as string) & quote & ")"
end WrapDate

-- 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)
	using terms from application "OmniOutliner Professional"
		set lngMax to 0
		set lstLevels to level of rows 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 using terms from
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

Last edited by RobTrew; 2011-09-08 at 12:27 AM.. Reason: Include whpalmer4's edits to fix date-handling bug
 
Hi Rob,

just stumbled on this by pure accident - my luck.

THIS SAVES MY DAY :-D - thanks for sharing. I'd be looking for this to manage my risk lists out from minutes that are kept in a general form.

Not being a strong Applescript hacker nor being the most skilled OO on the block the question would be: Can it be made so that the date stamp (that I add to all things flagged with a risk) be used to sort the result? Well I could just select the 'Keep Sorted' in the resulting Document.
 
Quote:
Originally Posted by ptorngaard View Post
Can it be made so that the date stamp (that I add to all things flagged with a risk) be used to sort the result? Well I could just select the 'Keep Sorted' in the resulting Document.
I'm glad to hear that it's proving useful.

I can see that sorting could be useful and I may add it at some point in the next month or two ...

(Sorting is not the kind of thing that Applescript is particularly well adapted to, but I will give it some thought)
 
Quote:
Originally Posted by Wobbly View Post
Is there a way to modify the "filtering values of columns copy"- script to always copy into a specified window instead creating a new one?
You mean append the results to a pre-existing document ?

How would you like that to work ?
  • get the script to list possible target documents (chosen from those that are open)
  • have a property in the script which points permanently at a particular document ?
  • something else ?

Last edited by RobTrew; 2010-09-29 at 12:18 PM..
 
Yeah, appending to an existing document.

Choosing from a list of open documents would be fine, but the workflow would be too slow, if we had to point it every time a copy is getting done. So it would be best if we could point to a specific document which gets used from then on until one would like to append to a different document.

If that's too complicated, pointing at a predefined document permanently would also be fine. Maybe even the more elegant solution.

It would depend on the personal workflow, which option would be preferable.

Last edited by Wobbly; 2010-09-30 at 01:29 AM..
 
Quote:
Originally Posted by Wobbly View Post
it would be best if we could point to a specific document which gets used from then on until one would like to append to a different document.
Certainly feasible - you could do it with a persistent global variable (which would exclude use from the OO3 toolbar, but should work, I think from FastScripts etc) or with a saved sub-script which retained a record of the output document.

I'm moving towards some deadlines here, so I'll have to flag it as a 'pending' project, but others are very welcome to tinker with my code in the meanwhile.
 
As it would obviously be easier, maybe someone could just change the "always creating new document"-behaviour, so it opens a new document, but copies all following information in this document, without creating new ones.

Would make life much easier if you are collecting coherent information from multiple documents.
 
Quote:
Originally Posted by Wobbly View Post
As it would obviously be easier, maybe someone could just change the "always creating new document"-behaviour, so it opens a new document, but copies all following information in this document, without creating new ones.

Would make life much easier if you are collecting coherent information from multiple documents.
Here is a simple variant, which may help.

It still outputs the filtered lines to a document called "FILTERED", creating it if no document with that name is open.

If, however, a document called "FILTERED" is already open, then the filtered lines are appended to it. (New columns are created in the output document if necessary).

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

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

(*
	(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 If an output document named "FILTERED" is already open, then the filtered lines are appended to it.
			(Creating new columns only if necessary).
--			otherwise a new document called "FILTERED" is created.

*)

-- 	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 MAKE A NEW DOCUMENT LISTING 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()
		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
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 id "com.omnigroup.OmniOutlinerPro3"
		set expanded of rows of front document to false
		set refParents to a reference to ancestors 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 id "com.omnigroup.OmniOutlinerPro3"
		set oDoc to front document
		
		try
			set docFiltered to first document where name is "FILTERED"
			set blnReUse to true
		on error
			set docFiltered to make new document with properties {name:"FILTERED"}
			set blnReUse to false
		end try
		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 columns of oDoc
			set oCol to column iCol of oDoc
			tell oCol
				set {varName, varType, varWidth} to {name, type, width}
			end tell
			tell docFiltered
				try
					set oNewCol to (first column where (name = varName) and (type = varType))
				on error
					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 enumerations of oCol)
						duplicate refEnums to end of enumerations of oNewCol
					end if
				end try
			end tell
		end repeat
		
		-- COPY FILTERED ROWS INTO NEW DOCUMENT
		duplicate refRows to the end of the children of docFiltered
		set expanded of rows 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 selected columns of oDoc
		set refAllCols to a reference to columns 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 {}
		
		-- separate the default property fields from the user-definable column fields
		-- 		set lstPropFields to «event ScTlLInt» varReply given «class PL2 »:lstDefaultProps
		-- 		set lstColFields to «event ScTlLDif» varReply given «class PL2 »:lstPropFields
		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 (columns 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 enumerations 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)
			if strTo is not strFrom then
				{{popGE, WrapDate(dteFrom)}, {popLE, WrapDate(dteTo)}}
			else
				{{popIS, WrapDate(dteFrom)}}
			end if
		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(str)
	(date str)
end cDate


-- Format date for WHERE clause
on WrapDate(dteAny)
	("my cDate(" & quote & dteAny as string) & quote & ")"
end WrapDate

-- 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)
	using terms from application "OmniOutliner Professional"
		set lngMax to 0
		set lstLevels to level of rows 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 using terms from
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
 
This is a great script. However, when run to filter checked items it does not include the parent row unless all the children are selected. Furthermore, it does not retain the original parent to children formatting. Can you please update the script to account for this?

Retaining the formatting is secondary to including the parent on partially selected children for my needs. I'm trying to create a specifications checklist template for residential construction where I can pick and choose items to include in contracted specifications.

Thanks,
BE
 
Quote:
Originally Posted by whedwards3 View Post
it does not include the parent row unless all the children are selected
Parents of children with mixed status are flagged as "indeterminate" in applescript, and are thus excluded from this kind of simple search.
(If all of their children are "checked" they too acquire the status "checked").

A tool better suited to your purpose (finer control of the search, scope for copying formatting) may be the 'command-line' query script which I have posted in another thread.

You could use it to save and re-use the search:

rows where (status is checked) or (status is indeterminate)


and it will automatically select matching rows in your source document. You could then copy and paste them, with formatting, elsewhere.

--
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
add/delete rows/columns in a table. fantzhi OmniGraffle General 1 2014-04-21 07:46 AM
Styling Rows vs. Columns pierog OmniOutliner 3 for Mac 1 2010-07-15 09:03 AM
OmniOutliner: Fastest way to set data in rows, columns or blocks? nelsonm AppleScripting Omni Apps 3 2009-07-27 02:45 PM
Is there a way to highlight table rows or columns? johanna OmniGraffle General 2 2009-01-20 01:19 PM
batch change of selected columns/rows talazem OmniOutliner 3 for Mac 0 2006-07-21 09:25 AM


All times are GMT -8. The time now is 01:28 AM.


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