I've been searching high and low for any way to perform a table join in Filemaker using a foreign key relationship without using a blasted portal! With a little bit of ingenuity and a lot of brute force, I finally found a way. I'm hereby declaring April 13th forevermore Victory over Filemaker Day! Here's how I did it.
Say you want to list organization affiliations on a given contact's record and you have two tables:
- organizations: with the fields - organization_id(PK) and organization_name
- contacts: with the field - organization_id(FK)
To display a list of organization names on the contact record:
- Create a layout based on the contacts table.
- Go to Manage... in the File menu and in the Relationships view (looks like an ERD or schema) link the contacts:organization_id field to the organizations:organization_id field.
- Now go to the Tables view and select the contacts table.
- Select the contacts table and add a calculation type field: calc_organization_names.
- For the calculation, insert the following formula which will take the matching organization names based on the table relationship and replace the line feeds with a comma:
- Substitute ( List ( organizations::organization_name) ; ¶ ; ", ")
- You may use a calculated field in the formula, e.g. if you wanted to concatenate organization_name with organization_city.
- NOTE: Unfortunately cannot combine fields directly within List() function (with multiple fields the first field's values get listed followed by the second, and so on).
- Click OK to save the database changes then return to the new contacts layout.
- If it wasn't added automatically, add the newly created calc_organization_names field to the layout.
- If you already have the appropriate data filled into the tables, you should be able to see the comma-separated organization lists as you browse through your contacts.
For me this was a huge victory because finally it meant getting Filemaker to behave more like a proper database as opposed to the kneecapped desktop-based monstrosity I've come to know over the years. Being forced to use this on projects instead of the real SQL databases (MySQL, PostgreSQL, mSQL, Oracle,...), this comes at nothing less than sweet relief!!! (Sweeter relief if I didn't have to use Filemaker at all, but that work's in progress. One step at a time.) Thanks to my colleagues who've put up with my ceaseless bellyaching about Filemaker over the past few months.
...but what the heck. One more rant for the road:
The Foreign Key Relationship is basic database functionality. It shouldn't be SOOOO hard to find out how to implement this properly on anything calling itself a database. Yet for all my searching I found absolutely no documentation pointing to me a solution that didn't involve portals which lock it into a stupid scroll box in layouts, hamstringing further use of the resultant data.
So short of buying a Filemaker book that may or may not have the answer I needed, paying for a training class or one of those infernal pay-as-you-go help forums I was stuck coming up with the solution above k-solo. Yes, that makes me a cheap bastard, but it's the principal, dammit!
SQL's been around since the 70's and is a de facto standard for relational database interaction. If you produce a product that mimics a relational database, then pay your developers properly, give them a few more Red Bulls and let them make it work the way a database is supposed to. Or at least document it properly so these basic functions aren't hidden! Cute quirky history with Apple aside, obfuscation doesn't equal value!!!
Posts
