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 02: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-29 at 11:55 PM.. 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 09: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
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
This worked great for me in OmniFocus/1.9/GM-v77.75.2.
Thanks for posting this is helpful.
 
I have updated this to Ver 1.7
  • The listing is slightly reorganized for better legibility
  • It now gives a warning that it requires OS X 10.6 (there are some differences in Sqlite SQL syntax between 10.5 and 10.6)
  • I have attempted to make it accessible to copies of OmniFocus purchased through the Mac App Store, which unfortunately have a variant bundle identifier and hence a variant cache path.



The attempt to enable AppStore customers to use it is experimental and untested, as I don't have an AppStore copy myself.

As a user who is not a developer, but has long had the semi-automatic reflex of sharing most of the scripts that I write for my own use, I have to say that attempting to adapt this script to make it equally accessible to App Store customers added real overheads of time and thought which frankly brought no compensating rewards - not even the satisfaction of feeling confident that it will work ...

(a slight feeling, in fact, of clearing up after somebody else's mess :-)

Very different from the situation when we had just one bundle identifier, and, barring OS X version differences, if something worked on my machine then I could feel reasonably confident that I could usefully and simply share it with most other OmniFocus users.

The conclusion I draw from this experiment is that while I am very happy to contribute to a general FAQ on the kinds of coding adjustments that AppStore customers will have to make for themselves, I don't feel that I can really justify the additional overhead of time and thought required to regularly attempt to write my scripts in ways that do their best to detect which version is being used, and add a related error-handling apparatus ...

Bottom line ... if you buy Omni products through the AppStore, the variant bundle identifiers which it bestows may be a source of some inconvenience ...

--

Last edited by RobTrew; 2011-06-30 at 10:16 AM..
 
I think I have looked through all or most of these posts about OF statistics. They are really great and I do appreciate all the work that goes into them and helping people like myself.

What I am looking for is something that will tell me how many projects in a particular folder or simply in the selection.

The closest I came to that was the one that counted the estimated times of selected projects (sorry I don't have a link for that right now). I tried that but if there is no estimated times entered then it won't work. I would love to have a go at this but time and lack of knowledge of apple script are working against me.

Also I have noticed that most of these posts are fairly old. That either means that most people are happy with it or not interested any more?
 
 


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 06:44 AM
Script: listing tasks by their creation date RobTrew OmniFocus Extras 0 2011-07-26 01:29 AM
OmniFocus AppleScript for Statistics Creation (for download) digitalimago OmniFocus Extras 30 2011-01-12 02:06 PM
Help with Entourage->OF quick entry script magnum6 OmniFocus Extras 4 2007-12-14 08:50 AM
OmniFocus crash statistics Ken Case OmniFocus 1 for Mac 0 2007-07-25 03:05 PM


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


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