How To Number Line Items

Scenario

There are a few scenarios where you may want line numbers to start over from "1" for a group of records. For example, in a Purchase Orders app, you have one or more Line Item records that are associated with a single Order record. For each new Order, any Line Items added will start at one and increment by a value of one for additional Line Items. The Line Item incrementation will start over from one for each new Order.

Requirements

If this is your first time creating an app, you'll need to know some basics about adding tables, fields, pages, and views. You can start by reading our Builder Basics section.

Other good resources can be found in our About Your Database and Working With Pages article in our knowledge base.

This guide will use equations, formulas, and record rules.

Steps

Add Tables & Fields

In the Data section of your app, add a new table to your app called Orders.

Next, add a new table to your app called Line Items. Then, add a new number field to your Line Items table called "Item Number".

Now, you will have two tables: Orders and Line Items. The Line Items table will have a field called "Item Number":

Add Connections

Next, the Orders and Line Items tables need to be connected. Go to your Line Items table and add a connection field to Orders by clicking on the green "+" button in the Connections bar on the right side of the Builder.

Use the default one-to-many connection type. Each Order record will connect to many Line Items, and each Line Item will connect to one Order.

Add Formulas & Equations

In your Orders table, add a count formula to count up the Line Item records connected to each Order record. Call this field "Line Item Count".

In your Orders table, add an equation field called "Current Count" that calculates “Line Item Count + 1”. This equation will use the Line Item Count field in the Orders table.

Add Pages

From the Pages section of the Builder, add a new page by clicking the green "+" button above the page list. Choose Orders as the source table and add the following views:

A grid to display Orders
- Details to view more Order fields.
- Grid to display Line Items connected to the Order.
- Form to add a connected Line Item

Add Workflow

Go to the Order details page you just created in the last step, and open the editor for the form view that adds a new Line Item. Click on the "Rules" tab and go to the “Record Rules” section.

Add a new record rule by clicking the "Add Rule" button and setting the following values:

Action: update this record
When: every form submission
Value: Set Item Number to - a connected value - Current Count:

Now, your Line Item records will be numbered separately for each Order record.