Purchase Order Help - Kinda Complicated
As a note, I took this setup from the Invoices starter solution (kudos PhilModJUnk for referring that to me!)
I have a Parts Inventory table, a Companies table, a Line Items table, and a Purchase Orders table.
I currently have my Parts Inventory table calling a primary key field _kp_CompanyID from the Companies table to link the Manufacturer of the part (Manufacturer field) to the part itself. I have this field as a pop up menu with the value list comprised of all of the companies using the _kp_CompanyID field as the primary field, and CompanyName as the secondary field. I then only display the name contents in the drop down list (so only the CompanyName is displayed under the Manufacturer field in my Parts Inventory table).
I created a table secondary occurrence of my Parts Inventory table (because of the way my relationships are setup) called Related Parts, to link with my Line Items table via the key relationship of _kp_PartID (from Parts Inventory) to _PartDocNumber in my Line Items table. I then specify other fields in my Line Items table to Look-Up related fields from my Parts Inventory table (such as my LineItemManufacturer field look up Manufacturer in Related Parts when new entry is made in the field "_PartDocNumber").
This works for my other fields, like part name, and part size - which are manually entered text values. However, for LineItemManufacturer, because it looks up from a drop down value list that refers to Companies by their ID, even though they are displayed as a text name (CompanyName is the secondary display field), they are composed by CompanyID. So my LineItemManufacturer field just shows the CompanyID number, not the company name itself. Phew!
So thats the problem. I have tried creating a ManufacturerName field in my Parts Inventory table (that gets replicated in the Related Parts table), that shows the Companies::CompanyName - so its definitely a text field. And I try to have LineItemManufacturer look that up - but it returns a blank space (I think because its not unique to my Related Parts table, its pulling it from my Companies table). I have also just tried creating the same ManufacturerName field right in my Line Items table (RelatedParts::ManufacturerName) - however that is the same result. So, what can I do here??? Thanks.