I use DT3 to collect all my expense and income receipts using different databases for private and real estate/tenament receipts as OCRed PDFs. For tax purposes, I need all the receipts with dates and amounts in separates tables. After some experimenting, I came up with this JavaScript script. It can be run from the script editor, the command line or from within DT3.
Obviously, the script relies on some assumptions like the availability of a custom meta data field with the amount for each receipt. I tried to annotate all parts where modifications might be necessary.
Nevertheless, it might be useful for someone. If only to see how some DT3 tasks can be solved with JavaScript.
/*
Collect income and expenses for one year in separate sheets
Conditions: documents are PDF
document names contain the date in yyyy-mm-dd
income receipts are contained in group "Einnahmen"
expense receipts are contained in group "Ausgaben"
uses custom meta data "Betrag" (aka amount) to save the income/expenses amount in the sheet as well
The year is entered by the user in a dialog
The script loops over all databases and creates one sheet per database/income and database/expense documents
Possible modifications are marked with ###MOD
*/
(() => {
var app = Application('DEVONthink 3');
app.includeStandardAdditions = true;
var yearDialog = app.displayDialog('Jahr', { defaultAnswer: "2020" }); /* ###MOD language */
var year = Number(yearDialog.textReturned)
if (year < 2019 || year > 2050) { /* ###MOD range */
/* check if the year is in the required range */
app.displayAlert("Jahr liegt zu weit in der Vergangenheit oder Zukunft."); /* ###MOD language */
exit
}
var yearString = year + "-";
var dateRE = /\d{4}-\d\d-\d\d/;
var databases = app.databases();
databases.forEach(db => { /* loop over all databases */
["Ausgaben", "Einnahmen"].forEach(typ => { /* ###MOD name of income/expenses groups */
var group = app.search("name:" + typ + " kind:group", {in: db.root()} );
if (group.length === 0) {
return;
}
var result = app.search("name:~" + yearString + " kind:pdf", {in: group[0]}); /* ###MOD accepts only PDF documents */
if (result.length === 0) {
return;
}
var sheet = app.createRecordWith({name: db.name() + ": " + typ + " " + year,
type: "sheet", tags: "Auswertung", creation: new Date(),
columns:["Datum","Name", "Tags", "Betrag"], /* ###MOD names and number of columns */
});
var myCells = [];
result.sort((a,b) => { /* Sort records by ascending date */
var datea = a.name().match(dateRE)[0];
var dateb = b.name().match(dateRE)[0];
return datea > dateb;
}).forEach(rec => {
/* ###MOD Data to collect, possibly name of custom meta data field */
var betrag = app.getCustomMetaData({from: rec, for: "Betrag"});
var tags = rec.tags().filter(t => t !== typ); /* ignore tags "Einnahmen"/"Ausgaben" */
/* Get the date from the record name and format it */
var dateMatch = rec.name().match(dateRE);
var dateFormatted = dateMatch[0].split('-').reverse().join("."); /* ###MOD uses German date format dd.mm.yyyy */
/* Add row to local array */
myCells.push([dateFormatted,rec.name(), tags.join(","), betrag]); /* ###MOD fields for sheet row */
})
/* Add all rows to sheet */
sheet.cells = myCells;
}) /* forEach Einnahmen/Ausgaben */
}) /* forEach databases */
})();