[QUOTE=codefreespirit;106771]I'd love to see the number of days till the action is due.[/QUOTE]

You could use, or refine, a custom relativedays() function in a bash (awk) script:

[CODE]#!/bin/sh
#Ver 0.04
# Time-zone handling adjusted
# Works with Vanilla and AppStore OF
# Exclusion of completed tasks corrected
OFOC="com.omnigroup.OmniFocus"
if [ ! -d "$HOME/Library/Caches/$OFOC" ]; then OFOC=$OFOC.MacAppStore; fi
OFQUERY="sqlite3 $HOME/Library/Caches/$OFOC/OmniFocusDatabase2"

NOW=$(date +%s) # the date command automatically allows for daylight savings like BST in the UK
#TODAY=$(date -v0H -v0M -v0S +%s) #Midnight at the start of today: set the time component to 00:00

ZONERESET=$(date +%z | awk '
{if (substr($1,1,1)!="+") {printf "+"} else {printf "-"} print substr($1,2,4)}')
YEARZERO=$(date -j -f "%Y-%m-%d %H:%M:%S %z" "2001-01-01 0:0:0 $ZONERESET" "+%s")
DUE="($YEARZERO + t.effectiveDateDue)";
DAY=86400
DAYS_LEFT="round((($DUE-$NOW)*1.0)/$DAY,1)"
DAYS_OVERDUE="($NOW-$DUE)/$DAY"

JOIN="(((task tt left join projectinfo pi on tt.containingprojectinfo=pi.pk) t
left join task p on t.task=p.persistentIdentifier)
left join context c on t.context = c.persistentIdentifier)
left join folder f on t.folder=f.persistentIdentifier"

MATCHES="status='active' and t.dateCompleted is null and t.isDueSoon=1 and $DAYS_OVERDUE < 14"
TOTAL=$($OFQUERY "SELECT count(*) FROM $JOIN WHERE $MATCHES")

echo "DUE SOON OR IN LAST FORTNIGHT ($TOTAL tasks)"
$OFQUERY "
SELECT f.name, p.name, c.name, t.name, $DAYS_LEFT, strftime('%m-%d %H:%M',$DUE, 'unixepoch')
FROM $JOIN
WHERE $MATCHES
ORDER BY f.name, p.name, c.name, t.effectiveDateDue
" | awk '
function relativedays(days) {
if (days<0) {return "("(-days) " days ago)"}
else {return "("days " days left)"}
}
BEGIN {FS="\|"; fld=0; prj=0; ctx=0;}
{
# Whenever the value of col. 1 changes, write it out as a FOLDER header,
if ( fld!=$1) { fld=$1; if (length( fld) < 1) {print "\n(No folder)"}
else {print "\n(Folder: " toupper( fld) ")"}};

# and whenever the value of col. 2 changes, write it out as a PROJECT header.
if (prj!=$2) {prj=$2; if (length(prj) < 1) {print "\nInbox:"}
else {print "\n" prj ":"}};

# Whenever the value of col. 3 changes, write it out as a [CONTEXT] sub-header.
if (ctx!=$3) {ctx=$3; if (length(ctx) > 0) {print "[" ctx "]"}};

# and whenever col 4. contains sth other than the project name, write it out as a • TASK.
if (length($4) > 0 && $4!=$2) {print "• " $4, relativedays($5), $6}
}'[/CODE]

Excellent!
 

Just wow, Rob! Thank you so much. You outdid yourself.

I'm running into one problem though. It shows all tasks even if they are completed.

I tried a couple of things (guessing that MATCHES= is supposed to set the matching criteria for the tasks displayed).

MATCHES="(t.isDueSoon=1 + t.DateCompleted=0) and $DAYS_OVERDUE < 14"

Also tried:

MATCHES="t.isDueSoon=1 and $DAYS_OVERDUE < 14 and t.DateCompleted=0"

Both caused all output to vanish.

