ARS identifies fields by a number id. Some id’s are retated to special functions. Maybe the most important of all is the field id ’1′, often called the Request Id or Entry Id. Understanding how this special field works is very important for any Remedy developer.

In this post I will explain how this field works going beyond the available documentation of BMC.

What is the Request Id?

The request Id is the field ’1′. You can change the name of this field, but it will continue being the field 1, and ARS will consider it as the Request id. It is the identifier, the primary key of your table, in a theoretical way. So it is always indexed, it’s unique and it’s automatically populated at every submit.

It is always a character text between 5 and 15 characters. This is not the best strategy for an Id, since a text index is not as good in performance terms than an integer one. But we get an advantage for having text id’s, we can use a text prefix, resulting in the typical id shape like INC000000066293. The advantage of prefixes is that in ARS is very common to have a field that stores resquest id’s of another forms, mixed. For instance, in a incident’s relationship table, at the id column we can have changes, problems, other incidents, or other types or requests. Having a different request id allows us to determine the related form just looking at the prefix. So it is human friendly.

How it is stored at the database?

All fields of a regular form are stored at the database in a table. Each field that is not a display only field is assigned to a column. All column are named by ‘C’ plus the field id. So the Request Id is always named C1 at the database. This makes things easier for the ARS engine and the developer, because the ARS engine will always call C1 to the main field, regardless of the form, and the developer is free to select a name for the field (‘RequestId’, ‘Entry Id’, ‘Invoice Number’, …).

Primary Key

In fact the C1 is the theoretical primary key of the database, but I don’t know why, BMC developers had chosen to not declare it as primary key. But sometimes they are enforced to declare a primary key to it’s tables, since some tools can’t work correctly if such key is not declared, mainly replication engines. So depending on the ARS version you are using and the database engine, maybe your C1 columns are the primary key or not. At version 7.6 with MS SQL Server, primary keys are declared, and with Oracle they aren’t.

But If you need those primary keys you can create easily with an script that adds a primary key with C1 column to all tables.

Autonumbering

To obtain a Request ID, ARS gets a number, pad it with zeroes and substitute the first characters with the submitted content of the field (normally the default value of the field or the one set by workflow). In steps:

But, how the ARS engine determines the next number? Let’s see. All forms are documented in a table at the database, the arschema. This table stores the form name, form id, and the next value for the Request Id. So the ARS query this value to get the next value and modify it to avoid duplicates. This operation can be configured to be performed one by one or in groups. Since the most common configuration is one by one, i will start by this option.

When the ARS system wants to create a request, it executes all the filters and at the end start the SQL operations that are:

All actions are performed inside a transaction, so if the register is not created at the form table, the id is not used since the operations over the next id counter are rollbacked.

To aument the next id counter the ARS makes the next SQL sentence:

Where 4423 is the identification number of the form.

To get the current value of the counter, the ARS makes the next SQL query:

So, the nextId field of the arschema table always holds the last used id, and not the next id as it’s name suggest.

Next id get configured in groups

ARS engine can be configured to get the id values one by one or in groups from the arschema table. If it is configured to obtain them in groups, then the arschema is only accessed when the ARS runs out of available numbers. I will explain it with an example. Imagine that the server is configured to obtain id’s in groups of 100. It needs to submit a register to the AA form. So it queries the arschema adds 100 to the next id field and get the value: 600. So the server now is in possession of the id numbers from 501 to 600 for the AA form. It creates the request with the Id 501. A sencond sumbit arrives. It establish the id 502, no query to the arschema is needed. When it runs outs of ids, because it has added 100, then a new query to the arschema is made.

This behavior has one main advantage: performance. Not by avoiding the query and the set to the arscheme table (those operations are very light), but because the ARS runs these actions inside a transaction that also contains the insert into the form, the register at the arscheme table is blocked until the whole operation has ended. This prevents two inserts at the same form to occur in parallel. Modern database engines can operate multiple submits at the same table in parallel, but since the read plus update operation of the arscheme is made inside a transactions, this operation is blocked.

But only happens when different threads or ARS server want to create the same register at the same time. The likelihood of these conditions is very little in standard deployments and only affects to big server groups with tons of users.

The cons of this setting are:

These cons are in fact an incident creator. Users instinctively expect id numbers to be sorted. When they discover this estrange behavior, they tend to create an incident, believing that the server is malfunctioning.

Taking profit of this knowledge

Since we know how the ARS gets the next id number, we can take profit of it to:

First, I want to warn you about the danger of corrupting the database if these operations are not well done. Be careful since they are not supported!

Create requests manually

The most difficult part of creating a request manually, i mean connecting directly to the database, is to populate the request id field. The best option to achieve it is to imitate the ARS procedure. That is, to update the value, get the next number, create the request, create the request at the H table and finally commit the work.

There are also other important fields that must be populated correctly at every form, like the submitter, last modified by, create date, etc. But there are not as difficult to populate than the Request ID.

If you want to make a bulk insert directly at the database, commit the operation at every record or the form will be blocked until the bulk insert ends (since the arschema nextId field will be blocked).

I repeat, it is a dangerous operation, so consider it!

Update the counter to any value

Imagine that you want to reset the counter value all years to show it at the id. So, INV000020120345 is a 2012 invoice. Or may be you want to reset it to start from one again.

To achieve it, you must set the nextid to the value you want to start from minus one. For instance, if I want to start from register 20120000, than I must set the value to 20119999 like:

Or you can reference it by the form name like: