How to Create Meaningful, Unique Batch Numbers with Alpha Five v11 and MS SQL Server

Blog



How to Create Meaningful, Unique Batch Numbers with Alpha Five v11 and MS SQL Server


Recently, Doron Farber, of The Farber Consulting Group, sent us this very helpful post on Batch Number management using Alpha Five v11 and MS SQL Server:

Tip

Batch Number management

Platform

Alpha Five v11 and MS SQL Server

Background

My client needed a “Student Loan Management” system which required the ability to print and approve checks.  Like many check printing applications, we needed to create a Batch Number (Batch No) to identify a particular check run for future queries and reporting.  The Batch No is a unique identifier for a group of records and also a friendly number to search for.  The Batch No is not designed to replace the Primary key. Unique Primary must be created in any event.

The Solution

There are several ways to create a unique Batch No (Identity columns,  GUID, etc...), but the approach we took was to take advantage of existing functionality in both Alpha Five and MS SQL Server.  We simply added a Batch No column to the CHECKS table.  The data type in SQL Server is datetime2 (more on why this data type is important later).

There are several benefits to using this Data Type. They include:

    • It provides a simple implicit conversion of properly formatted text to datetime2
    • It is easy to sort with no additional conversion
    • It can easily manage duplicate entries
    • It is human readable without additional display formatting
    • It is easy to search on
Since DATETIME2 allows precision to the millisecond, it is a good choice as a Batch No, even in high transaction environments. When using this technique in Alpha Five you need to use the TIME() function to create a string that will allow MS SQL Server to implicitly convert the value to DATETIME2 .

Example:
? TIME("yyyy-MM-dd 0h:0m:0s.3",Now())
= "2012-10-01 07:18:38.335"
Since the project required the Batch No to be applied to all records selected in the current session, I established the Batch No in a session variable in the OnGridInitialize event of a grid component.
If Variable_Exists("Session.BatchNo")= .F.
Session.BatchNo = TIME("yyyy-MM-dd 0h:0m:0s.3")
end if
In another section of the system, I needed to generate a “temporary” Batch No to allow saving and processing the currently selected records on a grid page.  Using the same technique above, I created a second BatchNo and named it as BatchGroup in the OnGridExecute() event which was saved with each record.  The new BatchGroup value is used in a stored procedure on the SQL Server to extract inserted field values used to insert new records into another table.

Here is the actual code to call the Stored Procedure to insert records into a MS SQL database with two different session variables:
Dim cn AS sql::Connection
Dim args as sql::Arguments
cn.Open("::Name::SQL")
args.Add(“BatchNo”,session.BatchNo
args.Add(“BatchGroup”,session.BatchGroup
SqlStr = "EXEC ApprovedLetters :BatchNo, :BatchGroup"
cn.Execute(SqlStr,args)
cn.Close()
Below is the grid which was used to filter groups of dunning letters based on the Batch No.  The first part of that Batch No is the date to be searched on. So, this way, the user can track down entire documents that were processed on a specific date.
Batch No. Management with Alpha Five v11l and MS SQL Server 

I mentioned earlier in this post the importance of using DATETIME2 instead of DATETIME.  There is a long standing issue with the DATETIME data type that rounds the milliseconds and could potentially lead to duplicate entries in a high transaction environment.

To convert the DateTime in MS SQL you will need to do it this way:

SELECT CAST('2012-09-18 00:04:23.291' AS DATETIME2)

Here are the values returned in SQL Server:
2012-09-18 00:04:23.2910000 when using DATETIME2
2012-09-18 00:04:23.290 when using DATETIME the last 3 digits were rounded from from 291 to 290.
Although not appropriate in every situation that arises, this technique provides a simple method to allow your clients to easily identify a Batch with select values to filter reports or to open a Grid for additional editing or review.

About Doron Farber

Doron Farber develops data-driven Web site  applications, and thanks to Alpha Five they can also run on desktop and mobile devices. Doron also provides Remote DBA services and can be reached at:

Website: www.dFarber.com
Blog: www.dFarber.com/computer-consulting-blog.aspx
Alpha Five Development : www.dFarber.com/website-design-development/alpha-five-developer.aspx
Convert MS Access to Web: www.dFarber.com/website-design-development/convert-access-to-web.aspx
Speed and Performance Matter: The New Alpha Five v11 Report Server
Mobile Business Applications Will Comprise 33% of All New Enterprise Applications In 2013, According to IDC

About Author

Default Author Image
Chris Conroy

Chris Conroy runs digital programs for Alpha Software.

Related Posts
Role-Based Security for Business Apps
Role-Based Security for Business Apps
Evaluating Low Code Mobile App Development Platforms
Evaluating Low Code Mobile App Development Platforms
Building Business Apps with Flexible Design
Building Business Apps with Flexible Design

Comment

Subscribe To Blog

Subscribe to Email Updates