Create a form to add multiple record

[Trung_Nguyen]

I have two table:

• Invoice
• Product

Is there any way to create a form where user can enter Customer, date (INVOICE table) and choose one or many Products for this invoice?

[Aernout_de_Beau]

Hi Trung,
Yes, this is possible. You would first need to decide for which table you would create the form. In this table, create a field of the ‘Link to [table X]’ type (referencing the other table) and then create the form for the first table.

If I understand your situation correctly, the form should be created in the Invoice table. This means that you would start by creating a Link field that points to the Products table. Once you have created the form, users will be able to select one or more products from the {products} field (which has a + button that, when clicked, opens a dialogue that allows the user to select products).

Next, if you would like to also have the prices for the selected products available in the invoice table, you would need to create a field of the ‘Lookup’ type (or perhaps the ‘Rollup’ type) in the Invoice table (pointing to the {product} field that references the other table). This field with prices would not be shown in the form, but that is no problem, since the prices will be automatically filled in. Then, looking at the grid view of your Invoices table, you would see the prices. These could be displayed
-at a later time- when creating your invoice documents (using the Page Designer block or a service like Integromat/Zapier) that will be able to display the prices.

Good luck!

[Trung_Nguyen]

First of all, thank you very much for giving step by step instruction.

It is just that when choosing products for the Invoice, the quantity should be already there to select appropriate record. What I would like to achieve is to select a product and set qty in that step. Something like a form inside a form.

I have been searching for solution but it seems that this is not feasible with Airtable alone.

[Justin_Barrett]

Correct. This can’t be done with Airtable alone. Its form system is pretty basic. I wrote this hypothesis on another reply recently, but my hunch is that most ordering systems actually store data in more than one table behind the scenes. There’s a orders table with general information about each order (order date, a link to a customer record in a [Customers] table perhaps, a shipping method, etc.) and then the line items of the order—each with a product and quantity—are actually records stored in a different table, and linked to the order in the [Orders] table.

This kind of storage scheme could work within Airtable, but the form to collect the order data can’t be an Airtable form because it’s not designed to work with multiple tables. A form in Airtable is a view, and a view is tied to a single table. An ordering system like this would require a more detailed form system for data collection, and then a service like Zapier or Integromat to parse that data and store each item in its relevant table: general order info in an [Orders] table, customer details in a [Customers] table, order line items in a [Line Items] table, etc.

[Aernout_de_Beau]

Ah, yes… setting the quantity would not be possible in this approach. I may have approached the question too narrowly. Granted, using additional tools to build your forms will certainly make it easier to develop the solution you want.

In fact, I prefer working with a combination of Airtable, Integromat and Cognito Forms for complete solutions (though Zapier is also great and many nice forms builders are available).

The reason why I prefer Cognito Forms over other forms builders, is that it does a great job in offering you design flexibility without making things too complex or time-consuming. The forms just look neat and beautiful!

Please note, Justin’s suggestion about parsing forms data can be extrapolated. Indeed, after people fill in a form, you would parse the collected data (using Integromat/Zapier), resulting in the creation of new records in several tables of Airtable. But consider that you would also use the reverse setup. Before the form can be filled in, you would want data to be retrieved from Airtable in orde to display it in the form. This would likely require parsing, as well.