I was playing around a bit and came up with a script that
- creates a link from an invoice to the account statement where this invoice was paid
- and vice versa, creates a link from the account statement back to this invoice
It’s not thoroughly tested, but did its job for at least 12 invoices. So, it might work sometimes. And it is, of course, written in JavaScript. Given that it’s fairly long, I didn’t comment it thoroughly – please ask for clarification if needed.
The script works with a selection of invoices. For each of them, it tries to find the corresponding account statement and links the two documents if that was successful. That’s only possible if invoice and statement contain the same string, which should be specific for each invoice. Differences in upper/lowercase spelling can be accommodated by appending i to the regular expression in companies.
To make the script work for your setup, provide the correct data in the companies object. That might require some experimentation to figure out robust strings to identify companies and invoice IDs.
Thanks to @cgrunenberg for pointing out that a PDF must be written after annotating it.
ObjC.import('PDFKit');
ObjC.import('AppKit');
ObjC.import('Foundation');
/* Map characteristic strings in the invoices (e.g. customer no, company name) to an object defining the group storing the account statements and the regular expression to identify the invoice number or code (idRE). Note that this "invoice ID" is stored in the Regular Expression's captuing group. It must exist verbatim in exactly one account statement for the links to happen. */
const companies = {
"Customer: 99999": {group: "/statements/bankA", idRE: /Invoice #(\d+)/},
"Company B": {group: "/statements/bankB", idRE: /Invoice (\d\d\d\d-\d+)/}
};
/* Build a regular expression to identify the "company" in the function getInvoiceID */
const companyRE = new RegExp(`(${Object.keys(companies).join('|')})`);
(() => {
const app = Application("DEVONthink 3");
app.selectedRecords().forEach(r => {
/* Get the company name and the ID to search for */
const [company,id] = getInvoiceID(r.plainText());
if (!company || !id) return; // If either company or ID are not found, do nothing
/* Invoices and account statements are stored in the same database.
If that's not the case, the logic and the search call must be modified */
const db = r.database();
/* Search for the account statement referencing "id" in database "db"
and the group defined for this company */
const searchGroup = app.createLocation(companies[company].group, {in: db});
const foundStatements = app.search(`text:${id}`,{in: searchGroup});
/* Continue only if _exactly_ one account statement is found */
if (foundStatements.length === 1) {
linkInvoiceStatement(r, foundStatements[0], id);
}
})
})()
/* Find the company for the invoice's text and the the invoice ID.
Return company and ID or undefined */
function getInvoiceID(txt) {
const match = txt.match(companyRE);
const company = match ? match[1] : null;
if (company) {
const matchID = txt.match(companies[company].idRE);
return [company, match ? matchID[1] : null];
}
return null;
}
/* Set up the linking between invoice and statement for "string":
get the PDFDocuments from both document paths and
build the URLs for the links from the UUID. Then call createAnnotations
once for each direction and save the PDFs back to disk.
*/
function linkInvoiceStatement(invoice, statement, string) {
const invoicePDF = $.PDFDocument.alloc.initWithURL($.NSURL.fileURLWithPath($(invoice.path())));
const statementPDF = $.PDFDocument.alloc.initWithURL($.NSURL.fileURLWithPath($(statement.path())));
const invoiceURL = $.NSURL.URLWithString(`x-devonthink-item://${invoice.uuid()}`);
const statementURL = $.NSURL.URLWithString(`x-devonthink-item://${statement.uuid()}`);
createAnnotations(invoicePDF, statementURL, string);
invoicePDF.writeToFile($(invoice.path()))
createAnnotations(statementPDF, invoiceURL, string);
statementPDF.writeToFile($(statement.path()))
}
/* Create two annotations in pdf: a green highlight for the string and a link to the url passed in */
function createAnnotations(pdf, url, string) {
const pdfSelections = pdf.findStringWithOptions($(string),null);
if (pdfSelections.js.length === 1) {
const selection = pdfSelections.js[0];
const pdfPage = selection.pages.js[0]; /* PDFPage object, not a number! */
const bounds = selection.boundsForPage(pdfPage);
const highlight = $.PDFAnnotation.alloc.initWithBoundsForTypeWithProperties(bounds, $.PDFAnnotationSubtypeHighlight, {});
highlight.color=$.NSColor.greenColor;
highlight.markupType = 0;
pdfPage.addAnnotation(highlight);
const link = $.PDFAnnotation.alloc.initWithBoundsForTypeWithProperties(bounds, $.PDFAnnotationSubtypeLink, {});
link.URL = url;
pdfPage.addAnnotation(link);
}
}