I am trying to import purchase orders into odoo and have sorted out my problems for the most part however i am unable to update the list of products using the external ID. i can use the database ID or the name of the product however if i try to use the external ID i get this error:
insert or update on table "purchase_order_line" violates foreign key constraint "purchase_order_line_product_id_fkey" DETAIL: Key (product_id)=(644) is not present in table "product_product". between rows 2 and 4
Could someone please explain to me why this is happening and what i have to do to fix it?
I guess the problem is that you are using the External XML ID of the product.template records instead of the product.product ones.
If that was the case, probably your product.product records are not going to have an External XML ID as they were created by hand in the database, or through the importation of their respective product templates, or the addition of attributes and attribute values to these templates.
If you have that problem, you can generate External XML IDs for your products only by exporting them first. If you export any record without an External XML ID, this one is automatically generated to be in your exportation file under the id column. So a workaround will be to select all the products (or better only the ones you are going to use), export them, obtain their External XML IDs from the exported file, and use them to import your purchase.order.line records.
Problems of this solution
The External XML IDs you obtain from a database cannot be the same as the ones you get from other different database in spite of they have similar products, so the importation file generated by this method for a specific database could not work for other databases.
External XML IDs are stored in the database, therefore if you generate thousands of them, the database will grow depending on how many products you export.