Hi.
I am just started with retool, so maybe a stupid question. But if i have two tables products and orders. I wish to add some product lines to an order.
is it possible to add a field in order table than contains more product lines?
Hi @klausb - welcome!
The simple answer is "yes" 
but how you do so will depend on how you design your database tables.
For a much longer answer, one common design would be to have an order_details table that indicates which of products are on each order by association to the products and orders tables. Very simplistically:
products table:
| product_id | product_name |
|---|---|
| 1 | foo |
| 2 | bar |
| 3 | buz |
orders table:
| order_id | order_name |
|---|---|
| 8 | order A |
| 9 | order B |
order_details table with foreign keys (labeled "fk" below) to orders and products
| order_detail_id | order_id (fk) | product_id (fk) | quantity |
|---|---|---|---|
| 1000 | 8 | 1 | 10 |
| 1001 | 8 | 2 | 12 |
| 1002 | 9 | 2 | 5 |
| 1003 | 9 | 3 | 8 |
This way, if you want to know what is on "Order B" and for what quantity, you would query:
select
order_name,
product_name,
quantity
from
order_details
join orders using (order_id)
join products using (product_id)
where
order_id = 9
This query would return:
| order_name | product_name | quantity |
|---|---|---|
| Order B | bar | 5 |
| Order B | buz | 8 |
Instead of coding the order_id directly in the query, you can use a dropdown select component (pass the result of select * from orders to the component and use order_name as the label and order_id as the value) where you choose the order you want and then pass in {{dropdownComponentName.value}} to the query.
You could then add products to "Order B" by (for example) triggering a form from a button in a table showing the details, or you could create a brand new order record (perhaps by clicking a button outside the table) with a single order_details record where product_id is NULL and then edit/create new order detail records as before.
Hope that helps, and happy building!
p.s. One other approach (that I personally don't like, but your mileage may vary) would be to add a field in the orders table that is a list/array of the products on the order, but this isn't a typical approach. If you wanted to add quantity for each product as well, you would also need to either add a "quantity" list/array field, and that would have to be coordinated with the products list/array, or you would need to turn the products list/array into a JSON which would have the details. Either way, that would get messy fast (IMHO).
1 Like