Hello to all,
So I need simple database-like features in DEVONthink, to get the EDM (document-focused) intelligence of DEVONthink at the same time as the normalization of database systems.
As some of you on this forum may know — I explained it in another post [url]Testimony: DTPO used for an artist's official committee] — I already handle the links using replicated groups in a subgroup, which is not perfect, but it works, and it is the easiest way to create a two-way connection in a single operation without a script (the linked child objects i.e. groups can be seen in the group browser, and the parent objects i.e. groups can be seen in the replicates list in the group’s Info panel).
Aside discussion: I’ve just realized a UI limitation of this setup: when using recursive linking (an object type e.g. books can have children e.g. exhibitions the book refers to, and each child e.g. exhibitions can have children as well e.g. artworks) the replicate list in the grandchild group’s Info panel becomes unreadable (way too long) because you do now only get the replicate with one parent level, it is multiplied by the number of grandparents if you see what I mean. In my example, you will not only see all the exhibitions that feature a specific artwork, but also all the books that refer to the all the exhibitions that feature the artwork. I may decide to write some scripts to handle two-way scripts in a different way later (it seems my only choice is links in formatted notes, because sheets unfortunately do not support it, unless you double-click/select a UUID link, right-click and select Go to URL) but let us get back to the topic at hand.
@DEVONthink team: do you have any suggestion? Would you consider two-way links between groups as a feature?
Links being taken care of, I also need to handle properties for each “object” (group, it can be a book or artwork description for instance), like in a database table.
In order to make it easy to maintain in a standardized way, I chose to create my “forms” with sheets, basically a “property-value” system.
It will make my job easier when I want to extract all the forms I have. If I had chosen text notes, field ordering modification would have difficult, data extraction would have required parsing, and typing mistakes in the field names would have made this task tedious. I could have chosen Excel, it was a tough call, but I thought that scripting in DEVONthink would not have been possible, and that it would be hard to define a fixed template location that would be maintained on different computers with synchronized DEVONthink databases, so I stuck with sheets.
But, sheets as they are provided in DEVONthink are not even close to having database-like features. For instance, there is no way to cancel the column order you have sorted by a column, and you cannot insert a line where you want, so I had to define a sort order column (1st column).
Also, I needed a system where I could start inputting data in my forms, and allow myself to update the form structure later without editing each existing form. Obviously sheets do not have dynamic templates.
So I devised a system where I maintain a template sheet with the structure I want, and if I want to update an existing form after I have added or deleted some fields i.e. lines, or updated some fields (field order, field name, field input examples), I can launch a script that automatically updates everything (and asks for a confirmation in case of a field deletion).
Since I wanted to be able to rename the fields, field names (2nd column) could not be the key, so I created a column (6th column) where I simply need to enter in the template any unique value such as the date plus a sequence number (e.g. 160119-01).
Since dropdown list values are not available in sheets (such as in Excel) I also added a column (5th column) for examples on the expected format (e.g. for a date: 1957-04-28; or the list of possible values for a book type: magazine, book, etc.).
The remaining columns are the value (3rd column) and the user comment (4rd column). Tip: for inputting multiple values into a field, you can use Alt+Return to enter a line break. After exiting the cell, the line will resize itself.
Also, in order to prevent inputting data into the template sheet by mistake by replicating it instead of duplicating it, I created a simple script to create a new form.
I’ve attached to this post my two scripts. In order to work, you will need to adapt its content to your needs, most importantly the UUID of the template form, and translate the strings (currently in French). When creating the template form sheet, don’t forget to sort by the first column, so duplicated forms will have that setting by default. The 6 columns setup should be sufficient for the general use case: order (defined by template) / property name (defined by template) / property value (user input) / comment (user input) / examples (defined by template) / ID (defined by template, should be unique and never be changed). The scripts have icons so that you can put them into your Toolbar scripts folder and add them to your toolbar.
I have not yet found a way (or rather found the time to find a way) to update a list of selected forms (for instance after opening a smart group that displays all forms to make sure that all are up-to-date), so for now the script should be launched manually before updating a form.
I hope this will help or inspire some of you, fellow DEVONthink lovers.
Best regards,
Edouard
Scripts.zip (35.7 KB)