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 > OmniFocus > OmniFocus Extras
FAQ Members List Calendar Search Today's Posts Mark Forums Read

 
Script for a quick listing of OmniFocus statistics Thread Tools Search this Thread Display Modes
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 this script 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 installed on the OF toolbar, or run on its own).

--

Last edited by RobTrew; 2012-10-02 at 03:04 PM.. Reason: Attachment now in following post
 
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]


Last edited by RobTrew; 2011-06-30 at 12:55 AM.. Reason: Ver 1.7 in subsequent post
 
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:
Originally Posted by kingsinger View Post
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.
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

Last edited by RobTrew; 2011-01-31 at 10:23 PM..
 
Here's what I got back.

SQL error: no such column: c.active

1.8.2 77.71.0.141655
 
Quote:
Originally Posted by kingsinger View Post
Here's what I got back.

SQL error: no such column: c.active

1.8.2 77.71.0.141655
Well, that's a puzzle :-)

The Context table certainly should have a field called Active 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.
 
Quote:
Originally Posted by RobTrew View Post
Well, that's a puzzle :-)

The Context table certainly should have a field called Active in that build ...

I assume you do have some contexts ?
Yes, I have plenty of contexts...

Perhaps it will fix itself the next time I upgrade.


JL

Last edited by Brian; 2011-02-03 at 06:22 PM.. Reason: added a missing close-quote tag.
 
Might be worth doing File->Rebuild Database... and then trying the script again.
 
Same error here:
SQL error: no such column: c.active
1.8.2 77.71.0.141655


Rebuilding the database did not help.
 
Quote:
Originally Posted by mrubenson View Post
Same error here:
SQL error: no such column: c.active
1.8.2 77.71.0.141655
Rebuilding the database did not help.
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 c.active 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:
  1. (Gordian knot) If you can do without those details, simply delete that block of six lines from the script,
  2. (221b Baker St) If you are feeling investigative, try eliminating them one at a time, to see if the problem can be narrowed down ...

Last edited by RobTrew; 2011-03-28 at 02:10 PM..
 
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
OmniFocus Statistics skylarkelly OmniFocus Extras 4 2012-02-29 07:44 AM
Script: listing tasks by their creation date RobTrew OmniFocus Extras 0 2011-07-26 02:29 AM
OmniFocus AppleScript for Statistics Creation (for download) digitalimago OmniFocus Extras 30 2011-01-12 03:06 PM
Help with Entourage->OF quick entry script magnum6 OmniFocus Extras 4 2007-12-14 09:50 AM
OmniFocus crash statistics Ken Case OmniFocus 1 for Mac 0 2007-07-25 04:05 PM


All times are GMT -8. The time now is 04:41 PM.


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