As a gift to the community, a script to handle DB-like forms

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)

A perhaps more efficient way to create templates than scripting (and share them without hard coding UUIDs) is to use File > Export > As Template and save the document in the location that DEVONthink suggests: ~/Library/Application Support/DEVONthink Pro 2/Templates.noindex. I suggest creating a subfolder there to put your specific templates. Sheets are saved as .tsv. Once you’ve saved your template, use Data > New From Template … to get a fresh instance of that template added to the current group.

Later, you might want to consider reading about templates, smart templates, using placeholders, and template packages in the Help file or the manual. DEVONthink can do some fairly powerful things with these features – especially adding automation. Your update script might be a candidate for a smart template, perhaps.

For linking between groups you might experiment with DEVONthink’s wiki links. For example, if your WikiLinks preferences are set so as to not exclude groups (see below) then you can type the name or one of the aliases of a group in an RTF file and that name or alias becomes a clickable link. If you ⌘-click that name, the group will open in a new window. Though sheets do not recognize these wikiinks, you can convert a sheet to an RTF file and the links are there clickable.

Check your settings at DEVONthink > Preferences > Editing > WikiLinks:

Hope these tips help – not sure about the particulars of your use case. It’s a bit beyond my comprehension :confused:

Thanks a lot korm for your suggestions! However, I had read about the smart templates before and it seems to me that they would not prevent the need for an additional script because once the smart template has been imported, the dynamic fields are set once and for all, whereas my script allows to bring enhancements to existing forms, it seems more powerful to me, have you tried it? It can be used when you want to store standardized information about the documents in a group, and you want to able to enhance the description form later without breaking the existing forms.

Placeholders are currently limited, at least the help files say so, to simple data like the current date or the clipboard, but they cannot get a value parsed from a file and they do not seem dynamic either, their value is calculated at import time.

Finally, your link idea is a good one, even though converting sheets to RTF every time you need to click a link is not very practical. Unfortunately, the names of my groups are quite long because they define the object at hand quite precisely (for instance, YYYY Exhibition name, Museum, Location) so typing them to create links is prone to error, and manually copying the UUID link or using a script with the group browser to get the link would be easier. Anyway, the easiest is still to replicate groups with my system, and it is the only solution that allows links in both ways.

This is not a need that is special to me, many needs require that you can view and go to the children or view and go to the parents, for instance the painting at a specific exhibition, and the exhibitions of a specific painting. To enable this two-way navigation, using links requires creating two of them. While a script could be used to automatically create them, let’s say in a RTF file, using the group browser, it seems a bit of a hassle.

DEVONthink developers would provide a lot a functionality by adding a linking features between groups, a simple page where you could simply link a group to another one without replicating them, and easily browser the groups linked to a group. That plus a more powerful sheet handling would really find the sweet spot between EDM and protodatabase features.

Placeholders are not limited. You can use DEVONthink’s helper library to interpret custom placeholders in a template. There are examples in the forum. Read also the the built in annotation template package’s .scpt for an example.

For example in this command (using properties and variables defined in the annotation template’s script, for example) you can see that the “placeholders” parameter of the “import” command evaluates a list of delimited placeholders / value pairs – those placeholders can be defined by you in your template. You template does not need to be a smart template – it can be an external file or an existing record in your database:


