It would be most helpful to post screenshots, print(s) as PDF of your script(s). It helps analyze what is going on.
I'm going to "guess" that you are not using a "lookup" when select a product for an Order/Invoice/Estimate/???
The user can select a product by various means. It would also help us to know how you do that. A good way is to have field and the "ID" of a product (primary key) is entered into a foreign key of the record you are creating. Then you have other fields that are "auto-enter with a lookup" to copy over other values (name, cost/price, colors, etc.).
The method of 'copy-over' is so that historical data is added with a new record. Then if you need to go back and change the price, for example, of a product, ONLY the new records will "lookup" the new price.
HTH! And feel free to reply with more questions.
I wanted to check in and see if you still needed help on this particular question. If so, reply back and we can continue the conversation.