[DrJJWMac]

This script reports "active" tasks, defined as those that are flagged and not completed. The output format is as

HEADER (COUNT)
BAR
PRE-STRING context SEPARATER task
...

Action groups can be displayed expanded or not, whereby in the latter case they show as

Project Group SEPARATER group name <COUNT>

Tasks with empty contexts show as

PRE-STRING <empty> SEPARATER task

The report can be sorted forward or reverse, and counts can be hidden. I use this with GeekTool -- caveat OF must be running for it to work. The script is attached in an archive.

(*
	This script creates a list of flagged actions and reports them in (reverse) order by context
	
	Report Format		
		NOHEADER
			or
		HEADER (COUNT)
		BAR
		PRESTRING context SEPARATOR task
		...
*)

property reverseSort : "true" -- set to false for forward sort
property showCount : "true" -- set to false to avoid showing count
property expandGroup : "true" -- set to true to expand action groups
property headerStr : "Active" -- HEADER text when active count > 0
property nheaderStr : "No Active Tasks" -- NOHEADER when active count = 0
property mtcntxtStr : "<empty>" -- string when context is missing
property barStr : "--" -- BAR string after header
property preStr : "●" & tab -- PRESTRING prior to each list item
property actGroupStr : "Project Group" -- pre-string when task is action group (shows when expandGroup = false)
property sepStr : " : " -- SEPARATOR string between context and task

tell application "OmniFocus"
	tell front document
		-- get list of active tasks
		set AList to flattened tasks where ((flagged is true) and (completed is false))
		if ((count of AList) > 0) then
			-- make the header
			set theCount to (count of AList)
			set SortedList to ""
			set theReport to ""
			-- start parsing each task
			repeat with ListItem in AList
				if (the number of tasks of ListItem = 0) then
					-- a single task
					try
						set theContext to preStr & (name of context of ListItem) & sepStr
					on error
						set theContext to preStr & mtcntxStr & sepStr
					end try
					set theAction to (name of ListItem)
					set SortedList to SortedList & (theContext & theAction) as list
				else
					-- an action group (no expand or expand)
					if (expandGroup is "false") then
						-- do not expand action groups
						if (showCount is "true") then
							set countStr to " <" & (number of tasks of ListItem) & ">"
						else
							set countStr to ""
						end if
						set theContext to preStr & actGroupStr & sepStr
						set theAction to (name of containing project of ListItem) & sepStr & (name of ListItem) & countStr
						set SortedList to SortedList & (theContext & theAction) as list
					else
						-- expand action groups completely
						set XList to ((flattened tasks of ListItem) where (completed is false))
						set theCount to theCount - 1
						repeat with XListItem in XList
							try
								set theContext to preStr & (name of context of XListItem) & sepStr
							on error
								set theContext to preStr & mtcntxtStr & sepStr
							end try
							set theAction to (name of XListItem)
							set theCount to theCount + 1
							set SortedList to SortedList & (theContext & theAction) as list
						end repeat
					end if
				end if
			end repeat
			if (reverseSort is "true") then
				set SortedList to the reverse of my simple_sort(the SortedList)
			else
				set SortedList to my simple_sort(the SortedList)
			end if
			repeat with ListItem in SortedList
				set theReport to theReport & (ListItem) & return
			end repeat
		else
			set theReport to nheaderStr
		end if
	end tell
	if (showCount is "true") then
		set countStr to " (" & theCount & ")"
	else
		set countStr to ""
	end if
	set theReport to headerStr & countStr & return & barStr & return & theReport
	return theReport
end tell

on simple_sort(my_list)
	set the index_list to {}
	set the sorted_list to {}
	repeat (the number of items in my_list) times
		set the low_item to ""
		repeat with i from 1 to (number of items in my_list)
			if i is not in the index_list then
				set this_item to item i of my_list as text
				if the low_item is "" then
					set the low_item to this_item
					set the low_item_index to i
				else if this_item comes before the low_item then
					set the low_item to this_item
					set the low_item_index to i
				end if
			end if
		end repeat
		set the end of sorted_list to the low_item
		set the end of the index_list to the low_item_index
	end repeat
	return the sorted_list
end simple_sort

If you'd like to get at the raw data without needing OmniFocus to be running, you could write something along these lines (to get sorted rows of three tab-delimited fields).

( man sort in Terminal.app will list the other switches for the bash sort command )

