How to find and select products on invoices through joined table
I have been searching online but can't seem to find the answer. Here it is:
I have two tables, products and orders. Since 1 product can appear on multiple orders and 1 order has multiple products, I created a third table joining the two, named 'line items'. I have populated the table 'products' with some products. Now i wish to set up a lay-out for 'orders': when i create a new order, i wish to select products from the product table How can i do that?
1. Products (has primary key 'Products ID')
2. Line items (joined table, has foreign key 'Product ID' and foreign key 'Order ID'
3. Invoices (has primary key 'Order ID'.
I imagined with a value list, but it doesnt seem to work, maybe because there is no order yet, so no relation can be made? How do i fix this.
Thanks for your help