Tip: Overcoming the default alphabetical column sort when sorting by rank

Here's a tip to sort names or items by rank, usage, or prioity rather than the default alphabetical sort. This tip uses a hidden column and the Notes formula language. A download of the application described in the tip is available.


Donald Russell, Advisory QA Engineer, IBM Corporation

Donald Russell is an Advisory QA Engineer for Lotus Education and has been working in IBM's Lotus Software division since 1996. Prior to his current position, Donald was an advisory IS application developer and a senior Premium Notes Support Analyst. He holds a Bachelor of Science in Electrical Engineering and is a Certified Novell Administrator. Donald has written over 500 technical documents and white papers for the IBM DCF and Lotus Knowledge Base and has published technical articles for The View magazine. Outside of work, Donald was a Cub Scout Den leader and now an Assistant Scout Master for the Boy Scouts.

06 December 2004

Also available in Japanese

When sorting name or items, the default alphabetical sort is not always desirable. In any company, sorting items by usage or priority and sorting names by rank may be necessary. Software programs for this solution include ClarisWorks, Microsoft Works for DOS/Windows, Microsoft Access, Microsoft Word using tables, DB2, Oracle, and Sybase. Lotus Notes has the advantage here and you will see why!

A common solution to the sorting issue is to create a table in Microsoft Word and to add sorting numbers in front of the items or names. Then you perform a manual sort of each column. However, each new sort overwrites the old sort, so you must copy and paste sorts or perform the sort again every time. Lotus Notes provides a more appealing solution that does not give those priority numbers away by displaying them! Each sort is a different view that can be seen in the navigator pane and accessed when needed. Each view can be exported, concatenated, and printed. Choose File - Export and save type as Tabular Text. This stores the sort in a text file on your hard drive.

Let's use an example to illustrate how this solution works. As an Assistant Scout Master and a Computer merit badge councilor, I used Lotus Notes to create a troop roster that sorts by rank instead of alphabetically. The trick is to use a special default form formula to assign ranking numbers to a hidden first column in the By Rank view.

You can download the Scout Roster example from the Sandbox. The application is compatible with Lotus Notes 5 and later.

NOTE: This IBM/Lotus Scout Roster application meets the requirement for Scouting's computer merit badge 4a.

Download the sample application (there is a version 5 and 6 database to choose from) to your Notes data directory. See the Using This Database document for help, and then enter your scout data! Or follow this procedure to create your own application.

  1. Create a new database from a blank template.
  2. Create a simple form.
  3. Add three views to meet the requirements:
    • Scout By Last Name (Alphabetically)
    • Scout By Patrol
    • Scout By Rank
  4. Enter scout data by creating documents.

Creating the new database and designing the form

For those who are new to Notes application development, refer to the Using This Database document for more detailed information about creating an application. For more experienced developers, follow the procedure below.

Create a new database in Domino Designer and create a form. Add the following text fields to the form:

  • ScoutNameF
  • ScoutNameL
  • SPatrol
  • HomePH

Then add these fields for rank:

  • SRank is a dialog list.
  • SRankOrder is a hidden, computed number field.

Because the sort order is alphabetical, one way to deviate from this is to add a hidden, first column with ranking numbers. Each rank is assigned a number to display it in the proper order.

The field called SRank (Scout Rank) is a dialog list that shows a drop-down box selection in the document, containing all the scout ranks from which to choose. These names must be consistent for the formula in the application to work. The second field, SRankOrder, is a hidden, computed number field based upon the SRank field that generates the Sort Number data automatically.

Add the following @formula to the SRankOrder field to make it work.

@If(SRank = "Eagle";1;SRank = "Life";2;SRank = "Star";3;SRank = "1st Class";4;SRank = "2nd Class";5;SRank = "Tenderfoot";6;SRank = "Boy Scout";7;8)

You can add an Edit button to the form using @Command([EditDocument]). Add a Save and Close button to the form with @Command([FileSave]);@Command([FileCloseWindow]) .

Figure 1. Form

NOTE: Download the Scout Roster Flash demo from the Sandbox for application setup instructions.

Viewing the design

We used three views in our application: Scout By Last Name (Alphabetically), Scout By Patrol, and Scout By Rank. Design your three views to display the value of each field on the form. In the Scout By Rank view, we included a hidden, first column called RankOrder for the SRankOrder field.

Figure 2. Hidden column
Hidden column

For columns that users can see, we enabled column sorting with the "Click on column header to sort" property and using the Descending option.

To each view, we added a shared action button to create a document using this formula: @Command([Compose]; ""; "<form name>"). See the Domino Designer Help for more information about creating and inserting shared actions.

Entering scout data

Last but not least, create documents in the application using the simple form. After completing the documents, open each view to see the different sorting solutions. The actual sort is performed on the hidden, first column so the rank is in the proper order.

Figure 3. Sort By Rank view shows rank order, but hides the ranking numbers
Sort By Rank view shows rank order, but hides the ranking numbers
Figure 4. Sort By Patrol view shows categories and ascending order
Sort By Patrol view shows categories and ascending order
Figure 5. Sort By LastName view shows alphabetical ascending order
Sort By LastName view shows alphabetical ascending order


As you can see Lotus Notes is not only a powerful way of easily viewing your sorted information in a local database but by hosting it on a Domino server, people within your organization can be assigned specific access privileges to view any or all of the data. Editors can be assigned to change the data and managers to manage who has access to the data.

NOTE: The sample application contains Boy Scout graphics that you can download from the Boy Scout Animated Graphics and Fonts Web site. The Scout Roster uses a frameset with an outline and pages to display more graphics. For more information about framesets, outlines, and other Domino design elements, refer to the Domino Designer Help.



developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into IBM collaboration and social software on developerWorks

ArticleTitle=Tip: Overcoming the default alphabetical column sort when sorting by rank