Mulitple relationships to same source data

[veruca]

Hi everyone-

I've dabbled in FileMaker for a few years and now I'm starting to make my first serious database. What I'm making is an order entry database. It's actually rather simple with the exception of the issue I'm stumped on.

The background info: I have a single database file. In it are two tables. The first table has an order number, customer name, item, itemqty and itempicture. What should happen is when you click on the item field, a pop down list comes up, you select the name of the item they're ordering and then the picture of the item pops up into the itempicture container. All of this works just fine. The second table are the item pictures. There are only two fields, itemname and itempicture. I've defined a relationship between "item" in the first table and "itemname" in the second table to make the picture show up in the container. This all works flawlessly when I have only one item to order on this first table.

Here's the problem: How do I get this to work when I want to have multiple items? I've tried making "item2", "itemqty2" and "itempicture2" and then linking "item2" in the first table to "itemname" in the second table, however when I do this, when I select the item in the first item list (variable "item") *both* of the container fields change to the selected item. I cannot select them independently.

Basically, my question is this: how can i have multiple fields in one table, look up the same data in another table?

THANKS!!!

jared

[transpower]

Try three tables: order, lineitem, itempicture. Link lineintem and itempicture. Put lineitems in a portal in order.

[veruca]

I'm sorry... I'm not following...thx for the help

jared

[-Queue-]

This is a classic many-to-many relationship. Do a search on +join +table or "many to many" or "many-to-many" on this site. There are dozens of threads discussing it.

[QuinTech]

I'm not following

Nor am i, because transpower is being unnecessarily abrupt. What he is saying is this:

A good relational structure for what you are trying to do demands at least three tables: One for Orders, one for Products, and one for LineItems. It's not required for your question, but i think it would be a good idea to also have a Customers table. And, before we start, you should base all your relationships off of unique ID numbers rather than item names-- names change, but ID numbers don't.

The LineItems table need only contain two fields, OrderID and ProductID. Its only purpose is to create a connection between an order and some products. (This would commonly be called a join table, and you should find a wealth of information on this forum and other database resources regarding join tables.)

As for relationships:

1. Orders::OrderID to LineItems::OrderID

2. LineItems::ProductID to Products::ProductID

To see why this join table type of structure is necessary, think about how you would include two products on two orders without duplicating the products. The only satisfactory way to do it is with a join table:

OrderID ProductID

----------- -------------

1 .............. 1

1 .............. 2

2 .............. 1

2 .............. 2

Which means that Order1 includes both Product1 and Product2 and Order2 includes both Product1 and Product2.

Maybe you should try setting this up like that and check back, because i'm still a ways away from answering your question.

Hope this helps,

Jerry

[veruca]

Jerry-

that most definately sets me on the right direction. thanks a ton!

jared

[transpower]

Thanks, Jerry, for providing the elaboration. I have to keep my posts brief because of time constraints...so many posts, so little time...

[veruca]

Jerry-

OK, i've got my totally redone database created... i have 4 tables now (created a customer table like you suggested...that way i can keep contact info too).

now, the question remains, however i'm sure it will be easier now that i have things set up right now... how do i go about creating that second, third, forth etc... item for each order? do i make duplicate fields now? (itemname2, qty2, picture2 etc...) ?

Thanks!!!

Jared

[The Shadow]

Jared,

Don't make duplicate fields, you can use a portal to allow you to create multiple items in a single order.

I have an example on my website:

http://www.spf-15.com/fmExamples/

you can download the "simple invoicing example" and take a look, it shows a rough setup for a bookstore.

[QuinTech]

OK, i've got my totally redone database created

Wow! Talk about rapid application development!

[QuinTech]

transpower said:

I have to keep my posts brief because of time constraints

Well, you don't have to respond to every post, do you? I find it's more helpful to withhold comment if i can't provide usable directions.

[hayesk]

The LineItems table need only contain two fields, OrderID and ProductID.

You may also want to include a few more fields in the line items:

- quantity, incase the user wants to order 100 of an item. You don't want to have to enter the item 100 times

- price - This can be auto-entered from the products table, but sometimes you need to change the price. For example, you may want to discount an item (it may be marked down because it is refurbished, scratched, etc.). You don't want to mark down the price for every item on other invoices. Also, if the price comes down in the future, you probably don't want it to change in old invoices.

- notes - not really necessary, but it's handy. For example, you lower a price on an item because it is scratched, etc. and you want to remember why you lowered the price.

[veruca]

Wow, you guys are great. I'm gonna go look at the portals route... from the bookstore example it looks like it's right up my alley.

Thanks!

Jared