Thanks. This is very useful and thank for the note on how to post code.
JXA doesnāt support synonyms unfortunately:
This is doing much better. Everything is getting imported. However, what is coming in appears as Tags in DEVONthink. So Category is not going into the data field I created but is just being a tag. None of the financial information is appearing anywhere. I was expecting a problem like this. None of the Paperless collections info is showing up. But I was expecting this types of problems and need to now look more closely at what is being imported and what is getting set. Collections should be fixable by creating tags in Paperless. And the Categories and Subcategories I can assign if I can figure out how to apple a Category to a bunch of selected records.
And I can probably get more information as tags if I can add their fields from Paperless into the script.
I did hit a problem which I have to blame on Paperless. The name of a PDF does not match the name in Paperless, Paperless passed an incorrect file name so I renamed the PDF. It is from back in 2012 so I can delete that record from Paperless.
In DEVONthink the following data is good: Name, Creation Date, Modified Date (I assume picked up from the PDF), Kind looks good but some are listed as PDF Document while others are PDF+Text. Iām guessing whether or not text is stored in the PDF as opposed to just a scan with no OCR.
So more playing around when I have more time.
Because thatās all that happens in addDocument. This function only creates tags. If you want to populate other fields, like custom metadata, keywords, whatever, you have to provide code to do that.
That can also be caused by the SQL that extracts the data. You might want to take a look at the SQLite database storing your Paperless data and perhaps adjust the SQL used in the script.
All that is probably feasible. But it also illustrates why data silos are a bad idea.
Iāve made some good progress in better understanding the script I am using but now stuck on something else. Thankfully I understand SQL views and figuring out the JavaScript that I need. But Iām hitting a data type error.
When Iām running the AddDocument function I added Category to be passed. This is modified from the script I posted.
addDocument(`${basePath}/${docPath}`, plNotes, plDate, plTags, plTitle, plCategory);
And then when I call this function I also added category to be received.
function addDocument(path, notes, date, tags, title, category) {
const newRecord = app.importPath(path, {to: targetGroup});
newRecord.tags = newRecord.tags().concat(tags);
newRecord.creationDate = date;
newRecord.name = title; /* that's _different_ from the path in DT! */
newRecord.category = category;
And then I run and the log shows an error
app.databases.byId(1).contents.byId(10119).category = āMedicalā
--> Error -1700: Can't convert types.
Iāve tried setting the category metadata in DEVONthink to be single line text, multiline text and rich text all to the same effect. I also tried other fields, such as notes, with the same effect. So I am wondering how to ensure that I am putting the right sort of text into a text field.
category is not a property of a DT record object. Please check the scripting dictionary. And please take the time to explain in detail what youāre trying to do. No one but you can know what you intend category to be.
If category is a user-defined metadata field, I suggest (again) consulting the scripting dictionary. There is a method to add a metadata value to a record.
Also, it would be helpful if you formatted your code samples so that one can see as much as possible without scrolling. A newline before the assignment to newRecord.category wouldāve made the issue evident at once. Iāve added that now.
I think I have a working script that works with custom metadata. Iāll post it after I do extensive testing, meaning importing all of my Paperless data.
I think the only problem I have now is when Paperless has a url for a PDF that is wrong.
Success. I will post my script, based on the Javascript found on this board soon.
Here is my Javascript, which I ran in the Script Editor. Since I am basing it on the work of others I will point to where I made changes.
First is how to handle custom metadata. I was stumped partly because this script is javascript and this is the first time Iāve edited javascript. The app.addCustomMetaData command was the key. It took me a while to find examples. This allowed me to put category and subcategory into their custom metadata fields rather than put them in as tags. I could also retain notes, type, how paid, and financial fields.
Rather than use Paperlessās import data I used the date I entered into the record itself, this was the transaction date.
I used DB Browser for SQLite to explore the databaseās structure.
I added a few fields to the once I selected. In the SELECT command they follow ZRECEIPT.ZPATH as PATH. I had to remember to add commas to the select. I added a couple of LEFT JOIN commands.
I added the sort command ORDER BY IMPORTED so I could better track progress. This proved to be important if a problem caused the script to fail. When I did have a failure after importing a few hundred records I could add a WHERE command to start after the date of the record that failed.
I had to remember to update the number in if (resultColumns.length === 12) { /* basic sanity check */ every time I added more fields to collect data to match the number of fields I was using. I would forget at times and then puzzle over the runtime error.
And to remember to add the new variables to the calling of the addDocument function and then add an equivalent variable to accept the variable being passed to the function itself.
I could not have written this from scratch but I hope people can use this script or take advantage of how I added stuff. This also had 2 minor updates reflecting changes from DEVONthink 3 to version 4.
(() => {
const app = Application("DEVONthink");
const targetGroup = app.databases['Finances'].incomingGroup();
function addDocument(path, notes, date, tags, title, category, subcategory, type, payment, price, tax) {
const newRecord = app.importPath(path, {to: targetGroup});
newRecord.tags = newRecord.tags().concat(tags);
newRecord.creationDate = date;
newRecord.name = title; /* that's _different_ from the path in DT! */
app.addCustomMetaData(notes, {to: newRecord, for: "notes"})
app.addCustomMetaData(category, {to: newRecord, for: "category"});
app.addCustomMetaData(subcategory, {to: newRecord, for: "subcategory"});
app.addCustomMetaData(type, {to: newRecord, for: "type"});
app.addCustomMetaData(payment, {to: newRecord, for: "payment"});
app.addCustomMetaData(price, {to: newRecord, for: "price"});
app.addCustomMetaData(tax, {to: newRecord, for: "tax"});
}
const queryString = `WITH DOC_TAGS AS (
SELECT tags.Z_14RECEIPTS1 as ID, ZTAG.ZNAME as NAME
FROM Z_14TAGS tags
JOIN ZTAG ON Z_PK = tags.Z_18TAGS
),
GROUPED_DOC_TAGS AS (
SELECT DOC_TAGS.ID, GROUP_CONCAT( DOC_TAGS.NAME, '<<[]>>' ) as TAGS
FROM DOC_TAGS
GROUP BY ID
)
SELECT ZRECEIPT.Z_PK as ID,
DATETIME(ZRECEIPT.ZDATE + 978307200, 'unixepoch') as IMPORTED,
ZRECEIPT.ZMERCHANT as TITLE,
ZCATEGORY.ZNAME as CATEGORY,
ZSUBCATEGORY.ZNAME as SUBCATEGORY,
GROUPED_DOC_TAGS.TAGS,
ZRECEIPT.ZNOTES as NOTES,
ZRECEIPT.ZPATH as PATH,
ZDATATYPE.ZNAME as DTYPE,
ZPAYMENTMETHOD.ZNAME as PAYMENT,
ZRECEIPT.ZAMOUNT as AMOUNT,
ZRECEIPT.ZTAXAMOUNT as TAX
FROM ZRECEIPT
LEFT JOIN GROUPED_DOC_TAGS ON GROUPED_DOC_TAGS.ID = ZRECEIPT.Z_PK
LEFT JOIN ZCATEGORY ON ZRECEIPT.ZCATEGORY = ZCATEGORY.Z_PK
LEFT JOIN ZSUBCATEGORY ON ZRECEIPT.ZSUBCATEGORY = ZSUBCATEGORY.Z_PK
LEFT JOIN ZDATATYPE ON ZRECEIPT.ZDATATYPE = ZDATATYPE.Z_PK
LEFT JOIN ZPAYMENTMETHOD ON ZRECEIPT.ZPAYMENTMETHOD = ZPAYMENTMETHOD.Z_PK
ORDER BY IMPORTED
`;
const basePath = '/Users/paulchernoff/Documents/Paperless Backups/Receipt Library.paperless'
const DBPath = `${basePath}/DocumentWallet.documentwalletsql`;
const curApp = Application.currentApplication();
curApp.includeStandardAdditions = true;
const rawDBResult = curApp.doShellScript(`sqlite3 -tabs "${DBPath}" "${queryString}"`, {alteringLineEndings: false});
const resultLines = rawDBResult.split('\n');
resultLines.forEach(r => {
const resultColumns = r.split('\t');
if (resultColumns.length === 12) { /* basic sanity check */
const plDate = new Date(resultColumns[1]);
const plTitle = resultColumns[2];
const plCategory = resultColumns[3];
const plSubCategory = resultColumns[4];
const plTags = resultColumns[5].split('<<[]>>');
const plNotes = resultColumns[6];
const docPath = resultColumns[7];
const plDType = resultColumns[8];
const plPayment = resultColumns[9];
const plAmount = resultColumns[10];
const plTax = resultColumns[11];
addDocument(`${basePath}/${docPath}`, plNotes, plDate, plTags, plTitle, plCategory, plSubCategory, plDType, plPayment, plAmount, plTax);
}
})
})()
1 Like