More than 1, one to many relationships help needed

March 10, 200817 yr

Hello All, great to be part of the Forums. I have just started working on a FM9 database and am trying to relate 1 customer to multiple inventory items which is also related to another point of sale table and delivery table. The layouts and relationships I've been trying to create ultimately turn into dead ends. I've attached a couple screenshots of what I have, one is the layout with one inventory item which I don't want new inventory items, just to relate multiple records from the inventory table. The other attachment is my relationship graph, any help would be much appreciated. Thank you.

March 10, 200817 yr

trying to relate 1 customer to multiple inventory items

I think that customers should not be related to inventory items.

Sounds like you are looking for and Invoice table where you choose the customer and then that customer can purchase any item that is in inventory table?

Something like:

Inventory

Line_Items

Customer

Invoice

Inventory (ItemID) = Line_Items (ItemID)

Line_Items (PO_Number) = Invoice (PO_Number)

Customer (CustomerID) = Invoice (CustomerID)

On Invoice Layout you put a portal that displays records from Line_Items. Line_Items pulls the data from Inventory when you enter ItemID into the portal and Invoice pulls data from Customer table when you choose CustomerID.

In order for it to work you need to enter a PO number in the Invoice table which will complete the relationship.

Hope this helps or I did not understand the question. :)