Log In

Log In

 

Creating a pivot table with orders

How to create a pivot table with orders and calculate their quantity and amount

You can create a pivot table with orders using Relationship.

For an example, we'll create three forms: Orders, Products and Pivot table.

Products form

Create two fields: "Product naming"(single line) and "Price"(number).

Pivot table form

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 - quantity formula:

Product naming field + CONSTANT - + Quantity field

Total formula:

Price field (Product  form) * Quantity field (Pivot table form)

Orders 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.

Adding records

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!


Please, login to QuintaDB to be able to post comments
Share the discussion in the social networks: