I am trying to create a purchase order and am looking for help and advice.
Basically, I have my list of 80 inventory items in a pickList table. The fields are picklist:: pickListID (auto enter serial number), picklist::pickListItem, picklist::pickListNumber.
I have a purchaseOrder table with fields purchaseOrder ::purchaseOrderID (auto enter serial number), purchaseOrder::PO_Number (auto enter PO number), and purchaseOrder::pickListID_fk (foreign key).
I have tried various relationships, but I am not getting what I need.
What I want to have happen is this. On any given order we can have up to 5 different items ordered so we need 5 rows to choose the items. I want to add five rows to the PO layout. I want a drop down list for the items field and that will be a value list of picklist::pickListItem (I have this working). On the same layout, I also have a field for picklist::pickListNumber. When an item is chosen, I want the picklist::pickListNumber to auto-populate.
Through various trial and error, I have had it work but with only one 1-line item (or row) on the PO. I need the 5 to be independent meaning when item 4 is chosen, the number for item 4 populates, on the next row down, chose item 5 and the number for item 5 populates. I have also had it working but where new records were being created in the pickList table.
Hope this makes sense. Suggestions or tips would be extremely appreciated.