The Omni Group Forums - View Single Post - Getting a better handle on hyperlinks in notes

RobTrew

Quote:

Originally Posted by -tm-

What I would need in the end is a list containing three pieces of information: a handle to the tasks (like task id = your first answer), the DEVONthink link (=your second answer) and the task status (on hold, dropped, etc.).

Good - I'm glad that that seems to meet the need. Here is a sketch of an approach to getting your three fields for each link, including multiple links in any given note.

(Note that the status options you have mentioned are technically project status options rather than properties of raw tasks. Not sure whether you also need to collected the Completed status of the tasks - task.dateCompleted is null vs task.dateCompleted is not null Thus something like SELECT (dateCompleted is not null) FROM task to get 1 or 0 for Completed or Not Completed).

Code:

property pstrDevLink : "x-devonthink-item://"
property pstrDBPath : "~/Library/Caches/com.omnigroup.OmniFocus/OmniFocusDatabase2"
property pEOR : "<eor>"
property pFldDelim : "<fldelim>"
property plstResults : {}

-- NOTE: Three possible cases:
-- 1. The item is a task with a project from which it inherits its status (strParentStatus)
-- 2. The item is itself a project (ie "root task of a project"), with its own status (strOwnStatus)
-- 3. The item is an (Inbox or other) task which has not been assigned to a project, and both strParentStatus and strStatus are ""

on run
	set strSQL to "
SELECT tp.persistentIdentifier, tp.status, pp.status, CAST(tp.noteXMLData as text), " & quoted form of pEOR & "
FROM (task t left join projectinfo p on t.containingProjectInfo=p.pk) as tp left join projectinfo pp on tp.persistentidentifier=pp.pk  
WHERE CAST(tp.noteXMLdata as text) like \"%" & pstrDevLink & "%\";"
	
	RunSQL(strSQL)
	
	set lst to {}
	repeat with oTask in plstResults
		set text item delimiters to pFldDelim
		set {strID, strParentStatus, strOwnStatus, strXML} to text items of oTask
		set text item delimiters to pstrDevLink
		set lstParts to text items of strXML
		set lngParts to count of lstParts
		if lngParts > 1 then
			repeat with i from 2 to lngParts
				set strStatus to ""
				if strParentStatus is not "" then
					set strStatus to strParentStatus
				else if strOwnStatus is not "" then
					set strStatus to strOwnStatus
				end if
				set end of lst to {strID, strStatus, pstrDevLink & text 1 thru 36 of item i of lstParts}
			end repeat
		end if
	end repeat
	set text item delimiters to space
	
	-- Return a list of three-part lists
	return lst
end run

on RunSQL(strSQL)
	set strCmd to "sqlite3 -separator '" & pFldDelim & "' " & pstrDBPath & space & quoted form of strSQL
	set text item delimiters to pFldDelim & pEOR & return -- strip out the end of line \r as well as the record delimiter
	set plstResults to text items 1 thru -2 of ((do shell script strCmd) & return)
end RunSQL

--