2 Replies Latest reply on Apr 3, 2011 1:05 PM by alexmiller

    Setting up a tricky Relationship. Please help!

    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!

        • 1. Re: Setting up a tricky Relationship. Please help!
          philmodjunk

          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).

          That's the issue in a nut shell. If you could merge Shipping Line Items and Receiving Line Items into a single table, this would be much easier to set up. Neither table has all the info you need as you need a balance on hand for a given product at each location. Just because this is an existing database doesn't mean that this cannot be done, but it would require that you move carefully and be able to take the database down long enough to run an upgrade and then put it back up.

          You could modify either  of these two tables by adding a relationship and some calculations to compute quantities on hand for each product broken down by location.

          Receiving Line Items::Product ID = Shipping Line ItemsByLocation::Product ID AND
          Receiving Line Items::Location ID = Shipping Line ItemsByLocation::Location ID

          Shipping Line ItemsByLocation is a new table occurrence of Shipping Line Items.

          You could define cQtyShipped in Receiving Line Items as:

          Sum ( Shipping Line ItemsByLocation::Quantity Shipped )

          Define a summary field, sTotalReceived, as the total of Quantity Received.

          Define cOnHandbyLocation as a calculation field:

          GetSummary( sTotalreceived ; Product ID ) - cQtyShipped

          Now you can set up a list type summary report with no body part--just a SubSummary part when sorted by Product ID. Place the Product fields and  cOnHandbyLocation in this sub summary part and make sure to sort your records first by LocationID, then by Product ID. Place a second sub summary part when sorted by Location ID above this sub summary part and place the Location fields in it to add the Location sub heads.

          • 2. Re: Setting up a tricky Relationship. Please help!
            alexmiller

            Thank you! This has pointed me in the right direction.