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

Code:
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} 
}'")
--

Last edited by RobTrew; 2012-08-01 at 12:15 PM.. Reason: Cleaner output for project tasks