In this tutorial, we will make a simple form that can create a purchase order. To start, we need to have 3 tables in our base:
Products - The table that contains the list of items that can be purchased
Order - The table where the purchase order will be saved
Order Details - The table that contains a list of items that are included in the purchase order
Let's go through the important fields that you need to set up for each of the tables.
Products
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
Status (optional) - This is a single-select field that works as an indicator that the item can still be purchased in case you do not wish to delete the product from the Airtable. This field can be used for filtering.
Order Details
Order Details Number - In this demo, it is set as a primary field of the table. It is an autonumber field to make sure that each row has a unique value for easier tracing. You can also use a formula field (eg. Order Number + Product Name).
Item - This is a linked record field that is connected to the Products table. Make sure you can only link one product for each row by clicking on the down icon on the right side of the field then Edit field. This toggle only shows up for the linked records field. Toggle should be turned off.
Item Name - This is a formula field. In the formula box, you just need to put {Item}. We need this field to make our form presentable. It will copy the data from Item and enable us to use it as the custom record title for the order details field.
Quantity - This is a number field that we will use later in a formula to compute the price for the order detail.
Item Unit Price - This is a lookup field to the field named Price in the Products table.
Order Detail Price - This is a formula field that will multiply Quantity with Item Unit Price. The formula you need to use is {Item Unit Price} * Quantity. For example, the unit price of the item is $50 and you need to purchase a quantity of 3. This formula field will show a value of 150.
Order* - This is a linked record field that is connected to the Order table. Make sure you can only link one order for each row.
Order
Order Number - In this demo, it is set as a primary field of the table. It is an autonumber field to ensure that each row has a unique value, which can be used to distinguish one order from another.
Finalized - This is a checkbox field. For the formula and the rollup fields to work, the records must be created in the Airtable. This can cause conflicts because some records in the 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. The setup that will trigger this will be discussed later in the tutorial.
Order Details - This is a linked record field connected to the Order. This is automatically created when you create the Order field in the Order Details table. Make sure that it is set up to support linking multiple records:
Order Price - This is a rollup field that will sum up the Order Detail Price of the connected Order Details records, calculating the total price for the order:
Total Number of Items - This is also a rollup field. This is an optional field that contains how many items are included in the purchase order. Its purpose is to show the total number of items in the order. If your Order Details have 1 record connected to the purchase order, but the quantity of that record is 2, then this field will show a value of 2.
Supplier Details - To make it simple in this demo, this field is a long text field where we can write down the details of the supplier where we will make the purchase. This can be a linked record field, but you would need to add a 4th table in your base.
Now that the Airtable base is ready, we can now set up our form.
miniExtensions Form
To get started, create a miniExtensions Form in the Order table.
These are the fields that need to be available in the Form Fields section. Mark those with a red asterisk as required to match the following screenshot:
Click on the Order Details field. Go to Record Finder Options and select 'Hide record finder button' as the Record finder mode. This is to ensure that a single record in the Order Details table cannot be connected to multiple records in the Orders table. With this setup, the user cannot link an existing Order Details record to the purchase order that is about to be created.
Go to the View section and set the Record title option to 'Select another field'. Then, set the formula field 'Item Name' as the Custom field for record title value. This will show the Item Name instead of the Order Details Number in the form to make it more presentable. Close the modal and click the 'Order Details Form' button in the Form Fields section.
These are the fields that we need in the Form Fields section of the child form. Mark those with a red asterisk as required to match the following screenshot:
This step is optional. Its purpose is to show more information about the product: Click on the Item field. Then, in the Create & Expand Records section, make sure that the 'Allow users to expand (or edit) linked records' toggle is enabled. Then, click on the blue Edit Form button below Form for expanding (or editing) records.
Click on the Bulk Actions hamburger icon and select Mark all read-only from the dropdown list.
Close the modal for the child form and select the Item Field in the Order Details form. Enter the Filter Records section and add the following condition: Where Status is Available. This will show products that are purchasable.
Close the modal and go to the Save & Continue section of the Create Purchase Order Form. Enable 'Save & Continue Mode' to show computed formula results to users. This will allow the Total Number of Items and Order Price fields to be calculated when the products and quantities change.
We also need to configure the Confirmation Checkbox Field. Set the 'Finalized' checkbox field there. This will automatically check this field upon form submission.
And that's the basic setup for a form that can create a purchase order!
Your child form will look like this:
After adding a couple of items, your form will look like this:
If you have any questions about this or any other extension, please let us know. You can contact us through the customer support chat in the bottom right corner of the screen. We'd be happy to help!