Database Table and Relationship Question

[lidocayne]

Hello all. New to Filemaker here and slowly figuring out how to use the program. I do have a question about a database I was looking at (trying to understand how things work by looking at a working database).

When looking at the database relationship diagram (see pic below) I see that for the blue and purple colored items, that they are all the same tables/fields, just that the tables are renamed.

My question is this: If I look at, for instance, the invoices table, it points to two copies of the customers table (renamed invoice billing customer and invoice shipping customer). Is there any reason the relationship couldn't just have been drawn back to the original customers table using customerID as the pK and billtocustomerID/shiptocustomerID as fK's?

What's the purpose/reason to copy the tables?

I'm sorry if this is such a newbish question, but I'm trying to learn and can't seem to find an answer for this question.

Thank you! Enjoying learning this program, though I can tell there is quite a bit to learn!

Edited October 30, 2010 by Guest

[Fenton]

As you can see on the left, there is a possibility of 2 different Customer IDs on an Invoice, one for billing, one for shipping. In order to connect to each (to see their names, etc.), you need a separate relationship for each, one from the billing ID, the other from the shipping ID, but both TO the CustomerID.

The "table occurrences" (TOs) on the graph can be thought of as "aliases" to the "base table" of the defined Customer table. Like aliases on Macs, they point to the original, but are not the original itself. So it is common to have multiple TOs pointing to the same base table.

[lidocayne]

So is it safe to say that you can not have more than one fK pointing to a pK? If there is the need to point to the same pK that another table is already pointing to, you should create a copy of the table and point to that 'copy'?

Is there a reason for this? Would it result in corrupt data if you DID have 3 different table fKs pointing to the same pK? I ask because FM will LET me point 3 different table fK to the same pK... so was curious what the reason you'd make a copy/alias?

[comment]

A few notes:

1. There is no primary key as such in Filemaker. You can call a field ID or PK and think of it as a primary key - but to Filemaker it's just another field.

2. There are times when you MUST create another occurrence of a table on the relationships graph. For example, you may have a relationship of:

Parent::ParentID = Child::ParentID

Now you want to have another relationship that shows only a selected child:

Parent::SelectedChildID = Child::ChildID

For this, you MUST create another occurrence of either table (preferably the Child table in this case), because Filemaker will not let you have two relationships between any two table occurrences.

The reason for this is that a reference to a related field cannot be ambivalent. In the above example, a reference to Child::SomeField could mean the child record from either relationship - and these are not necessarily the same records.

But if the second relationship is (as it must be)

Parent::SelectedChildID = Child 2::ChildID

then it's very clear what Child::SomeField means and what Child 2::SomeField means.

3. Sometimes you'll see examples where additional occurrences of a table have been created not (only) out of necessity, but out of convenience. The file you are using is an example of that. I am not a big fan of this method, but many developers swear by it. You can read more about it (and other things related to your question) in this article:

http://developer.filemaker.com/content/technet/pdf/approaches_to_graph_modeling_en.pdf

[Fenton]

lidocayne asked, "So is it safe to say that you can not have more than one fK pointing to a pK?"

You don't need to worry too much about making "circular" relationships; because FileMaker won't let you. A dialog will tell you so. So, basically, if you can do it, then it is OK. That does not mean that it makes logical sense or works for your purpose though.

You can point 2 different "foreign" IDs to the same "primary" ID of their parent table. But it doesn't usually make sense. In the case of the Bill & Ship Customer IDs both pointing to the same table occurrence of the Customer table, you'd be saying, "only match if the Bill AND Ship IDs are the same as the Customer ID."

Which might be a convoluted and slow method to see if they're the same, but is otherwise pretty useless, and really the opposite of what you want here. The Invoice's Customer Bill and Ship IDs were created for the sole purpose of allowing 2 separate Customers to be connected to a single Invoice, each with their own role.

[lidocayne]

Thanks everyone. I have included a pic of the current database relationship. Here's my problem:

