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:
TipBatch Number management
PlatformAlpha Five v11 and MS SQL Server
BackgroundMy 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 SolutionThere 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
? TIME("yyyy-MM-dd 0h:0m:0s.3",Now())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.
= "2012-10-01 07:18:38.335"
If Variable_Exists("Session.BatchNo")= .F.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.
Session.BatchNo = TIME("yyyy-MM-dd 0h:0m:0s.3")
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::ConnectionBelow 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.
Dim args as sql::Arguments
SqlStr = "EXEC ApprovedLetters :BatchNo, :BatchGroup"
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 FarberDoron 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:
Alpha Five Development : www.dFarber.com/website-
Convert MS Access to Web: www.dFarber.com/website-