How to create a pivot table with orders and calculate their quantity and amount
Create two fields: "Product naming"(single line) and "Price"(number).
1) Product field (Relationship with "Product naming" field - Products form) DropDown: Many-to-one.
2) Quantity field (Number).
3) Order number (Relationship with "Order number" field Orders form) Multi input with autocompletion: Many-to-many.
Also, you need to add two Formula type fields: Product - quantity and Total.
Product naming field + CONSTANT - + Quantity field
Price field (Product form) * Quantity field (Pivot table form)
1) Order number field (Number).
2) Select products field (Relationship with Pivot table) Multi input with autocompletion: Many-to-many
Very important! You need to select the "Product - quantity" field to display. Find this option in the Relationship settings tab in the Select products field editing.
1. Add records to the Products form
2. Go to the Orders form - Records menu and press "add a new record" button.
3. Enter the Order Number and click on the "Add" button near the Select products field.
4. We select the Product and enter the quantity number. Thus, we add a new record to the Pivot table.
You can add one more product and select the required ones.
5. Click on the "Create" button to add a new record to the Orders table. As a result, the table with orders looks like this:
6. Go to the Pivot table Records menu.
7. Group records by order number.
8. To calculate the order total, open the "Total" formula field editing in the Pivot table form. Select "Page total" in the Total and Averange tab.
As a result, we have the following table:
We hope you'll find this article useful!