Creating RelationShip Between 3 tables

[gullfounder]

Hi,

I am a new in the FileMaker and Database Making. I am trying to relate the 3 tables but the Filemaker Doesn't allow me. Kindly help me resolve this issue.

Here is how i am trying to relate the table.

Purchase

Purchase ID (PK)

Supplier ID (FK)

Date

Purchase Line

Purchase ID (FK)

Product ID (FK)

Quantity

Product

Product ID(PK)

Product Name

I am trying to relate the Purchase Detail with Product and Purchase detail. But Filemaker creat another Graph or Table (I don't really understand it why? or How?). But is that a wrong ? why?

And how to resolve this in Filemaker?

[gullfounder]

I am attaching this PDF file of my Database Relation Diagram. In this table i am trying to Add the Relation between the Product and Purchase Detail tables.

test.pdf

[djc728]

What do you mean Filemaker does not allow you. What you seem to have is the classic Invoice to Line Items to Products relationship.

Invoice ( Inv_ID) related to

Line Items ( LI_ID ( PK), Inv_ID ( FK ), Prod_ID ( FK ) related to

Products ( Prod_ID ( PK)

D.

[gullfounder]

Yes that is right. But when i try to create the field in layout mode. It is created fine but when i try to input a value i cannot select the field the field. Even when i try to click on it does not do any thing.

I noted that in relation view there is no relation line between the Product Table and Purchase Detail table. When i try to created 1 it creates the Graph table!

[gullfounder]

Here is the screen shot of what i see when i try to create the relation between Product and Purchase Detail.

[eos]

See if this helps:

http://www.filemaker...ional.11.3.html

[gullfounder]

ok i understand the concept of occurrence. But When i have another occurrence of the table Purchase Detail does it have the same values as of the original?

And how to make the layout to handle this type or Relation?

[gullfounder]

I tried creating the occurrence of the table. But still when i create the Field in the Layout i cannot enter data into field. Kindly help me solve the problem. I am attaching my Filemaker Pro 11 File. In there is a Layout Name Purchase. I am trying to create Field in that Layout But i cannot not.

Project_Filemaker.zip

[eos]

ok i understand the concept of occurrence. But When i have another occurrence of the table Purchase Detail does it have the same values as of the original?

And how to make the layout to handle this type or Relation?

Your question shows that evidently you have not understood the concept of table occurrences. There is no “original” TO; all TOs of the same table only differ in the perspective (the context) they have to other (connected) TOs, but in itself, each one “is” the table. Hence the term “occurrence”, rather than “copy”.

I tried creating the occurrence of the table. But still when i create the Field in the Layout i cannot enter data into field. Kindly help me solve the problem. I am attaching my Filemaker Pro 11 File. In there is a Layout Name Purchase. I am trying to create Field in that Layout But i cannot not.

You can't enter data in that field on that layout, because you have no Purchase Details records (yet) which would establish a link between Purchase and Products, so the relationship is not valid (meaning there is no related record from which you could display data in that field). Furthermore, you wouldn't want to modify or create a product from the Purchases table; instead, use a Products layout to first create the products you want to include in a purchase.

You might want to read this introduction into database design: http://help.filemake...etail/a_id/3247

See if this article helps you, also from the FileMaker Knowledge Base. http://help.filemake...n-filemaker-pro

Where it reads “A student can take many classes and a class can have many students.”, just think “one of my products can be in many purchases, and purchases can have many products.”; then Signups in the example would be “Purchase Details”.

[gullfounder]

You might be right about me concept. But still there are 2 records in the Product table. At least these should be shown in the Purchase Detail table? Am i right?

You right i don't want to create new Product Record from Purchase Table. As you know When 1 purchase can have many Products. That what i am trying to achieve. I wanted to create a layout by which i can add products on in Purchases. So, i have added some products in Product Table. But when i try to make field in Purchase Layout i can't select the field ?

I cannot understand the reason why?

Sorry! If it is dumb question!

[gullfounder]

eos I am great full you are helping. Kindly download my Filemaker File. At least check the database is it correct or not?

When i try to relate the Product Table with Purchase Detail table it asks me to create new occurrence?

But i saw a Lyanda Video Tutorial that i created the relation between the Invoice Line items and Products with out occurrence!

[eos]

You might be right about me concept. But still there are 2 records in the Product table. At least these should be shown in the Purchase Detail table? Am i right?

Your added TOs are OK so far, but what you want to do (or think you want to do) is not possible this way.

Try to see it like this: if you have two records in Products and one record in Purchases, why should these products show up in that purchase? Which connection is there between any one Purchase and any one Product? Answer: there is no inherent connection - until you create it.

Only when you create line items (or Purchase Details) for a purchase, which use products, you make this connection; then you can see/display these products (directly as line items or indirectly in Purchases).

You really should read those articles I linked to, or maybe an introductory book about FileMaker Pro which explains this concept in-depth and with some practical examples.

[eos]

But i saw a Lyanda Video Tutorial that i created the relation between the Invoice Line items and Products with out occurrence!

I have seen your database, and I'm sure the Relationship Graph in the Lynda tutorial had a different structure than yours. Note that there must be only one connection between two TOs (including indirect connections via a series of uninterrupted connection lines), so trying to connect them again brings up the dialog.

Study the tutorial again; in your Relationship Graph all TOs are connected to each other, which in the tutorial example most likely will not be the case.

[gullfounder]

Yeah you are right it is bit different. But i borrowed the Invoice concept from the tutorial. the tutorial had 3 tables i used them as they where and added with some of my own creation.