match field question

[cricket]

I have looked through the FM Help documentation and cannot find the answer to this. I have a file with 2 tables in it. The main table has a portal in it and I want it to display records from the other table. Now when I set the match field relationship to "=" none of the information show up in the main table, neither in the portal nor outside of it. However, when I set the relationship to "X" everything shows up fine. Is there a particular reason for this?

Thanks.

[Lee Smith]

That is telling you that your relationship is not finding the exact references, this could be that your fields are of different type (date = date, text = text, number = number... etc.)

Lee

[cricket]

What if they are both specified as numbers?

Also, what is the difference between placing the field from the related table as opposed to placing the field from the main table list? The one comes in as a regular field and the other comes in like "::field" telling me it's placed from another table, but is there a reason to choose one instead of the other?

And why isn't this documented in the FM Help? Isn't this an integral part of using FM?

[Lee Smith]

Names mean nothing, go into Field Definitions and be sure that both of the fields are number.

If they are, the same type, then you might want to attach a copy of it so we can investigate the relationship

Lee

[cricket]

I get that the names mean nothing, but I don't understand why FM is letting me choose between placing a "field" from the main table, or a "::field" from the related table. I know that the names of fields can be different in different tables and still related to each other. But once I establish the relationship:

1. Why would I have any cause to place one field instead of the other?

2. Is there a difference between them?

3. If there is no difference, then why am I allowed to place either one? Shouldn't FM just say, "OK, you can place this "::field" (or this "field") now because you've set up a relationship between the tables."?

It's kind of confusing that I am allowed to place either.

[T-Square]

FM uses the immediate context of the control to determine what gets displayed. Inside the portal, you're "in" the related table; outside it, you're "in" the main table. So, in your portal, you want to put fields from the *related* table; this is how you ask FM to show you stuff from the related table. If you put a field from the main table in the portal, FM has no way of knowing what you want, since the main table field has no meaningful context in the portal.

I think FM allows you to put either field anywhere because in designing the layout, you are free to put fields anywhere--either within or without the portal. It's up to the developer to figure out the correct placement, based on their knowledge of what information they need.

HTH,

David

[cricket]

Thanks.

So then, I would never want to place the match field created in the main table into the main table layout.

But then this kind of seems redundant to even create a match field doesn't it? Wouldn't it be less confusing if for each field in a table there was a simple checkbox that said "make this field visible in other tables"? Then we don't have to create match fields at all and we only have one field that exists for that data. Would that be a stupid idea?

[LaRetta]

Cricket, why in the world would you WANT to place a match key from another table on your layout? Let's put this into perspective here ... you don't want to. What you want is to place ALL OTHER related fields. FM gives you the ability to cross-place related fields because, in ALL CASES except the match field, the data doesn't EXIST in the current table - you've stored it in the related table instead. That is the power of relational. The only exception is the match field which must exist in both. Since there are many times you will want to cross-place fields, FM gives you the choice to place all fields (including the match).

Example:

Parent is related to Child on ParentID (match field). While in Child (on Child layout), you may want to see the Parent's address. But you don't want to AGAIN type the Parent's address within the Child record. So instead you grab a copy of the Parent::Address field and just place it on the child layout. Poof! Instant address.

You are asking questions which go back to (and before) basic relational theory on why keys (match fields) should be used. It would be helpful to grab that FM Manual and read about relationships. Then make some test ones and watch how they work. You will see the data is only entered once. But because they are related on the KEY, that data (by placing it's field on another layout) becomes instantly available. Better to duplicate one field (a key) than duplicate all the data in each table, no? It will all start to make sense once you start DOING it. Once you understand, you'll be tickled with the idea of using match fields.

LaRetta

[cricket]

Thanks LaRetta.

See, I didn't even know that you weren't supposed to place the match field on your layout. There is nothing in the Help documentation that tells you not to place it. Maybe it is supposed to be common sense? It's a little hard to find anything useful in the Help docs, and I don't have a printed manual. Everything seems to say, "ok, you build a database this way", however it does not tell you WHY you should build it this way.

I am starting to do it, and that's why I have all these questions. Because the documentation is of no help. Especially with the use of portals. It just says, click the portal tool and draw your portal and add your fields from the related table. But, what if something isn't showing up? Well, then something is wrong with the way you set up your relationship. Yes, I know that, that is why the info is not showing up. But I am left to figure out how to fix it without any knowledge of how to do that.

As a beginner, I would EXPECT that I have to place a match field into my portal in order to get the linked information to show up in that portal from the related table. It seems to make sense. Because, if that match field isn't in the portal, then how is FM going to know the records are linked to the portal?

Out of curiosity, do all databases work this way, or just FM. For instance, if I was working in MySQL, is it a similar approach?

[LaRetta]

Hang in there, Cricket,

Using a unique identifier (although they are called different things in various databases) is the norm. And linking these unique IDs to provide one-to-one (1:1), one-to-many (1:n) and many-to-many (n:n) relationships is also the norm. You don't need the ID in the portal. FM knows they are related because you told it in your relationship graph when you selected:

ParentTableOccurrence::ParentID = ChildTableOccurrence::ParentID

That relationship still exists even without the portal. The portal simply allows display of multiple children while in the parent record. So, even without a portal, you can directly place fields from the Parent table on your Child layout and view any parent data. They are related. Usually, uniqueIDs are not displayed. Set their properties (in field definitions) to Prohibit Modification. If I display this uniqueID, I also prohibit entry in Browse mode so the User doesn't get a sharp rebuff from FM if they try to change it (if Prohibit.. is on).

Most of the information and training materials I have on relational design and theory is hardcopy from several years ago (although theory still holds). Maybe someone knows of some links which provide a basic understanding of the concept. Once a business (or an individual) grasps this power (eliminate redundancy), they are hooked. That's why we're all in business today.

LaRetta

[T-Square]

LaRetta--

You said: "Usually, uniqueIDs are not displayed."

While it's true that it's not *necessary* to display UniqueIDs to the end user, I have found in practice that it's helpful to have the ID on display as a point of reference to the end user.

I can't tell you how many times I've had duplicate records (with different IDs) and pulled out my hair trying to figure out why my database wasn't working, only to find out that somewhere along the line, I'd duplicated the record, and I wasn't seeing that because I didn't have the ID displayed...

That's just my own two cents, since cricket is interested in gleaning from our experience.

David