OmniFocus 3 Statistics

In OF2, I used to collect daily statistics (of my active projects, # of completed tasks, etc.) using a script created by Rob Trew. The script relied on a sqlite cache created by OF, which seems to be gone in OF3.

Are there any similar tools that can provide a snapshot of:

  • of projects (active, on hold)

  • of actions (completed, blocked, active, …)

Any pointers are highly appreciated.

4 Likes

I really need this too. I’ve sent a request to Omni about it asking where the data are so I can try to create a new script that does the same thing.

1 Like

I wrote in to Support and got this script back

property pVersion : "2.01"

property pstrDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus2/Data/Library/Caches/com.omnigroup.OmniFocus2/OmniFocusDatabase2"
property pblnToClipboard : true
property pblnSubTreeCounts : true
property pToClipboard : "Copy list to clipboard"


-- 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
-- Ver 1.7 Reorganizes menu, and attempts to enable access for .macappstore installations of OF
--Ver 1.8 Adjusts handling of variant bundle identifiers generally
-- Ver 2.00 Redraft of task breakdown, with progressive narrowing of criteria ...
-- Ver 2.01 Change for OF2.  pstrDBPath reset


tell application "Finder"
	if pstrDBPath ≠ "" then
		set strCmd to "sqlite3 -separator ': ' \"" & pstrDBPath & "\" " & quoted form of ("
	select \"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;
	select \"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 \"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\") or (folderEffectiveActive=0));
	select null;	
	select \"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\") or (folderEffectiveActive=0));
	select null;
	select \"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 \"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 \"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 \"    Dropped project actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp where (projectinfo is null) and (childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is not null and (tp.status=\"dropped\" or tp.folderEffectiveActive=0));
	select \"    Dropped context actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and c.effectiveActive= 0;
	select \"    Remaining actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1);
	select null;
	select \"        Actions in Projects on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is not null and tp.status=\"inactive\");
	select \"        Actions in Contexts on hold\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is not null and c.allowsNextAction=0);
	select null;
	select \"        Blocked actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1;
	select \"        	Sequentially blocked\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1
				and tp.blockedByFutureStartDate=0;
	select \"        	Awaiting start date\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=1
				and tp.blockedByFutureStartDate=1;				
	select null;			
	select \"        Available actions\", count(*) from (task t left join projectinfo p on t.containingProjectinfo=p.pk) tp left join context c on tp.context=c.persistentIdentifier where (projectinfo is null) and (tp.childrenCount=0)  and (dateCompleted is null) 
				and (tp.containingProjectinfo is null or (tp.status !=\"dropped\" and tp.folderEffectiveActive=1))
				and (tp.context is null or c.effectiveActive= 1)
				and (tp.containingProjectInfo is null or tp.status!=\"inactive\")
				and (tp.context is null or c.allowsNextAction=1)
				and tp.blocked=0;

	")
		
		-- 		try
		
		set strList to do shell script strCmd
		-- 		on error
		-- 			display dialog "The SQL schema for the OmniFocus cache may have changed in a recent update of OF." & return & return & ¬
		-- 				"Look on the OmniFocus user forums for an updated version of this script." buttons {"OK"} with title pTitle & "Ver. " & pVersion
		-- 			return
		-- 		end try
		
		activate
		if button returned of (display dialog strList buttons {pToClipboard, "OK"} default button "OK" with title pTitle & " Ver. " & pVersion) ¬
			is pToClipboard then tell application id "com.apple.finder" to set the clipboard to strList
	else
		display dialog "OmniFocus cache not found ..." buttons {"OK"} default button 1 with title pTitle & " Ver. " & pVersion
	end if
end tell

on FileExists(strPath)
	(do shell script "test -e " & strPath & " ; echo $?") = "0"
end FileExists

on GetCachePath()
	try
		tell application "Finder" to tell (application file id "OFOC") to "$HOME/Library/Caches/" & its id & "/OmniFocusDatabase2"
	on error
		error "OmniFocus not installed ..."
	end try
end GetCachePath

If you click the (pencil) edit icon and repaste that between triple back-ticks

```
  -- code here

```

You may be able to fix the corruption of the code by ‘smart’ replacements for single and double quotes etc, while also preserving indentation.

1 Like