Create Auto-incrementing Code Numbers Using jQuery and the SP Services Library

You’ve seen them before, usually found at the top of an invoice or other business form, sometimes in red ink, a series of letters followed by a number. Businesses use sequential numbering to identify and keep track of important documents that serve as a reference to business transactions or operations. These code numbers are often added to the form pads that companies order from a printer. They are also commonly added to electronic forms and are generated with an auto-increment function in code. For example, Excel allows a power user to create complex sequential codes by using built-in functions and VBA. Although it is possible to create auto-incrementing numbers for items in SharePoint using calculated columns inside of a workflow, we will show you in this series of blog posts an alternative method that relies on jQuery and the SP Services library. Doing it this way provides more flexibility for formatting the code and also helps server performance by offloading these operations to the client browser. Along the way, we will encounter an issue that crops up in SharePoint with look-up fields that have more than 20 items, and we will show you how to overcome the limitations it imposes when using jQuery events.

Scenario and Preparation

Our scenario for this exercise will be to generate a unique sequential code for a fictional organization that provides services for airports in the US and Canada. The number will be built with three components: a reference to the international airport code, a reference to the airport department making the request, and a number that is generated by counting the number of codes that match the combination airport code and airport and assigning it the next available number. Before we can begin writing our code, we must first build the requisite look up lists and master list.

Airport Codes list

The first list we want to create is the Airport Codes list. This will contain a list of airport codes, which are  three-letter codes that identify each airport and are often seen on the baggage tags attached to your luggage.

In SharePoint, create a new custom list and name it “AirportCodes”. In List Settings, add the following columns to the list (see Fig. 1-1):

  • City : Single line of text
  • Country: Single line of text
  • AirportCode: Single line of text
  • DisplayName : Calculated Column
Figure 1-1
Figure 1-1

Add the following code to DisplayName’s formula field (see Fig. 1-2):

Figure 1-2
Figure 1-2

The Display Name column will be used as options in a drop-down list in the new item and edit item forms of this list.

Also in List Settings, modify the All Items view to show only the created columns and order them as seen in Figure 1-3:

Figure 1-3
Figure 1-3

Once our list has been set up, we will want to populate it with values. I used the values found on this list of Airport Codes listed on the Expedia website. The easiest way to do this is by downloading this Excel file copying and pasting the values from the AirportCodes worksheet into the SharePoint list using Datasheet view.

Departments list

For the airport departments list, create another custom list, name it “Department”, and add the following columns (see Fig. 1-4):

  • DepartmentName           Single line of text
  • DepartmentCode            Single line of text
Figure 1-4
Figure 1-4

Also, modify the All Items view as shown in Figure 1-5:

Figure 1-5
Figure 1-5

Finally, copy and paste the values found in the Departments worksheet of the downloaded Excel file into the Departments list, as you did for the AirportCodes list.

RequestNumbers list

The AirportCodes and Departments lists we just created will be used to create look-up columns in the service request master list. This list will allow us to register each service request with a new unique code. Normally such a list would include fields for service type, service description, requested by, and other associated data, but for the purposes of this exercise we will only be using the fields needed to generate the code. Create a new custom list and name it “RequestNumber”. Add the following columns and additional fields to the list:

  • AirPortCity                                         Lookup
  • AirPortCity:AirportCode             Lookup  (Additional field for AirportCity; see Fig. 1-7)
  • Department                                       Lookup
  • Department:DepartmentCode  Lookup  (Additional field for Department)
  • RequestNumber                              Single line of text
Figure 1-6
Figure 1-6
Figure 1-7
Figure 1-7

Modify the All Items view to show only the AirportCity, Department, and RequestNumber columns:

Figure 1-8
Figure 1-8

Now that we have created and populated our lists, we are ready to begin writing the jQuery code that will create our auto-incrementing code numbers. But that is a topic for next week’s blog.

February 12, 2013