property pstrSQL : quoted form of "
SELECT p.name, c.name, tt.name 
FROM ((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt 
left join task p on tt.task=p.persistentIdentifier) 
left join context c on tt.context = c.persistentIdentifier
WHERE tt.flagged=1 and tt.dateCompleted is null"

tell application id "MACS" to set strID to (get id of application file id "OFOC")
set strDBPath to "~/Library/Caches/" & strID & "/OmniFocusDatabase2"
set strLines to do shell script "sqlite3 " & strDBPath & space & pstrSQL & " | tr '|' '\\t' | sort -k 1,2"

--

[DrJJWMac]

I can just about follow what is happening ... the SQLite commands are stored, the datapath is stored, the commands are assembled, and the result is sent through a shell pipe.

Nice to watch a master at work here!

Thanks.

--
JJW

No mastery at all, but you are right – the sqlite approach is not very good for legibility or maintenance – especially as code like this is likely to break with OF2

(but echoing lines to the sort command may prove to be a useful thing to experiment with. Here it's just sorting by columns 1 and 2.)

--

[DrJJWMac]

Version 1.1 is attached with following notes:

(*
		version 1.1 (JJW)
			-- added ability to hide tasks starting later
			-- added ability to show only Next Actions
			-- changed to booleans for flags
			-- increment count of tasks in all loops
*)

This can give an equivalent output to the perspective settings shown below.

--
JJW

FWIW a lazy implementation of sorting in Applescript might look like this:

on sort(my_list)
	set {dlm, my text item delimiters} to {my text item delimiters, linefeed}
	set lstSorted to paragraphs of (do shell script "echo " & quoted form of (my_list as text) & " | sort")
	set my text item delimiters to dlm
	lstSorted
end sort

(you could append the -r switch for a reversed sort)

--

i.e.

set lstReversed to paragraphs of (do shell script "echo " & quoted form of (my_list as text) & " | sort -r")

[DrJJWMac]

Originally Posted by RobTrew

FWIW a lazy implementation of sorting in Applescript might look like this: ...

Thank you. That's not really lazy IMO, actually it is rather clever to use a shell sort. I figured it was possible somehow, I just cobbled in what I found as the first reasonable result after a Web search for Applescript sorting routines. This is also useful to see how to capture returns from shell commands.

--
JJW

You can do simple formatting (Markdown bulleted lists with headers and sub-headers, for example) by further piping through awk from Applescript

property pstrSQL : quoted form of "
SELECT c.name, p.name,  tt.name 
FROM ((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt 
left join task p on tt.task=p.persistentIdentifier) 
left join context c on tt.context = c.persistentIdentifier
WHERE tt.flagged=1 and tt.dateCompleted is null
ORDER BY c.name, p.name"

tell application "Finder" to set strDBPath to "~/Library/Caches/" & (id of application file id "OFOC") & "/OmniFocusDatabase2"
set strReport to (do shell script "sqlite3 " & strDBPath & space & pstrSQL & " | tr '|' '\\t' | awk '
BEGIN {FS=\"\\t\"; ctx=0; prj=0} # Initialise File Separator and header variables
{
# Output an MD header whenever 1st col value [CONTEXT] changes,
	if (ctx!=$1)  {ctx=$1; if (length(ctx) < 1) {print \"\\n##(no context)\"} 
	else {print \"\\n##\" ctx}}; 
	
# and output an MD bulleted project name whenever 2nd  col value [PROJECT] changes,	 
	if (prj!=$2)  {prj=$2; if (length(prj) < 1) {print \"\\n* Inbox\"} 
	else {print \"\\n* \" prj}};  
		
# and output column 3 [TASK] (with an MD sub-bullet) if it differs from col 2
	if ($3!=$2) {print \"\\t* \" $3} 
}'")

--

Or simple text formatting from a pure shell script, for NerdTool, GeekTool, Terminal.app etc, without needing OmniFocus to be running.

#!/bin/sh
#OFOC=$(osascript -e 'tell application "Finder" to get id of application file id "OFOC"')
OFOC="com.omnigroup.OmniFocus" # For Appstore-purchased variant use (slower) line above instead
echo -e "FLAGGED AND NOT COMPLETED:\n"
sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2 '
SELECT c.name, p.name, tt.name 
FROM ((task t left join projectinfo pi on t.containingprojectinfo=pi.pk) tt 
left join task p on tt.task=p.persistentIdentifier) 
left join context c on tt.context = c.persistentIdentifier
WHERE tt.flagged=1 and tt.dateCompleted is null 
ORDER BY c.name, p.name' | awk '
BEGIN {FS="\|"; prj=0; ctx=0;}
{
	# Output a [CONTEXT] header whenever 1st col value changes,
		if (ctx!=$1)  {ctx=$1; if (length(ctx) < 1) {print "(no context)"} 
		else {print "[" ctx "]"}}; 
	# and output a project name whenever 2nd  col value [PROJECT] changes,	 
		if (prj!=$2)  {prj=$2; if (length(prj) < 1) {print "\t(Inbox)"} 
		else {print "\t" prj}};  
	# and output column 3 [TASK] if it differs from col 2
		if ($3!=$2) {print "\t• " $3}
}'