How to Number Line Items

In this article, we will guide you through an example scenario where a workflow is required to assign line item numbers to items associated with orders.

Use Case

There are several scenarios where it may be necessary for line numbers to reset to "1" for a group of records. For instance, in a Purchase Orders app, there might be multiple Line Item records associated with a single Order record.

With each new Order, the Line Items added will begin with one and increment by one for additional Line Items. The Line Item numbering will reset to 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

Adding Tables & Fields

To set up your app, navigate to the Data section and create two tables: "Orders" and "Line Items".

In the Line Items table, include a number field named "Item Number".

Adding Connections

To establish a connection between the Orders and Line Items tables, navigate to the Line Items table. Select the "+" button located in the Connections bar on the right side of the Builder to add a connection field to Orders.

Choose the default one-to-many connection type. This means that each Order record will be connected to multiple Line Items, while each Line Item will be connected to only one Order.

Adding 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 the Orders table, you'll also need to create an equation field titled "Current Count" that performs a calculation of "Line Item Count + 1". This equation will utilize the Line Item Count field within the Orders table.

Adding 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 view to display Orders that includes additional links to:
- Details view to view more Order fields
- Grid view to display Line Items connected to the Order
- Form view to add a connected Line Item

Adding Workflow

Navigate to the Order details page you created in the previous step and access the editor for the form view that allows you to add a new Line Item. Proceed to click on the "Rules" tab and navigate 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
Values: Item Number > to a connected value > Orders > Current Count:

Now, the Line Item records will be assigned separate numbers for each Order record. Congratulations, you have successfully set up this workflow!