Orders and products with many to many relationship

[ernesto84]

Hello everyone!
I'm new to AirTable but I have 20+ years of experience with SQL, but I'm stuck with the interface design here.

I'm helping a friend setting up a project to keep track of orders and AirTable seems to be the perfect place.
I have created a "product" table, an "order" table and a junction table "order_product" for the many-to-many relationship between them.

Now I need to create a form or an interface to execute the following process:

1. I need a page where I can choose the client for the order and add multiple products with quantity
2. When the form is submitted it has to create a new record for the order
3. When the form is submitted it has to create a new record for each product in the order (linking the new order that has been created)

Any idea on how to do that? Thank you so much in advance and greetings from Italy! 

[Sho]

How about separating the table into Order Heading and Order Detail?
Order Detail is one record per item and quantity.
Both can be edited on one screen in the list view.

However, Airtable's forms are poor and not suitable for this type of input.

[ernesto84]

Thank you for your solution, I'm definitely going to try out those form builders.

Creating a custom web app would require extra time and knowledge, I recommended my friend to use AirTable to be up and running ASAP.

Can you confirm that there is no way to insert many-to-many records directly with native AirTable interfaces?

[ernesto84]

Hello Sho,
thanks for your reply.

The model is correct from a SQL p.o.v. what I need is just the interface to let people place orders all in one step (oder and products), without the need of creating an empty order first and then add to that specific orders one product at the time.