English Flag English Español Flag Español Deutsch Flag Deutsch Italiano Flag Italiano Polski Flag Polski Nederlands Flag Nederlands Português Flag Português Français Flag Français 中文 Flag 中文 日本語 Flag 日本語 हिन्दी Flag हिन्दी اللغة العربية Flag اللغة العربية Русский Flag Русский Українська Flag Українська עִברִית Flag עִברִית Ελληνικά Flag Ελληνικά Türk Flag Türk Latvietis Flag Latvietis Dansk Flag Dansk Norsk Flag Norsk Íslenska Flag Íslenska 한국어 Flag 한국어 Suomen Flag Suomen Gaeilge Flag Gaeilge Bahasa Melayu Flag Bahasa Melayu Svenska Flag Svenska Čeština Flag Čeština
User IconLog In
English Flag English

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!