App to add multiple line items to an order

[Trevor_Petach]

• Goal: I am trying to build an app where users can create and submit orders. My backend database structure is similar to the one described here. I have an orders table, with one row per order, and an order_items table, with one row per item in the order. I also have a products table with all possible products that could be ordered, and a customers table with one row per customer.

I would like a user of my app to be able to submit an "order" form, with an order name (free text input) and a customer_id (dropdown based on existing customers in the customer table) and a list of order items (products and quantities). When the user submits the form, I would like to add a row to the orders table and several rows to the order_items table, depending on how many items are in the order.

I know how to create a basic form with a text input field for the order name and a dropdown for the customer.

I also have all of my tables created in postgres, with appropriate foreign key relationships.

What I don't know how to do is create an arbitrarily long list of order items in my form, each with a product (selected from a dropdown list of products) and a quantity (numeric input).

I am envisioning two buttons:

1. an "add item" button that would add another two input boxes to my form (one with the product dropdown and a second with the quantity input). This button would not write any data to postgres.
2. a "submit" button, which would both insert one row into my orders table with the order name and customer_id and insert several rows into my order_items table (depending on how many items had been added in the form) with the product_id, quantity, and order_id. I wouldn't know what order_id to populate in the order_items table until the new row had been created in the orders table, since the order_id would need to be the newly created serially incrementing integer primary key for the orders table.

• Attempts: I tried to use list views and repeatables, but was only able to list or repeat data that already existed in my database, not queue up multiple order_items to insert.

Can anyone recommend an approach?

Thanks in advance!

[Jack_T]

Hello @Trevor_Petach!

Just read over your post as well as the one you linked it to. Going to work on building out the functionality you are looking to implement to share with you, just want to double check with you on the DB schema and features that you are still looking to implement.

For the "arbitrarily long list of order items in my form" would be it fair to call this the 'order summary' section of the order form? Containing a string to represent the item name and an integer for the quantity?

What I am imagining for the approach would be temporarily storing the data for all the items being ordered and their QTYs in the retool app's frontend state, then running queries to create a row for each in the 'order items' table AFTER the order has been created so that the order ID/foreign_key is accessible for the order_item rows.

I believe such a process can be done using the returned value that the Postgres DB will provide on a successful row creation, which you can then chain together to additional queries using the 'on success' handler in the query's setup to then thread in the row ID as a foreign key to the corresponding order_item rows.

For table schema, would this be close enough for me to build a mock up?

customer_table
id | name |

order_table
id | order_name | customer_id |

order_items_table
id | order_id(FK to order_table) | product_id(FK to product table) | qty |

product_table
id | name |

[Jack_T]

It looks like I was able to set up functionality for an app to work as you described. I am able to create orders and have rows or order-items with the proper foreign key pointing to their parent order.

For adding items/products I used a container with a submit button and two input fields, OUTSIDE of the primary form(being used for customer from a drop down and order name from a text input).

On the container button click, I ran a script to concat together a javascript variable I created called 'currentOrder'. The script looks like this and uses the .setValue method to assign the JS variable to be the previous value concated together with the new data being added which is an array wrapping JSON with the string of the product and the quantity count. While also clearing those values on button click so that the next item in the order could be entered in.
currentOrder.setValue(currentOrder.value.concat([{product_id:product.value, quantity: quantity.value}]))

I then had to do a little bit of a work around to format the data, as the PostgresDB needs the product_id to be an integer and not the string value taken out of the containers dropdown options. So I needed to change string of "chicken" or "steak" into a 1 or a 2 depending on the row id in my product table.

To do this I created another javascript variable, an object with keys of strings and values of the IDs, this was easy for me to demo with only two options but if you have a large number of order items you should map over the data that you query from the products_table of your database. My "map product" function looked like this
currentOrder.value.map(order => order.product_id = productList.value[order.product_id] ) return currentOrder.value

So now that the javascript query is set to convert my javascript variable of currentOrder into an object of {product_id: int, qty: int} I can use it in a script that I will run on success of the main form used to create an order, combined with a bulk insert query to add the product rows to the product table, after the order id is created by the form's first query.

Here is my create order query which uses my form1 to create the order_name and customer_id.

Then on success of that query I run the following script to map over the items in the current order, grab the ID from the query creating the order and then trigger the bulk upload query while passing it the additional scope of the order data to add to the table with all of the order items with their quantity and the order_id they correspond to!

let mapped = await mapProduct.trigger()
let orderId = create_order.data.result[0].id

let finished = mapped.map(order => 
  {order.order_id = orderId; return order}
)

bulkOrderItems.trigger({
  additionalScope: {finished: finished}
})

Inside of the bulkOrderItems query, I pass into the 'array of records to insert' field {{ finished }} which will appear red in the editor because this value has not been defined yet, but it will work when that query is triggered by the success script from the create_order script.

Hope that helps and let me know if you have any other questions! I can also link the project I built/DB used as well.