Background: I am working on compiling a parts database. The parts table is related by field "Part Number (PN)" to all features, drawings, aliases, and each assembly, so each table is indexed by PN. I also made a Table for Orders, here is the problem. An order can consist of parts, assemblies, or both. We often make new custom parts that get a PN on a new order, but not every time. I have tried to relate the Parts Table to the Order Table by Order Number (ON) and include that data per part (e.g. part 0123 has data "Order#: 3, Quantity: 6"). I can enter an Order part by part, but some orders have dozens of parts per assembly.
Ideal Operation: Get order> go to Order layout> Select: #1 Parts/ #2 Assemblies / #3 Both->#1 (Parts) Enter Order part by part and auto update Part table with order number each was used in / #2(Assemblies) Select from value list of assemblies that have been done before and then have a multiplier field if more than one of an assembly is needed or different assemblies are needed per order. Then auto update each part in Parts table to show order used. / # 3(Both) do #2(Assemblies) and then #1(Parts) for individual parts.
NOTE: I don't expect a solution for the ideal process, but I thought it may help for offered solutions.
The problem: I haven't found a fix for adding a full assembly (#2) with the part breakdown on the Order table. I want to avoid a "Many to Many" relationship because that would put me in a back-and-forth motion between finding what parts are needed for the order and adding orders/ assembly numbers to each part and order for anything that wasn't a standard set.
Thank you in advance,