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
Add the following code to DisplayName’s formula field (see Fig. 1-2):
=City&", "&Country&" - "&AirportCode
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:
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.
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
Also, modify the All Items view as shown in 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.
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
Modify the All Items view to show only the AirportCity, Department, and RequestNumber columns:
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.