Scenario

You want to begin the count in an auto increment field from a number other than 1.

Knack users often already have record ID numbers stored in an existing database or spreadsheet and would like to use the next ID number in sequence.

Say you want to add new Purchase Order IDs in sequence from the last number you have on file. We’ll use 3500 in this example.

Requirements

For this example, we will be using a Purchase Order app. You will need:

1. An Auto Increment Field in the Purchase Order Object.

2. An Equation Field in the Purchase Order Object.

3. A Number Field in the Purchase Order Object. This field is only required for the second recipe: import to set auto increment to any number. 

Recipe

Set Up the Object to Start Auto Increment from Any Number

First, you need to add an auto increment field to the Purchase Order Object:

Once you’ve saved the auto increment field, you’ll need to add an equation field to combine the existing ID number with the auto increment field.

In the equation field box, entering the following formula (replacing 3500 with your own number of choice):

Once you’ve added the equation field, update your pages to show your new PO number field:

After you’ve added the PO number field to your pages, you can verify new Purchase Order records in the records tab of your Purchase Order object:

And you can view the new PO Number values in your app:

Import to Set Auto Increment to Any Number

Similar to the steps outlined in Section 1, in this case you will be using an auto increment field, an equation field, and a new number field called "Beginning ID Number."

If you have not already created an auto increment field, see Step 1 (above). Once you have that field in place, you’ll need to create the number field. This will serve as the beginning number for your custom auto increment values:

Once you have the number field, you’ll need to create an equation field - or modify your existing equation field. We’ll call this field "PO Number." In the equation box, select from existing fields to create this formula: {Beginning ID Number} + Auto Increment

Now, prepare your .csv file for import by adding the field "Beginning ID Number" to the spreadsheet, then enter the same starting ID number for each record you’re importing. In our example, we’re using 3500.

Next, you’ll need to import your .csv into your database. Select the import button to begin the import wizard:

Select your .csv and click "Next" to view the columns/fields you plan to import.

In this step you do not need to include the PO number field, as it is an equation field that will automatically update upon import. Make sure the field names match those in your database object. Then click "Next" to import the records.

Once complete, you can verify the imported records in the object:

Be sure to add these fields to any views in your pages.

Note

You can also add text characters to your equation fields. For example: PO3500 + Auto Increment. This would insert "PO" into each PO Number.