AnsweredAssumed Answered

Setting up a tricky Relationship. Please help!

Question asked by alexmiller on Mar 4, 2011
Latest reply on Apr 3, 2011 by alexmiller

Title

Setting up a tricky Relationship. Please help!

Post

Help! I can't figure out how to set up the Relationships graph for the following:

I have 4 tables (3 existing and 1 newly added)

Products
- Product ID
- Quantity on Hand (calc of Quantity Received - Quantity Shipped below)

Receiving Line Items
- RLI ID
- Product ID
- Quantity Received
- Location ID (in a warehouse where put away)

Shipping Line Items
- SLI ID
- Product ID
- Quantity Shipped
- Location ID (warehouse location where taken from)

Locations (this is a new table and is what is complicating everything)
- Location ID (this field was added to the Line Items tables above too)

I need two create two portals:
- a Product portal which only lists the  current locations where the selected product is located and the total  quantity at each location

Product 1
 Location L1    x cases
 Location L2    x cases

- a Locations portal which only lists the  products currently at the selected location and the total quantity of  each product at that location

Location L1
  Product 1    x cases
  Product 2    x cases

I've spent days experimenting with this with no luck. The fact that I have a separate IN line items and OUT line items tables seems to make it more complicated (however, I can't help this as I'm making these changes to an existing database). I've created a calculation field in each line items table called "ProductIDLocationID" which seems like I'm getting closer, but I have no idea how to setup the relationships to the new Location table and how many instances I need, etc.

Any suggestions would be beyond appreciated!

Outcomes