In this tutorial, we will make a simple form that can create a purchase order. We will use the Invoice Form / Order Form template on the Create Extension page for this.
There are two main ways to use this template:
Have the Form auto-generate the required tables
Use existing tables
1. Auto-generate Tables
This is the easiest, and therefore recommended, option. After selecting Invoice Form / Order Form from the Create Extension page, simply turn on Generate Tables. Once you click on Create, we will generate all required tables for you. The names of the tables will be based on what you set as the Title:
{Title} Invoice Table: This is where the actual invoice will be stored. The form creates records in this table.
{Title} Products Table: This is where you will need to add the products that your customers can choose from.
{Title} - Line Items: This is where the form will store each line item from your invoices. This table is necessary to allow for quantities to be added for each product.
When using this auto-generated table set up, most of the configuration is done for you out of the box, both on Airtable as well as on miniExtensions.
The form now contains a field where customers can add new line items. For each line item, they can pick from your list of products, and add a quantity.
There are some features that we cannot provide out of the box for you, due to limitations put in place by Airtable. These can be added easily however, as explained below.
2. Use existing Tables
To start, we need to have two tables in our base:
Products - The table that contains the list of items that can be purchased
Invoice - The table where the purchase order will be saved
We will generate the Line Items table for you automatically, which is where the form will store each line item from your invoices. This table is necessary to allow for quantities to be added for each product.
Let's go through the important fields that you need to set up for each of the tables.
Airtable setup
Products Table
Item Name - This is a single-line text field that contains the name of the product
Price - This is a currency field for the unit price of the item
Invoice Table
Invoice Number - It makes sense to have an "Invoice Number" field set as the primary field of the table. It could be an autonumber field to ensure that each row has a unique value, which can be used to distinguish one invoice from another.
Record finalized - We add this checkbox field automatically. For the formula and the rollup fields to work (see below), the records must be created in the Airtable. This can cause conflicts because some records in Airtable might still be in the preliminary stage and can get mixed with the rest. To solve that issue, this field will indicate if the purchase order is finalized. It will be automatically checked when the form is submitted.
Line Items - We add this linked record field connected to the automatically generated Line Items table.
Invoice Total - This is a rollup field that will sum up the Line Item Price of the connected Line Item records, calculating the total price for the invoice. You can add this later, once the Line Items table has been generated.
Now that the Airtable base is ready, we can now set up our form:
miniExtensions Form Setup
After selecting Invoice Form from the Create Extension page, select your Invoice and Product Tables. Once you hit create we will generate the Line Items table for you based on the Title you have set for the form. You can always rename the table later.
The form will now be set up with sensible default settings for this application. You can add further functionality as explained below:
Additional Features
Display the cost of each line item
Add a lookup field for the Product Price to the Line Items table.
Create a formula field "line item cost" in the Line Items table that multiplies the product price with the quantity field.
Display a total for the entire invoice
Add a rollup field for the "line item cost" field to the Invoice table. The formula for the rollup should default to SUM(values). This will add up all the line item costs to provide an invoice total.
Add the rollup field to the miniExtensions form.
Filter Products based on status
Add a single-select field to the Products table that works as an indicator that the item can still be purchased (e.g. in stock). This field can be used for filtering in the Products field in the Line Items child form.