Jump to content

how to query to


Uncle Bob
 Share

This topic is 5663 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I have three tables:

Person:

PersonID

PersonName

Phone:

PhoneID

PhoneNo

PersonPhone:

PersonID

PhoneID

And I've established the obvious relationship links.

How can I write a query so that given a person's name, I get back the phone number(s)?

All the DB I learned was standard stuff, e.g., from the O'Neils' book, all theory and SQL. I'm confused about how this translates to FM. I even understand stuff like B-trees, etc., but knowing it's not helping.

I hope and assume that this question and the answer are trivial, but I have been unable to translate all the FM documentation into the concepts and terminology I'm familiar with.

Thanks in advance.

Link to comment
Share on other sites

  • Newbies

Finding info in another table.

Figured it out.

As an academic, I was looking for pretty technical language and not finding it. But this messy stuff is probably best taught/explained through examples.

I now understand that FM uses explicitly stated relationships and the graph they form can only be a tree, no cycles or loops allowed. There is thus a unique sequence of links between any two tables in a relationship graph.

I finally learned, from Coffey's FMPro8 The Missing Manual, and especially, Chapter 7, pgs 318+, that the key is to add a field from another table to a layout. Then FM will trace along the unique path from the table you're in to the other one. So FM was able to trace through the join table I had created. I could finally type in a name and get a phone number because FM could trace through the personID to the join table to the phoneID and get the phone number in that last table. I even created a Portal to show multiple hits.

Now I'd like to figure out how, once a phone number is returned to also find more info about that phone no such as is it business or home, how many hours different is it from my time zone, etc.

Looks like I still have my work cut out for me.

If anyone will ever be able to find this post from its messed up title, maybe they can profit from my road to understanding.

-- UB

Link to comment
Share on other sites

Well, we have some specific terms for FileMaker objects and operations. For example, we would not say:

"unique sequence of links between any two tables in a relationship graph"

We'd say:

"unique sequence of links between any two table occurrences in a relationship graph"

The objects on the graph are table occurrences (TO), which are somewhat like what an "alias" is to a file. There can be many for 1 "base table" (if you want to distinguish the difference), each of which has specific relationships to other table occurrences.

Your statement about uniqueness is a primary fact. It's the reason why a FileMaker relationship graph has so many more connections than an entity relationship graph (ERD).

There seems to be a little confusion about layouts, tables, and relationships. FileMaker automatically creates a layout when you create a new base table (as well as a table occurrence on the relationship graph; but it's not connected to any other, yet). The layout is assigned to that TO. Every layout is assigned to a TO.

You do not need to put a field on any layout for a relationship to work however. You do have to put a field somewhere if you want to see it (duh). It may be on a layout belonging to a TO of its base table (:-). Or it may on a layout belonging to a TO which is related to the TO above. It can be on its own, or in a portal with other related fields (usually of the same relationship as the portal, but not necessarily; fields from other TOs on the extended relational line will also work, if logical).

So, "business" or "home" would be in a field "Type," which would be in the Phones table, same as the phone number; probably formatted as a drop-down list or menu.

"How many hours different from my time zone" a phone number is brings up a question. Do you also have a location for this phone? Do you have a separate table for locations; does a person have multiple locations? If not, if there's only 1 location per person, then their time zone belongs in the People table. It would be visible from Phone. Relationships between indexed fields are bi-directional.

If there's mulitple locations, then you'd likely have a table for locations, and the time zone would be in there; and the link to phones also.

Link to comment
Share on other sites

  • Newbies

Are you then implying that two table occurrences for the same base table can have different relationships?

That would allow close to arbitrary, but possibly quite useful, relationship graph complexity.

-- UB

Link to comment
Share on other sites

Certainly 2 different table occurrences for the same base table would have different relationships (usually*). A "relationship" in FileMaker 7/8 is kind of difficult to explain. In reality it is the total path from the originating table occurrence to the destination table occurrence; the lines.

In casual discussion we usually mean the "little white box" in between 2 adjacent TOs. But if the originating TO is not adjacent to the destination TO, the relationship is what results from all along the entire relational line. Many valid combinations are possible; it's up to you to make sure they make logical sense to produce what you want.

You only specify the destination TO (which has whatever name you gave it). The originating TO is determined by where you are at that time. That is why the correlation between a layout and its TO is so important; it's where you are.

Where you end up, the final destination layout, does NOT have to belong to the destination TO line; though it does need to belong to the same base table. This allows you to "jump" between "table occurrence groups,"** bringing the found set and current record with you (my terminology).

Calculation fields, which are defined within their base table, also have a place to specify the TO where they evaluate. Normally this is the base table (whose primary TO was created by FileMaker automatically). However, in some cases you may need a calculation to evaluate in some other specific place; a place which, on the graph, is not even connected to that primary TO. You can specify this place in the drop-down in the field definition.

* A "self-relationship" from a TO to another based on the same base table, using the same field on both sides, could be said to have the "same relationship," since relationships are bi-directional. However, because the layout would be based on the main one (hopefully :-), the two TOs would be "operationally" different; you'd still need to know which was which.

** A table occurrence group (TOG) is a group of TOs that are connected in some way with relational lines. Often each of your main base tables has its own TOG. This is known as the "anchor-bouy" method.

Link to comment
Share on other sites

This topic is 5663 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.