4 Replies Latest reply on Sep 29, 2014 12:14 PM by ReidLarson

    Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order

    ReidLarson

      Title

      Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order

      Post

      I'm using FileMaker Pro Advanced 13 and am setting up a database for ordering and receiving inventory. The Purchase Order table is linked to its Line Items by __kp_PO and _kf_PO and Line Items appears as a portal on Purchase Order. Purchase Order is also linked to Receiving the same way. This part works great.

      The issue is that Receiving, in a separate table occurrence, is linked to the same Line Items table by _kf_PO (which also appear as a portal on Receiving). This would normally work except sometimes a PO isn't fulfilled in one shipment. My hope was that there would be a way to have a field for Quantity_Received with different values depending on how they were related to Receiving.

      I've attached a diagram showing these relationships.

      I thought about using a repeating field that hides all but one repetition depending on the Receiving record, but wasn't sure the correct calculation and it seems like a messy solution with a theoretical limit on the number of times one PO can receive shipments.

      I then tried creating a separate table, Quantity Received, just to hold the field, but the relationship always ended up creating a loop or only being able to be linked to either the specific Line Items or Receiving record, not both at the same time. The red lines on the diagram show this issue.

      If I have to, I'll just create a new Receiving Line Items table and create new records over there any time a shipment comes in, but since this was already built, I wanted to see if it was doable before tearing it out.

      Screen_Shot_2014-09-29_at_9.17.22_AM.png

        • 1. Re: Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order
          philmodjunk

          It would seem that Quantity Received IS your Receiving line items table. You may want to set up a table of "shipments" with the receiving line items table linked to both it and the PO line items table.

          • 2. Re: Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order
            ReidLarson

            It would seem that Quantity Received IS your Receiving line items table.

            Yes it could be set up that way, and that's my next step if there isn't a good way to keep it how it is. I'm working with a database that was partially made and handed off to me. This is part of deciding what parts are functional and should be kept and what parts need to be scrapped and redesigned.

            You may want to set up a table of "shipments" with the receiving line items table linked to both it and the PO line items table.

            I think that's essentially what the Receiving table is.

             

            • 3. Re: Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order
              philmodjunk

              My point is that it appears to already be "set up that way".

              What I am suggesting would look like this:

              POReceiving------<Receiving----<ReceivedLineItems>----POLineItems>-------POs
                                                 |
                                                 ^
                                   ReceivingPOLineItems-----ReceivingReceivedLineItems

              Note: Tutorial: What are Table Occurrences? colored the same here have the same data source table.

              POReceiving::__pkPOID = Receiving::_fkPOID
              Receiving::__pkReceivingID = ReceivedLineItems::_fkReceivingID
              POLineItems::__pkLineItemID = ReceivedLineItems::_fkLineItemID
              POs::__pkPOID = POLineItems::_fkPOID
              Receiving::_fkPOID = RecevingPOLineItems::_fkPOID

              And this last one is special:
              ReceivingPOLineItems::_fkgReceivingID = ReceivingReceivedLineItems::_fkReceivingID AND
              ReceivingPOLineItems:__pkLineItemsID = ReceivingReceivedLineItems::_fkLineItemID

              "Allow Creation of records via this relationship" would be enabled for ReceivingReceivedLineItems in this relationship. _fkgReceivingID would be a global field (hence the 'g') and set with the current value of Receiving via an OnRecordLoad trigger.

              For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

              This allows you to set up a portal to ReceivingPOLineitems with a QtyReceived field from ReceivingReceivedLineItems right in the portal row. By entering a quanty received into that field, you automatically create a new record in ReceivingReceivedLineItems if a related record does not already exist so simply recording quantities received builds the needed set of received line items and quantities for the current record in Receiving. When the next shipment arrives for the same PO, you create a new record in Receiving, link it to the correct PO and then you see the same list of POlineITems in the portal but with empty QtyReceived fields where you can now log the reception of line items received from this shipment.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order
                ReidLarson

                My point is that it appears to already be "set up that way".

                It looks like that global field was the missing piece. I couldn't figure out a way to associate the PO Line Items with many Receiving records. Thanks.