I can't seem to get a field from one table to show up in a layout. The user clicks on a portal row (based on the papRx portal).... and I can't get that value to show up on the layout screen.

I'm trying to get papRx::epap to show up on a layout based on patients. Based on the pic you see of the relationships layout, do you see an obvious reason why this wouldn't be working?

Edited October 31, 2010 by Guest

[lidocayne]

Ok so I'm still at a loss here, but hopefully can offer the following information based on what I'm seeing.

I have a new relationship diagram, and am putting it below. I have a layout (layout A) based on the patients table, and a portal within that layout (layout A) set up based on the PapRX table.

I have set up a button in the portal (GoToRelatedRecords) that when clicked will go to a new layout (layout B ). This new layout (layout B ) is also based on the patients table.

The issue is that I want a value from the portal, when clicked, to be carried to the layout B. This isn't happening. It seems to not be carrying over anything, but rather it's just displaying the first record from the PapRX table.

Perhaps that will make things clearer?

Thanks!

Edited October 31, 2010 by Guest

[comment]

I have set up a button in the portal (GoToRelatedRecords) that when clicked will go to a new layout (layout B ). This new layout (layout B ) is also based on the patients table.

As I already hinted here:

http://fmforums.com/forum/showpost.php?post/369293/

the request doesn't make sense.

You already are in the Patients table (I'd suggest you name your layout "Patients" instead of a cryptic "A"). The portal is showing prescriptions (I think?) that belong to the currently viewed patient. If you want to stay in the Patients table, then Go to Related Record[] is the wrong step to take. Go to Related Record[] is for going to ANOTHER table, to view the related record/s directly instead of through a portal.

If you want to see the same patient's record through another layout of the Patients table, why not simply switch to that layout? OTOH, if you want to see the related record in more detail, then you cannot use a layout that "is also based on the patients table".

The issue is that I want a value from the portal, when clicked, to be carried to the layout B.

This part isn't clear either. The values in the portal belong to another table; why "carry" them to the Patients table?? And what will you do with this value once you get there?

[lidocayne]

@comment:

Thank you for your reply. I used layout A and B only because the true layout names would probably make little sense to anyone else but me. I did it for sake of simplicity.

You are correct, that the portal is showing various prescriptions (from the PapRX table).

Layout A is a sort of summary screen. Layout B is a prescription layout. When the user clicks on the portal line from layout A, I want it to populate the layout B prescription form with not only all the patient demographic information contained in the patients table, but also specifically take the portal row information and put it on the prescription form (because it is this line which is the prescription itself).

I don't want to carry the value to the patient's table... I want to carry it to the layout. The value does nothing other than be displayed.

I have a button on the layout B (prescription form) which will internet fax the prescription to a pharmacy (creates a PDF of the layout).

I hope that makes it more clear. And thank you again.

[comment]

It's still not very clear, but I'll take a chance:

Define a layout "Prescription", showing records from the PapRX table. Make it a form layout.

On this layout, place ALL the fields that are required to make up a prescription - including fields from related tables, such as the patient's name (from the Patients table) and the prescribing physician's credentials (from the PrescribingMD table).

Define the button in the portal to Go to Related Record [ From table: PapRX; Using layout: "Prescription" (PapRX) ].

[lidocayne]

@comment:

THANK YOU! That was it. I thought it would be something simple like that. Thank you for sticking with me on this!

So for my future knowledge, if I am going to create a portal and use GTRR the layout that it goes to should be based on the portal, and I should bring in the other information from related tables then?

Thank you again!

Edited October 31, 2010 by Guest

[comment]

So for my future knowledge, if I am going to create a portal and use GTRR the layout that it goes to should be based on the portal, and I should bring in the other information from related tables then?

If the circumstances are similar, yes. But GTRR is more flexible than that: for example, you could have another button in the same portal to go the prescribing doctor's record (using a layout of the PrescribingMD table).