I have 2 tables: Shipments and Orders. I can have many orders on one shipment.
When the user enters data into the Orders table, I need to check and see if a shipment to this area already exists. I use a temporary key like LAX0831 for routing purposes. If a shipment exists, this new order becomes part of a consolidation with the existing shipment and gets assigned to that ShipID. If a shipment to this area does not exist, I need to create one automatically, otherwise it will not allow me to add the record because of integrity. Shipment info needs to be generated automatically requiring no user intervention. Any suggestions on the best way to go about this?