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

I’ve made some progress in my research.

Two years after having dropped it, I’ve tried again FileMaker Pro Advanced (I have version 13).
It is powerful enough to write proprietary scripts that can launch an Applescript. Therefore I have been able to create a proof of concept with a “Link to DT Group” button in a FileMaker form that opens the DEVONthink browser and allows the user to pick a group that holds all documents related to the current entry. The group’s UUID is then stored in a field I called “DEVONthink Group”.

try
	tell application id "DNtp"
		set thePickedGroup to (display group selector "Pick a group to link to FileMaker's current record.")
		if thePickedGroup is {} then error "No group was picked."
		set toPath to the location of thePickedGroup
		set toDatabase to the name of the database of thePickedGroup
		set dtLink to ((uuid of thePickedGroup) as string)
	end tell
	tell application "FileMaker Pro Advanced"
		set cell "DEVONthink Group" of current record to dtLink
	end tell
on error errorMessage number errorNumber
	if the errorNumber is not -128 then display alert "DEVONthink script" message errorMessage as warning
end try

I could also create a “Open DT Group” button that opens the linked group in DEVONthink.

try
	tell application "FileMaker Pro Advanced"
		set dtLink to cell "DEVONthink Group" of current record
	end tell
	if dtLink ≠ "" then
		tell application id "DNtp"
			set dtRecord to (get record with uuid dtLink)
			open window for record dtRecord
			activate
		end tell
	end if
on error errorMessage number errorNumber
	if the errorNumber is not -128 then display alert "DEVONthink script" message errorMessage as warning
end try

However, FileMaker is a real pain and required advanced scripting and UI hacking when it comes to a feature as elementary as linking the current entry to a new or existing entry in a many-to-many linked table (n:n relationship). Even by copying what is shown in the program’s sample solutions, I couldn’t get to a working result after several hours. Of course I will manage after some more time, but this is clearly unacceptable considering the price of this piece of software (€600) and the fact that computers and software are supposed to do for us repetitive tasks. Searching and linking to an entry from a linked table should be the bread and butter of personal database software.

Then I tried Tap Forms, and appreciated it, considering I value the ease of configuring new fields. Linked entries, even in many-to-many relations, work perfectly. In no more than 10 seconds, you can ask the program to search and pick an existing entry, or create a new one using the standard form. Also, with no more than checking a checkbox, you can see the corresponding linked entries table in the other table (other direction of the relationship). So, if I accept to lose the advanced features of FileMaker Pro Advanced such as the local web server, Tap Forms offers exactly what I would need… except it has no Applescript support. Unless I haphazardly try to emulate the keyboard, I don’t see how I will be able to create the same link as I could do with FileMaker. Actually it is possible to use the web field type to hold the DT link but it requires a manual copy/paste, and missing AppleScript support means no way to implement features such as real calculated fields (currently no if/then/else statements or comparison operators in calculated fields).

Any idea? Is there any equivalent application to Tap Forms that supports n:n relationships and AppleScript?

Best regards,
Edouard

Unfortunately, short of going full bore into SQL, there’s not much available on OS X for relational database management. You’re already working with the top candidates.

But consider if you really need to have your script control both the database program (FileMaker or TapForms) and DEVONthink. If the goal is to pause on a field in the database, open the group selector to choose a group in the database, get the reference URL for that group, then paste it back to the database – you can do all that with a KeyboardMaestro macro that either manipulates DEVONthink’s menus to get the item link, or a macro that has a version your script embedded to get the reference URL (item link and reference URL are the same thing; different terms used by DEVONthink). With a shortcut assigned to your macro, you’re on your way.

KeyboardMaestro (or, in different ways, TextExpander and Typinator) are powerful allies of DEVONthink when you need to interact with other applications.

Alternate: another way to do this is to use Split View with TapForms on one side and DEVONthink on the other, open to a group list such as Three Pane View. Copy the link from DEVONthink, paste it to TapForms – it’s not always necessary to write software to solve workflow issues.

Great point, korm (and something I had fallen prey to in the past).