set theRecord to import theTemplateFiles placeholders {|%documentName%|:theFrontmostDocumentName, |%documentLink%|:{|URL|:theFrontmostDocumentURL, |name|:my helperLibrary's localizedString("Click here to view annotated document")}} to theFirstParent

Anyway, good luck.

You are right, I shouldn’t have said that placeholders are limited. Actually they are very powerful at creation/import time. However, unless I’m badly mistaken, once the keywords have been evaluated and replaced, they will not be updated at a later time, unlike field codes in Word, not because DEVONthink is not powerful enough — it mostly is — but simply because there is no way to store both the ID and the content of a field in a standard plain text, RTF file or sheet without it being seen. So in my solution to the problem I use a visible ID whose linked content I can update by script.

The RTF format supports a tag to hide text. But it would be a pain to manipulate the internal RTF codes and also avoid damage from other apps. Not a fruitful avenue. If your sheets are not too lengthy you could store the {placeholder/value} pair in the Spotlight comments. A kludge, admittedly. Another avenue would be a sidebar file that stores the pairs, but that’s also difficult to administer. Or your could use a real database program (“real” meaning one that supports custom data elements, relations, rules, and layouts) such as TapForms. A good solution but it takes you away from DEVONthink.

To ease updating in your current approach, you might be aware that scripts can be attached to groups and individual documents in DEVONthink. You could modify and simplify your “sync” script and attach it to the group whose records you want to update, or attach it to an individual document’s record in your database. The “attach” part is done via the Show Info panel for a group or record. The syntax for an attached script is:

on triggered(theRecord)
	try
		tell application id "DNtp"
			[do something useful]
		end tell
	end try
end triggered

I think you could also do something interesting using .markdown. Since most Markdown flavors (including DEVONthink’s) support HTML comment tags, and tables, you might be able to store your raw info in a plain text file and use Markdown features to display it. For example, the raw Markdown is on the left, and DEVONthink’s rendering of it on the right. Markdown can include links, of course, and be converted to rich text or PDF.

Related – some users here (@Frederiko) have used Pashua to structure front end dialogs for files stored in DEVONthink.

This is all a lot of work, but I doubt DEVONthink will ever support the features you want because they are rather purpose-built.

Thanks a lot Korm for this very very interesting read.

  • I didn’t know about hide tags in RTF but you are right, it sounds too risky and prone to error.

  • I’m not sure what to think of the Spotlight comments usage as a container: I don’t need to store the key/value pairs locally but rather in a central location to allow updating the values in a single operation so a template sheet works well for that.

  • I didn’t know about Tap Forms but it looks like a more complex Bento or a less complex FileMaker. I chose to drop FileMaker altogether in favor of DEVONthink because there was no elegant way to synchronize nice structured database forms/entries with nicely sorted groups representing the same entries and containing all the related documents in DEVONthink. That would have been the perfect duo to handle structured information on one hand and unstructured documentation on the other hand. But since it’s not possible, I’m trying to get close to simple DB feature in DEVONthink. Working in a single program also simplifies synchronization issues when working in a team setting.

  • I had previously wondered if I should really use the triggered script. I was reluctant to do so because that would create a dependency of DEVONthink towards the presence of the script on the local drive. I didn’t have the time to test it, but I suppose the path to the script is stored in an absolute way, so if I deploy the script to another computer for another user (we are a team) in the standard Application Support location in the user profile, if the user name is different the trigger will not find the script. This is why I chose the less elegant and automatic way of update: clicking a toolbar button. Would you happen to know of a way to link a group to a trigger script that is located in a single way, that is easily updatable and can be deployed on several computers?

  • Markdown is an interesting way of formatting text files that allow links in the content, actually I would favor it over RTF any day because RTF contains all kind of formatting junk that usually are unwanted. However Markdown is more focused on presenting information in a rendering mode, but not on entering data in a table structure. Even though it is possible, it does not seem practical to type all those pipes, so sheets have a more structured and unbreakable feel.

  • I didn’t know about Pashua. It seems to open wide the doors to full control over the UI. I can feel a huge amount of necessary work, but can definitely imagine the possibility for me, if I can provide enough information as parameters to Pashua, to open the sheet in order to display my read-only columns (title, examples) in read-only, and to provide drop-down lists to restrict the values for some fields, and maybe even provide a way to render UUID links clickable. I will really think about it when I get the time.

Best regards,
Edouard

I’m pretty sure if you put the triggered script is a location that is the same relative to the volume root on all machines, then it should work. (Barring any localized property values or paths inside the script.). Say in ~/Dropbox/Shared Scripts/ if that folder were shared among collaborators. I don’t have a setup here to test that today.

I did it, spending many hours on it: I developed a system that uses Pashua to automatically generate a dynamic form for all the fields defined in a definition sheet, that displays the values of a data sheet, and updates them if they have been modified in the form. Pashua field attributes can be modified without modifying the code by simply editing them in a column of the definition sheet. The data sheet is locked before and after opening it with the script (attached to each data sheet).

This allows the sheet’s modification to handle mandatory fields, combo boxes and dropdown lists. Without specific configuration the labels are generated in line with a fixed width but nothing prevents using the attribute column to place them at specific coordinates with a custom size. The system still stores the order and field name in the data sheet so that the data can be understood without the script or when exported.

If anyone is interested I will post the script and sheets (definition and data sheet template) here.

I’m not doing so right away, because there are many features I’d like to add such as a read-only mode at first with an Edit button, and a system to add/go to/remove links to DEVONthink groups.

Yes, please post the solution when you’re ready.

Would be very interested to see your solution too. Been following this with interest.

Frederiko

I’m hard at work on it, it’s getting better every day. Remind me in a week! :wink:

Hello to all,

Here is my much updated version. I removed all specific wording and translated the UI text from French to English.

This version allows to synchronize a form (duplicated from a form template) based on a form definition, by adding/updating/deleting properties (i.e. fields), and to edit a form in a dialog window with more UI possibilities than a sheet (with labels, required fields, tooltips, dropdown lists, pictures, etc.) as configured in the form definition.

For now, the script opens forms in edit mode right away. The script code contains the ability to open the form in read mode before giving the choice to edit it (change the initial “mode” value to “view” instead of “edit”). It works well if you define the fields without specifying absolute location, leaving Pashua place each field below the other, but I disabled it because when using absolute positioning at the same time as date fields, the date field takes much more space in edit mode (a large date picker) than in view mode (a text label), so this doesn’t work well. Another possibility was to display the view mode as disabled controls instead of text labels, but the text is displayed in light gray instead of black which makes it quite illegible.

Also, I’m not quite happy about the fact that, when using DEVONthink in fullscreen, which I do on my MacBook (but not on my Mac Pro), the Pashua dialog window opens in the main desktop, causing an inconvenient screen shift and possibly application focus problems for the script (I haven’t tested much on the MacBook).

I haven’t decided yet if I will add support for links to groups, because I’m not sure I want to go this way with my structured data: even if my solution should be great for most people with simple needs, it doesn’t convince me entirely for heavy users with complex requirements. For instance, Pashua does not offer full programming possibilities, for the copy to clipboard feature, I had to exit the dialog window, which is not very professional.

Anyway, I hope you will still find some use to this quite long script, I’m sure it has many uses. The good thing with this system is that even if you drop it, the forms remain perfectly readable and compatible with DEVONthink and manual editing. According to me, an IT choice should be made 50% for its ease of getting out of it (getting your data out and minimizing the rework to get into a new system).

Best regards,
Edouard
Generic version for release (2016-01-28).zip (91.1 KB)

This is excellent – a lot of work and a tribute to your thoughtfulness for this community. It’s also a model for how to document and distribute contributed scripts.

Years ago, DEVONthink v1 had a built-in forms feature. (If I recall correctly, it was a front end to what became today’s “sheets”.) It was terrific but got dropped. I think you’ve made a great alternative to that.

I have found many applescripts, especially those which rely on determining the topmost window (think window 1) do not work reliably across spaces, for obvious reasons when you think about it.

Thanks for this. Going to enjoy looking at it. Sure to generate lots of ideas

Frederiko

Have been following this thread with interest. Many thanks for the script and work put into it.

Edit: Irrelevant. Will download Pashua and give it a go.

Thanks, Edouard. I’m anxious to take a look inside. :smiley:

So, any feedback, does it fit anyone’s needs :question:

I’ve only unzipped it. I’ve been so swamped in here I haven’t gotten to run a test on it yet. :open_mouth: :cry: :mrgreen:

Well, that’s a first step. :laughing: