OF2 change database schema? Impact Rob Trew apple scripts


#1

I have several Rob Trew apple scripts which need to be redirected to my OF2 db.
I believe I did that successfully, but I keep errors like the one below for a script “Stats” which shows DB stats (e.g. projects open, deferred, completed, etc,)

“Finder got an error: Error: no such table: task”

I am not particularly strong on SQL or scripts, but it appears like its telling me that table “task” does not exist any more.
Did the OF2 change database schema?
Anyone else see this or find a solution?

Many thanks.


How to update Rob Trew's DEVONthink scripts for OF2?
Any OF2 scripts to count tasks and projects?
OFStats with OF2?
Looking for OFQuickStats or Similar Statics Info
#2

It would be easier to help sort this out if you could post a particular script or link to a script so I can see what the script is doing.


#3

Lizard, Here is the “Stats” apple script which worked perfectly in OF1. Any insight appreciated. Thanks, T


property pTitle : "OmniFocus: Quick Stats"
property pVersion : "2.01"

property pstrDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus2.MacAppStore/Data/Library/Application Support/OmniFocus/Omnifocus"
property pstrMinOSX : "10.6"

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 (version < pstrMinOSX) then
		display dialog "This script requires OSX " & pstrMinOSX & " or higher" buttons {"OK"} default button 1 with title pTitle & "Ver. " & pVersion
		return
	end if
	
	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

#4

Lizard,

I know y;all up up to your ears in feedback, but did you have a chance to figure out what needs to be changed in this script to transition from OF1 to OF2?

Many thanks


#5

I would also like to see this script successfully updated. Thanks Thart for posting to the forum even though it doesn’t seem like you have been getting much love.


#6

The sqlite cache moved to a new location, but its format shouldn’t have changed, and the queries in this script still work once you have the correct path.

In case you care, whoever told you this is the original script was mistaken, because it references “OmniFocus2” in the pstrDBPath. (I didn’t check whether there are any other differences from the original.)

However, if you change that line to point at the sqlite cache (as the original script did) instead of the OmniFocus file, it works just fine:

property pstrDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus2/Data/Library/Caches/com.omnigroup.OmniFocus2/OmniFocusDatabase2"

I don’t have a Mac App Store build handy to test, but in that case the script might need to be something like

property pstrDBPath : "$HOME/Library/Containers/com.omnigroup.OmniFocus2.MacAppStore/Data/Library/Caches/com.omnigroup.OmniFocus2/OmniFocusDatabase2"

but you can check for yourself just by browsing to that directory in Finder and checking what its name is.


#7

Lizard, That works like a charm. Thank you for the follow up. Regards, Todd


#8

Here is the updated script for OF2 from the MacAppStore

property pTitle : “OmniFocus: Quick Stats”
property pVersion : “2.01”

property pstrDBPath : “$HOME/Library/Containers/com.omnigroup.OmniFocus2.MacAppStore/Data/Library/Caches/com.omnigroup.OmniFocus2/OmniFocusDatabase2”
property pstrMinOSX : “10.6”

– 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 2014.0623/1

property pToClipboard : “Copy list to clipboard”

if not FileExists(pstrDBPath) then set pstrDBPath to GetCachePath()

tell application “Finder”
if (version < pstrMinOSX) then
display dialog “This script requires OSX " & pstrMinOSX & " or higher” buttons {“OK”} default button 1 with title pTitle & "Ver. " & pVersion
return
end if

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/Containers/com.omnigroup.OmniFocus2.MacAppStore/Data/Library/Caches/” & its id & “/OmniFocusDatabase2”
on error
error “OmniFocus not installed …”
end try
end GetCachePath


#9

Thanks Lizard. This worked perfectly and it is the script that I remember from OF 1. I don’t have the app store version, so I used the first version. It was an easy fix in the apple script editor.

Thanks to thart for highlighting this script.


#10

Does anyone have OFStats (or anything similar) working with OF2?


#11

Yes. Works fine here.

There are other threads about this on this forum, like this one where you can find the solution ;-)


#12

Search fail on my part, so thank you for pointing me here.

I don’t have the app store version either but I also used the first version successfully. Great to have my stats back!


#13

Thank you to everyone who helped update this thread. I’m an AppleScript novice so I wouldn’t have been able to get this running without the detailed instructions here.

In case anyone runs into the same issue, I did want to mention a problem I ran into. In trying to get this script to run over the last 24 hours, I kept getting an error that this script needs 10.6 to run. I received this error despite the fact I was running 10.10 which is the latest version of OS X. In order to get around this, I changed a single line of code.

For the MacAppStore version which thart was kind enough to post above, I changed the fourth line of code

property pstrMinOSX : "10.6"

to

property pstrMinOSX : "10.10"

and it works now just fine.


#14

I have been searching on the app store but can’t find this script. can someone post the link or better search terms (Omnifocus stats doesn’t’ work and neither does just plain Omnifocus)


#15

The term app is confusing in this instance. You are running a script for version 2 of OmniFocus Pro that was either bought on the Omni Store or the Mac App Store. The script itself is the freely licensed work of others, like Rob Trew. Create a new script using Apple’s built-in Script Editor application. Paste this in, click the hammer icon to compile it, then the play button to run it.

property pTitle : "OmniFocus: Quick Stats"
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

Save the file to your desktop. In OmniFocus, select Help>Open Scripts Folder and move the script file into the Finder window that opens. Then, right click on your OmniFocus toolbar and select Customize Toolbar. You should see your new script somewhere at the bottom of the sheet that appears. Drag it to a place on your toolbar and now you can run the script any time OF is open.


#16

Thanks so much, I was confused as to why scripts were now consider Mac Store apps , thanks!


#17

This is great!

Sorry to relive an old thread but for some reason after copying the last version pasted here it still didn’t work. So below a version that does work on OmniFocus2 (MAS)

https://www.evernote.com/l/AkiRgdIiwE9II4EvOgXk0wsiJ98jCSk4QaU

–Grace


#18

Any chance someone has updated these scripts for OF3?


#19

I’ve managed to run this from OF3 with DTPO2.

What I did:

  1. Renamed OmniFocus2 to OmniFocus3 in the script.
  2. In Containers/… folder I’ve put my old OmniFocus2 database and renamed it to OmniFocus3. Without that. the DB was created with zero size. This is kind of quick workaround…

#20

Thank you. I tried that, but it looks like the numbers reported are not updated. I’m guessing the DB is not being updated.