Setting Up Relationships for Receiving Multiple Shipments Per Purchase Order
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.