Add: I tried t.DateCompleted=null, but that didn't work either.

Thanks !

You should be able to test on ([I]t.dateCompleted is null[/I]) vs ([I]t.dateCompleted is not null[/I])

I'll take a look tomorrow and correct it.

(PS glancing at it now it looks as if there's also scope for a daylight savings adjustment, and for making it more easily accessible to Appstore-purchased versions of OF)

[COLOR="White"]--[/COLOR]

[QUOTE=RobTrew;113923]I'll take a look tomorrow and correct it.

(PS glancing at it now it looks as if there's also scope for a daylight savings adjustment, and for making it more easily accessible to Appstore-purchased versions of OF)[/QUOTE]

Version 0.02 now in [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]post 61[/URL] above.

[CODE]#Ver 0.02
# Works with Vanilla and AppStore OF
# Adjusts for any daylight savings
# Exclusion of completed tasks corrected[/CODE]

You're amazing, Rob. Thank you so much for all your help and hard scripting work. It's appreciated.

Rob, I love the fact that you posted these shell scripts. This is one of my favorite threads. So, thanks a ton, they are useful to me on a daily basis.

I always had trouble with AppleScript geeklets not showing up when using them in conjunction with OmniFocus. The shell scripts fix that.

The only thing I'm missing is some way to display remaining flagged tasks. I tried to make the folders shell script to work with perspectives, but I failed miserably since I've been into shell scripting for only a month.

[QUOTE=pattulus;114431]The only thing I'm missing is some way to display remaining flagged tasks[/QUOTE]


Should be something like:
[CODE]select [I][field list][/I] from task where [B]effectiveFlagged=1 and dateCompleted is null[/B][/CODE]

[QUOTE=RobTrew;114436]Should be something like:
[CODE]select [I][field list][/I] from task where [B]effectiveFlagged=1 and dateCompleted is null[/B][/CODE][/QUOTE]

Thanks. That wasn't the (shell) script I referred to, but it works splendid with your pack from the 1st post.

I hesitated to try it out at first because my other Geeklets for OmniFocus which also use AppleScript never showed up the information after l log into my Mac. At least unless OmniFocus was loaded on startup or via the OS X resume feature. I always had to open Geektool and click on the empty shell containers and the text popped up.

[B]However[/B], with your script it does work without all that hassle.

One tiny question to the audience here:

[CODE]osascript ~/GeekOFSQL.applescript 'task where flagged=1 and dateCompleted is null order by projectinfo, dateDue --header=Flagged ($N)\n' | fold -s -w 120 | perl -pe 's/(^[^•]+)/\t\1/' | perl -pe 's/•/-/g' [/CODE]

Is it possible to get an output like "==== Flagged (4) ===="?

I have equal signs around all my other lists and I've googled some Linux boards and people suggest that I put the equal signs in quotes. I've tried all kind of quotes, inserting Hex or decimal values for the '=' it just won't work.

[QUOTE=pattulus;114471]
Is it possible to get an output like "==== Flagged (4) ===="?
[/QUOTE]

I've come up with a very simple workaround: I use the ≡ sign which just looks like the equal sign with the font-size I use for my geeklets. I'm happy.

[QUOTE=RobTrew;113933]Version 0.02 now in [URL="http://forums.omnigroup.com/showpost.php?p=113374&postcount=61"]post 61[/URL] above.

[CODE]#Ver 0.02
# Works with Vanilla and AppStore OF
# Adjusts for any daylight savings
# Exclusion of completed tasks corrected[/CODE][/QUOTE]

I'm happily using Ver 0.03 of the script. Completed tasks are now excluded, but only if they are not in the specified timeframe (last fortnight): [CODE]$DAYS_OVERDUE < 14[/CODE].

So at the moment every completed item of the last 14 days still shows up. I've tried some (amateurish) things, but couldn't get it to also exclude the last 14 days completed items.