Home Forums Discussions Problems Patient chart numbers

Hello Serene,

Amazing Charts uses a SQL Server feature called an "Identity" to create numbers for the ID field of a patient. Consider the following statement to create a table in a SQL Server database:

CREATE TABLE Demographics (
PatientID INT IDENTITY(1,1) NOT NULL,
FirstName NVARCHAR(20) NULL,
LastName NVARCHAR(20) NULL,
)

The second line which reads "PatientID INT IDENTITY(1,1) NOT NULL means the following:

* PatientID: The name of the column. You see this name and use this name when referring to the data in that column.
* INT: The data type. The type of data stored in this column is to be an Integer. Meaning, of course, numbers such as 1, 2, 3, 4.
* IDENTITY(1,1): The Identity specification. The two numbers in parenthesis are the seed and the increment. The seed means to start at the number 1. The increment means to increment by 1. You can have different seeds and increments. For example: IDENTITY(500,100) would mean to start at 500 and increment by 100 every time a new record is added. So 500, 600, 700, and so on.
* NOT NULL: The not null specification means this column can never be blank. It MUST contain a value whenever a new row is added to the table.

The PatientID of the demographics table in AC always starts at 1000 from what I've seen so it's safe to say it is IDENTITY(1000,1).

With that setting, every time a new patient is added to AC, it should go 1000, 1001, 1002, and so on. However, sequential is NOT guaranteed with IDENTITY. Therefore, it is possible to see some skipping. For example, if SQL Server caches the next 100 identity numbers but then the server restarts, some of the values might be lost so when the server comes back up the next IDENTITY will be 105 (assuming the last used was 5). So, yes, skipping is normal.

Also, an IDENTITY column is IMMUTABLE. That means user code cannot change it. Only the system can.

The only way to fix this issue is to perform surgery on your database. The steps would be to create a temporary column in your Demographics table, copy the existing PatientID values to that temp column, delete the PatientID column, create a new PatientID column and number it with fresh numbers that are sequential, then use the temp column as a reference to update all other tables that have a foreign key relation to your Demographics table with the new IDENTITY. When the time comes, you may wish to reach out to AC support to see what they have to say about this issue.

If they are of no help, then I can, of course, do this kind of work.

James