Retool db add product lines to order tables

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" :slight_smile: 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:

  join orders using (order_id)
  join products using (product_id)
  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