The Omni Group Forums

The Omni Group Forums (http://forums.omnigroup.com/index.php)
-   OmniFocus Extras (http://forums.omnigroup.com/forumdisplay.php?f=44)
-   -   Script for a quick listing of OmniFocus statistics (http://forums.omnigroup.com/showthread.php?t=18405)

RobTrew 2010-10-07 02:52 PM

Script for a quick listing of OmniFocus statistics
 
There are various scripts on this forum which do a good job of listing OF statistics (total numbers for projects, tasks, contexts etc, plus numbers of those with a particular status like completed, on-hold etc.)

The only merit of [URL="http://www.complexpoint.macmate.me/Site/Quick_Stats_for_OmniFocus.html"]this script[/URL] is that it is unusually fast.
(It also works when OmniFocus is not running - perhaps one can imagine circumstances in which that might be useful :-)

It works well with OF 1.8, but its speed depends on a short-cut which may prevent it from working with later versions of OF, when they come. (The schema of the OF cache is liable to be adjusted between builds, so this script may also stop working, and need to be updated, next time there is an update of OmniFocus).

If this happens, the script will simply suggest that you look for an update on this forum.

(attached as a zip below - can be [URL="http://forums.omnigroup.com/showthread.php?t=7453"]installed[/URL] on the OF toolbar, or run on its own).

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

RobTrew 2011-01-17 04:52 AM

Reorganized the list, and added some new counts, for a break-down of Active projects and Remaining actions.

POSTSCRIPT [See ver 1.7 in later post]

[IMG]http://farm6.static.flickr.com/5089/5363829486_91e1cacafe_o.jpg[/IMG]

kingsinger 2011-01-31 05:53 PM

I just tried using this script and I got a dialog box indicating that sql schema may have changed and that I should check for a new version. Does that seem possible? Or did I not install it right? I didn't put it on my tool bar. I just actuated it from the applescript menu.

RobTrew 2011-01-31 09:11 PM

[QUOTE=kingsinger;92542]I just tried using this script and I got a dialog box indicating that sql schema may have changed and that I should check for a new version. Does that seem possible? Or did I not install it right? I didn't put it on my tool bar. I just actuated it from the applescript menu.[/QUOTE]

The OF SQL schema can change between builds, but it certainly should work if you are running OF 1.8.2. It might well fail if you were running an earlier version of OF.

The form of installation should make no difference.
The script does, however, assume that your OF cache is located at
~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2

(Where ~ is your own user directory).

The quickest way to find out what is going on is probably to make a slight edit to the script (below), run it again, and see what particular error message it generates.

Try pasting this version into your applescript editor, running it, and reporting the error message that it gives. (A copy of the error message will also be placed in your clipboard).

[CODE]property pTitle : "OmniFocus: Quick Stats"
property pVersion : "TEST"

-- Ver 0.8 adds clipboard option to dialogue
-- Ver 0.9 gives an error message if the cache schema has changed, leading to an SQL error in the script
-- Ver 1.0 slight simplification of the code
-- Ver 1.1 added count of Pending projects
-- Ver 1.2 added a count of available actions
-- Ver 1.3 added a break-down of unavailable actions
-- Ver 1.4 added count of Current projects to complement Pending projects
-- ver 1.5 replaced Applescript time function with SQL time expression

property pToClipboard : "Copy list to clipboard"
property pstrCmd : "sqlite3 -separator ': ' ~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2 " & quoted form of ("
select \"ALL FOLDERS\" , count(*) from folder;
select \" Active folders\", count(*) from folder where effectiveActive=1;
select \" Dropped folders\", count(*) from folder where effectiveActive=0;
select null;
select \"ALL PROJECTS\", count(*) from projectInfo where containsSingletonActions=0;
select \" Active projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"active\");
select \" Current projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart is null or t.dateToStart < (strftime('%s','now') - strftime('%s','2001-01-01')));
select \" Pending projects\", count(*) from projectInfo p join task t on t.projectinfo=p.pk where (p.containsSingletonActions=0) and (p.folderEffectiveActive=1) and (p.status=\"active\") and (t.dateToStart > (strftime('%s','now') - strftime('%s','2001-01-01')));
select \" On-hold projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"inactive\");
select \" Completed projects\", count(*) from projectInfo where (containsSingletonActions=0) and (status=\"done\");
select \" Dropped projects\", count(*) from projectInfo where (containsSingletonActions=0) and ( status=\"dropped\");
select null;
select \"ALL SINGLE ACTION LISTS\", count(*) from projectInfo where containsSingletonActions=1;
select \" Active single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"active\");
select \" On-hold single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"inactive\");
select \" Completed single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and (status=\"done\");
select \" Dropped single action lists\", count(*) from projectInfo where (containsSingletonActions=1) and ( status=\"dropped\");
select null;
select \"ALL CONTEXTS\", count(*) from context;
select \" Active contexts\", count(*) from context where (effectiveActive=1) and (allowsNextAction=1);
select \" On-hold contexts\", count(*) from context where (effectiveActive=1) and allowsNextAction=0;
select \" Dropped contexts\", count(*) from context where effectiveActive=0;
select null;
select \"ALL ACTION GROUPS\", count(*) from task where (projectinfo is null) and (childrenCount>0);
select \" Remaining action groups\", count(*) from task where (projectinfo is null) and (dateCompleted is null) and (childrenCount>0);
select \" Completed action groups\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount>0);
select null;
select \"ALL ACTIONS\", count(*) from task where (projectinfo is null) and (childrenCount=0);
select \" Completed actions\", count(dateCompleted) from task where (projectinfo is null) and (childrenCount=0);
select \" Remaining actions\", count(*) from task where (projectinfo is null) and (dateCompleted is null) and (childrenCount=0);
select \" Available actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.blocked=0) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
select \" Actions awaiting start date\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=1) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
select \" Sequentially blocked actions\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.blocked=1) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
select \" Actions of inactive projects\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.effectiveContainingProjectInfoActive=0) and (t.blockedByFutureStartDate=0) and ((t.context is null) or ((c.allowsNextAction=1) and (c.active=1)));
select \" Actions of contexts which are on hold\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and (c.allowsNextAction=0);
select \" Actions of contexts which are dropped\", count(*) from (task t left join context c on t.context=c.persistentIdentifier) where (t.projectInfo is null) and (t.childrenCount=0) and (t.dateCompleted is null) and (t.effectiveContainingProjectInfoActive=1) and (t.blockedByFutureStartDate=0) and (c.active=0);
select null;
select \"ALL INBOX GROUPS & ACTIONS\", count(*) from task where (inInbox=1);
select \" Inbox action groups\", count(*) from task where (inInbox=1) and (childrenCount>0);
select \" Inbox actions\", count(*) from task where (inInbox=1) and (childrenCount=0);
select null;
")

try
set strList to do shell script pstrCmd
on error strMsg
tell application id "com.omnigroup.omnifocus"
set strMsg to strMsg & return & return & version & tab & build number
end tell
display dialog strMsg buttons {"OK"} with title pTitle & "Ver. " & pVersion
set the clipboard to strMsg
return
end try

activate
if button returned of (display dialog strList buttons {pToClipboard, "OK"} with title pTitle & " Ver. " & pVersion)
is pToClipboard then tell application id "com.apple.finder" to set the clipboard to strList



[/CODE]

kingsinger 2011-02-01 09:22 AM

Here's what I got back.

SQL error: no such column: c.active

1.8.2 77.71.0.141655

RobTrew 2011-02-01 09:41 AM

[QUOTE=kingsinger;92571]Here's what I got back.

SQL error: no such column: c.active

1.8.2 77.71.0.141655[/QUOTE]

Well, that's a puzzle :-)

The [I]Context[/I] table certainly should have a field called [I]Active [/I]in that build ...

I assume you do have some contexts ?

Has anyone else seen that ?

I will give it some thought, but nothing jumps immediately to mind.

In the meanwhile, there are some other Applescript-based stats counters on this forum which should fill the gap.

kingsinger 2011-02-02 09:32 PM

[QUOTE=RobTrew;92572]Well, that's a puzzle :-)

The [I]Context[/I] table certainly should have a field called [I]Active [/I]in that build ...

I assume you do have some contexts ?[/QUOTE]

Yes, I have plenty of contexts...

Perhaps it will fix itself the next time I upgrade.


JL

whpalmer4 2011-02-03 10:19 AM

Might be worth doing File->Rebuild Database... and then trying the script again.

mrubenson 2011-03-21 02:15 PM

Same error here:
[I]SQL error: no such column: c.active
1.8.2 77.71.0.141655[/I]

Rebuilding the database did not help.

RobTrew 2011-03-23 09:26 AM

[QUOTE=mrubenson;95008]Same error here:
[I]SQL error: no such column: c.active
1.8.2 77.71.0.141655[/I]Rebuilding the database did not help.[/QUOTE]

Haven't managed to reproduce this error, but it's interesting that there have now been two reports.

If you open the script and take a look at it, you will see that the [I]c.active[/I] field is referred to six times, in the lines which give a breakdown of remaining actions. Short of asking you to send me your database, or an encrypted version of it, there are two solutions:[LIST=1][*](Gordian knot) If you can do without those details, simply delete that block of six lines from the script,[*](221b Baker St) If you are feeling investigative, try eliminating them one at a time, to see if the problem can be narrowed down ...[/LIST]


All times are GMT -8. The time now is 12:16 PM.

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