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 contains the name of the product
Price - This is the unit price of the item
Status (optional) - This is 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.
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.
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.
* Click 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.
Order
Order 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. This 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 should be created in the Airtable which means that if a record is created in the Airtable, it might still be in the preliminary stage. This field will be an indicator 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 in the later part of the tutorial.
Order Details* - This is a linked record field that is connected to the Order. This is automatically created when you created 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 record.
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. 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 do the purchase. This can be a linked records field but you will need to add a 4th table in your base.
*This toggle should be turned on.
**This is the setup for Order Price field.
***This is the setup for the Total Number of Items field.
Now that the Airtable base is ready, we can now set up our form.
miniExtensions Form
In the Form Settings section, the Form Table should be the Order table.
These are the fields needed to be available in the Form Fields section. Mark them as required.
Click on the Order Details field. Go to Record Finder Options and turn off Show record finder to ensure that a single record in the Order Details table cannot be connected to multiple records in the Orders table. By having this set up, the user cannot link an existing Order Details record in the purchase order that is about to be created.
Scroll down and go to Record Titles section. Use Item Name field in Custom field for Record title value. So instead of showing the Order Details Number in the form, it will show the Item Name instead to make it more presentable.
Close the modal and click this button in the Form Fields section.
These are the fields that we need in the Form Fields section of the child form. Mark them as required.
The step is optional. The purpose of this is to be able to show more information about the product. Click on the Item field. In Creating and Editing Records section, make sure that only Allow users to edit linked records toggle is enabled. Click on the Form Settings button afterward.
Click on Bulk Actions and select Mark all read-only.
Close the modal for the child form setting of Item. Scroll to the Filter Records section and set this condition. This will show products that are purchasable.
Close the modal to get back to the child form setting modal for the Order Details field. Click on the Advanced section then go to Formulas (Computed Fields). Turn on Show formula results to users (Compute Mode). The purpose of this is to let the Order Detail Price field process calculation.
Close the modal to get back to the main form settings. Click on the Advanced section then go to Formulas (Computed Fields). Turn on Show formula results to users (Compute Mode). The purpose of this is to let the Total Number of Items and Order Price field process calculation. We also need to set the Confirmation Checkbox Field and set the Finalized field. This will automatically check this field upon form submission.
And that's the basic setup for a form that can create a purchase order.