FileMaker Pro Relationships: Using a self-join to auto-fill data from the same table
A redditor asked, “If I enter a client that already exists in my database into a new record, how can I have it automatically fill in the company name from that client?”
Simply put: you can use a self-join to look up information from the same table, just like you could use a relatiomship (a ‘join’, in database geek parlance) to look up information from a different table.
Here’s an unlocked demo file you can explore to see an example of how it works.
Click to download: Self-Join example.fmp12
How it works
1. The file’s layout is based on a CLI__clients table with two data fields, client and company.
2. A relationship is set up on the Relationship Graph, but instead of it being between two tables, it’s between the client field of two table occurrences of the CLI__Clients table:
This was done by dragging from the Client field and one table and, holding your mouse button, down drag it off the table, and then back onto the Client field in the same table, instead of onto a different table. This creates a second Table Occurrence of that Clients table (a fancy word for the same table being displayed a second time) in the relationship graph, which I’ve named cli_CLI__clients__selfByClient. It’s still just one table, but by using the cli_CLI__clients__selfByClient relationship, you can refer to related records in that table, rather than to fields in the same record. It works exactly like any other relationship, it points to related records in the same table rather than another one.
3. Then, the field CLI__Clients::company, a text field, is set to auto-enter a calculated value of cli_CLI__clients__selfByClient::company.
This causes company to be automatically filled with the first related value from client from the same table whenever the client is changed.
If this is all a bit tough to wrap your head around, just open up the demo file provided above and have a look at it. It might be clearer to see it in action that to try to understand a written explanation of it.
UPDATE: A second redditor asked, “If I enter a client that already exists in my database into a new record, how can I have it automatically fill in a serial number for that client, with the first letter of the client name followed by a number that increases to track each client separately?“
(Actually, they asked about wine, not clients, but I already had this demo set up and used clients, so I used that instead.)
For instance, if you entered the clients “Bob”, “Frank”, and then “Bob” again, the serial numbers would be “B1”, “F1”, and then “B2”.
In this case, you’d use a field CLI__Clients::numberForClient, and to update it you’d autoenter it as max(CLI__Clients::numberForClient)+1.
This has been added to the demo. Again, probably easier to understand if you just look at how it works in the demo than if you try to understand from my description.
