Strictly speaking, a match field (aka key field) is any field that is used to define a relationship – which you do by relating two table occurrences in the “Relationships” tab of the “Define/Manage Database”* dialog.
*It is Manage… in FM12/13, and used to be Define… in earlier versions.
While you can relate any two fields to each other in that dialog, be sure to understand which fields are suitable as match fields, and which ones aren't.
No, not really. FM doesn't enforce referential integrity or really care if a field is a "match field" outside of it actually be used in a relationship.
There is a tiny, slight way to clarify that to filemaker. An auto-enter serial field, or one labeled as unique will, when used in a relationship, show a slightly different graphic in the relationship line than other types of relationships.
I thought the MATCH FIELD was the one that is used to tell which record the related records show up in. Like a customer or item number, eh? What am I missing?
Ah, I see. "Referential distance". I didnt' realize at what level your question was...I'm still not sure, but I think you want:
Under Manage Database... go to the "Relationships" tab. That's where you connect one table to another.
I thought the MATCH FIELD was the one that is used to tell which record the related records show up in.
In a simplified sense, that statement is correct (actually, you need to match two “match fields”, one from each table).
What is it you suspect you are missing?
Yes. That's how to establish relationships. But how do you tell FM which records to put the data in? I don't want it to just randomly show up somewhere. I want the tenant address to show under the property. So I want there to be some identifier in the Contacts table that matches up with the property table, right? Otherwise, how does FM know where to put the data?
HOW TO DO IT. Not how to create the relationships, how to tell FM where to put the data - to put the tenant address (from contacts table) into the property record (from the property table). But each tenant has to match up to the right property. How do I tell FM which tenant lives where?
In this one-to-many scenario …
“one property can have many tenants, but every tenant lives in (exactly) one property”
… you would put the primary key value of the desired Properties record into the foreign key field of the designated Contacts record. Once that value is set, you could simply display* the fields from the matching (as per the relationship) Properties record on a Contacts layout – and conversely, display a list of matching Tenants (Contacts) records in a portal on a Properties layout. (In most scenarios within a relational database management system, you don't copy data, but reference it.)
For starters, try creating a value list of Properties: using field values from Properties, 1. field: id, 2. field: address (must be a unique value, like a combination of address fields calculated into an address line), “Display only from 2. field”.
Put the Properties foreign key field from the Contacts table onto a Contacts layout, and format it as a popover with that value list. Select an address (which will actually insert the corresponding primary key value). Now these two record (current contact, selected property) are related.
Note that this is just to get you started; there are more versatile methods to assign/relate a record from one table to a record in another (e.g. a picker portal [inside a popover, in FM13]).
rebfree wants to know how to set the matching field value in the related table.
Since my level is intermediate, please fell free to correct my statements:
- Create a Layout set on the table occurrence for Contacts table
- Add a portal for the Property table
- Set the relation so that you may create records using it
- To set the value in the Property table, use Set Field by making that record current in the portal
I may be missing some piece of information here, but believe this is almost correct.
"So I want there to be some identifier in the Contacts table that matches up with the property table, right?"
That's a very good question. You have a list of properties. How can you tell filemaker that a person belongs to a property? You need some way of uniquely identifying a property. Many beginners will use obvious things to identify a property, like the unit number (eg 45B, 12E, etc) but experienced developers have seen that can produce problems down the road. What if you want to keep track of another building with the same apt numbers? Or the apartments get re-numbered? It'll blow up your system.
It's generally recognized that the best thing to do is to use a meaningless string of letters and numbers. Some people like auto-enter serial numbers, I prefer an auto-enter calc of Get ( UUID ). Make a field in Properties, called pID and set it's auto-enter calculation to Get ( UUID ). Make a field in Tenants, fID. Connect the two tables based on those two fields.*
That's the short version. An additional consideration is if you want to keep track of historical data. What if a tenant moves apartments? Do you want to know that they used to live in a different apartment?If so, you'll need a "Join Table". And that's a lesson for a different thread.
*p is for primary. f is for foreign.
Hi there, this is an interesting question, as it seems your question is how to get data into a Match Field. As you've mentioned above, you know how to create the actual relationship, but how do you fill in data to make sure that there is a relationship between two records. Instead of just calling them match fields, I'm going to use the terms we use mostly in databases called primary keys and foreign keys. All tables should have a primary key, where only certain tables get a foreign key.
In your example, you have a one-to-many relationship from property to tenant (a property can have multiple tenants, but a tenant can only belong to one property). In this case, the tenant is the many side. The many side always gets the foreign key, so your tenant table I assume has a "Property Match Field" in it to relate it to the property? So the question is how do we make it easy for a user to fill in that field without having to remember the ID's for individual properties, correct? (If I'm wrong, I apologize).
There are a few options for you built into FileMaker, and which one you choose depends on where you want someone to forge the relationship. Is the person sitting on a property and creating new tenants, or is someone sitting on a tenant record and want to assign the correct property to that tenant? If the answer is the first one, Gilles above gave the basic outline. Edit the relationship to "Allow creation of records using this relationship" on the tenant side, then create a portal of tenants on a properties layout, and users will be able to create new tenant records by editing the last row of the portal (which shows as blank).
If it's the second example, then you need to create a value list of all properties with two fields, the first field being the primary key of the property table, and the second field being some field that is unique for each property and easy for someone to choose (maybe address? maybe create a calc to show multiple fields?). Then on a tenant layout, assign that value list as a pop-up menu to the "Property Match Field" foreign key in your tenant table.
There are other more advanced options, but those are the simplest. If you'd like some training that will take you step by step on how to create these kind of things, I suggest looking here:
Let me know if you have any questions.
In case your question is about the interaction that "puts data in", I've built you a small demo which shows a possible way to assign/unassign tenants to a specific property, see enclosed.
PropsAndTenants.fmp12.zip 137.6 K
Oh, gosh! Thank you everyone for replying! I'm poring over these answers - I appreciate the detail, and hopefully it will lead me home. Sorry for the delayed response; I don't get time to work on this project very often!
Perfect! You guided me to the problem I was having. I was connecting every field in the define relationships, when I only need to connect the ID fields. Now it's working perfectly, of course! Thank you so much!!!!