”Hey, Jim… that thing you’re building?”
“Yeah?”
“Uh… we’re done.”
“Oh… okay.” :blush:

:mrgreen:

Then I’ll probably lean towards Tap Forms, despite the lack of Applescript. You often mention Keyboard Maestro. What can it do and how can it be useful if all it does is outputting a series of shortcuts (if that’s all it does)? For instance, let’s suppose that the first part of the macro got the link of the current group (I guess it cannot control if the current selection was a file instead of a group), let’s suppose that it activates Tap Forms without needing to know how many Cmd+Tab are required. It won’t know which field is active there before pasting the link into it. Before I buy this program, could you please elaborate on how it would be possible to use it for this purpose? (The best scenario case being that I could have a group picker opened when I’m on my Tap Forms form in order to retrieve and paste the link).
Thanks!

You would trigger your macro while you have the TapForms field selected – the macro would open DEVONthink, run your script, ask for the group, get the group’s reference url, put it on the clipboard, take you back to TapForms, and paste the url. Macros can manipulate menus, they can contain app script (AppleScript, shell, Swift), manipulate images, set and use environment variables – including it’s private variables, pass and read its variables in scripts … and all of the other things shown here:

d.pr/i/1hTkS (This link will disappear in 24 hours.)

Thank you korm for your answer, this is very precise. I did not mean to make anyone responsible for solving my own problem. I was mostly curious and wanted to get expert advice if possible. I will do my research as I have already previously done.

Thanks for this Edouard. You might also want to look at Shane Stanley’s Myriad tables. It might be better than Pashua for this.

http://www.macscripter.net/viewtopic.php?pid=184809

Frederiko

This is very interesting, I’ll check it out. Thank you Frederiko!

Maybe Ninox is worth looking at. After more than 20 years of moving my structured data from one database program to another in a seemingly never ending search for the ideal database program I now consider my search ended and am almost perfectly happy with Ninox.

I always wanted one program to do it all and so I built a large database with DT Sheets in DT version 1. As replicating of sheet rows did not work anymore in DT 2 I went for plan B - looking for a separate relational database program. Bento, Tapforms and a few other , Filemaker was too expensive for me and I didn’t like it. Ninox is very fast and I am often amazed about how well done it is. So now it is Ninox with links to DT Groups with all the documents.

Thanks a lot blueshoes for this piece of advice, it sounds exactly like what I’m looking for, more complex than TapForms but less complex than FileMaker. Unfortunately there is no Applescript support but there are formulas, triggers and a LAN server/local cloud option. I’ll have to check if there can be conditional field visibility and most importantly good n:n relationship management.

Best regards,
Edouard

What a disappointment! There was no trial version so I bought the Mac and iPhone version to test synchronization and features. It is true that, opposite to TapForms, it features real formulas (the visual editor is buggy but the text editor works well), triggers, conditional display, constrained drop down lists and tabs in forms. However, opposite to TapForms, comboboxes do not exist (how basic are they?), you cannot add a photo by drag and drop (you have to click and browse to find the image file), the UI does not look good, there can be only one form per object, forms (UI) cannot be customized as extensively (pictures have a fixed height for some reason), and most importantly many-to-many relationships are not supported! (cf. http://manual.ninoxdb.de/en/relations/) unless you use two one-to-many relationships through an intermediary table. While possible, this is quite complicated opposite to their argument of simplicity, and makes data entry and form editing unnecessarily complex when you don’t want additional fields on the relationship, for instance in simple cases such as a music album that can feature several artists and vice versa. Also the local cloud option (Pro version) is limited to web clients, not thick clients.

Back to the drawing board for me.

Sorry about that Edouard. I hadn’t checked the n:n capabilities since I never needed it and through the grouping feature I currently don’t even use 1:n anymore. Ninox is a young product so some features are rather basic but the developer is very responsive. It took some time for me to get used to the UI concept which is quite different to other database programs. My main requirement is speed and that is where Ninox excels (that is what I also liked about DT sheets). TapForms used to get incredibly slow with larger datbases.