Shipments: One to many relationship and selection via portal

[mpau]

I've got a FM database set up to track orders. Currently, there's a basic one (order) to many (orderline) relationship that's used to create order. I'd like to add shipments into the mix. Each order can have multiple shipments (not all items necessarily ship at once) so, I've created a shipment table that has a one (order) to many (shipment) relationship with the order table. I am able, via a portal, to pull up an invoice number in the shipment record and see all the items (related orderlines).

What I'd like to do is this:

create a shipment for an order and then be able to select which orderlines/items I'd like to ship for that particular shipment. In other words, I'd like to have that shipment record state which of the order's items were shipped.

I'm thinking that ideally I'd like to be able to select what should ship via a drop down which would restrict the selection to what's in the order and hasn't already shipped. Or via portal view where I can click which related order lines to ship, that haven't already.

Anyone have any ideas or tips about how to do this?

Thanks in advance!!

[bcooney]

Do the order line items have quantities? And if so, can the quantities be split across a shipment?

If they don't have quantities that split across shipments, then you can simply add a ShipID to the OrderLI.

Otherwise you'll need a ShipLI table, and each record will have an OrderLI_ID.

Edited October 31, 2009 by Guest

[mpau]

Thank you for your help! I've spent hours on this and am somehow completely stuck. Can't wrap my mind around it the right way.

There are quantities for the order lines, but these won't be split across shipments.

If I'm understanding you correctly, you're saying I can simply add a serial ID # (ShipID) to each orderLI?

Assuming I'm right, wouldn't this give each orderline its own unique shipment? While the individual orderlines themselves won't be split across multiple shipments, they will often be batched together. Meaning several orderlines will ship in one shipment. So I'd assume I'd want them to share a ShipID rather than each have their own.

Currently, I do have a Ship Table set up and connects to the Orders table via the OrderID which in turn connects to the Orderline table. I tried a different configuration, connecting the Ship table directly to the orderline table via the ShipID (parent table being the Ship table). However, I wasn't able to get any information from the orderline table to populate on the ship table using this configuration. Somehow, it seems to me that I'd need to connect the Ship table to both the Order and Orderline tables but this isn't allowed with the structure that already exists - without copying one of the already existing tables.

Ideally, I'm thinking I'll need a Ship table. As with each shipment I'd like to record what orderlines shipped along with various shipment information (carrier, cost, shipdate, etc).

Does this change your recommendation?

[bcooney]

"So I'd assume I'd want them to share a ShipID rather than each have their own."

-Yes, that's what I meant. You don't need another LI table.

Yes, you do need a Ship table. User creates New Shipment, and specifies the Order (that is, if a shipment is related to only one order). Then, from a portal of OrderLI from that Order that do not have a ShipID, he picks which items will be on this Shipment. The script attached to that button simply populates the ShipID into the OrderLI.

[bcooney]

Quick and simple demo.

OrderShip.fp7.zip

[mpau]

Wow! The demo is great. Thank you so much.

Looks like you're using relationships I'm not familiar with so it'll take me a bit to figure this out.

Couple of questions:

What kind of tables are the two tables named shipment_OrderLI~thisSHip & shipment_OrderLI~unshipped? They don't show up under the table list. If I'm going to create comparable tables in my DB what action do I choose?

[mpau]

OK figured out that I'm looking a table occurrences rather than tables in regards to the two tables I mentioned above. So, how do I create multiple occurrences of a table?

[Vaughan]

The relationship graph ONLY shows Table Occurrances.

Use the icons at the bottom to add and duplicate TOs.

[mpau]

Figured everything out. I was able to easily adapt your demo to fit my DB and it works!

Thank you so much bcooney! I would definitely not have figured this out on my own.