|Mixed Mode Data Using Delimited Strings|
|This is offered for Educational Purposes only.|
|Back in the early '80's, when microprocessors were first starting to be used in home computers, I purchased a S100 bus system from California Computer systems. It had a Z80 processor running at 4 Mhz and two 8" floppy disk drives.
I also purchased a Macro Assembler and a CBasic program to run under CPM. I started writing an Inventory Control program with the CBasic prior to starting Beckwith Computers & Electronics. The Basic was called Compiled Basic. It was still interpreted but created a compressed file of tokens. It was not overly fast, but adequate. The only other option was to purchase a DEC Mini Computer and Inventory Software costing in the thousands of dollars.
After running the programs for several years, I was able to get CB80, a truly compiled basic that created object code and used the existing CBasic code. It was 60 times faster than the interpreted version. Years later, when I purchased my first PC, a 486, it came with Quick Basic, another interpreted basic running under DOS. I had several disk file issues with the Quick Basic in the way they stored data and read it back from the disk. It was not always read back as it was written. It turned out that when Microsoft converted QB45 to the Quick Basic Interpreter, there were several errors in the conversion. I found this out when I got the original QB45 compiler. It was a true compiler that generated object code. All the disk file issues magically went away. I am still running the QB45 on the work stations but I am slowly converting them over to Visual Basic.
Since, in the beginning, I didn't know how long to make each item, I decided not to use fixed length fields. If you allocate a length of 20 characters for a part number, you will soon find one that is 21 characters long and have to change the field length. By allowing variable length fields and setting the maximum length of a record, you can adjust the least important field, usually the description, to fit in the available space allocated when necessary. That is what I decided to do. Originally I used real numbers for prices and wrote them along with the strings to the disk. Eventually there were problems. Each time I changed Basic programs I had to reformat the entire Inventory file. This got old, even though I wrote a program each time to convert the file.
After years of having issues with storing the data on the disk and making it compatible with different versions of Basic, I seem to have finally found the most universal way of storing and retrieving the Inventory Data. This method, which seems the most compatible with various programs, is to use a delimited string. Over several generations of program revisions, using commas, quotes and various other characters for delimiters, each time finding problems with them sneaking into the strings, I finally decided to search for a character that couldn't be entered from the Keyboard. I have found that the character 127, depicted in this text as(), is the best choice. It is the Delete character. All the programs that I am aware of process this key from the keyboard as "Delete". Therefore, it is never added to the text, but removes the previous character. This keeps the strings from getting corrupted by the keyboard entry data.
|The format for each string that we use is as follows:|
Inventory Number, Quantity, Minimum Quantity, On Order, Type, Part Number, Package, Cost, Price 1, Price Break 1, Price 2, Price Break 2, Price 3, Price Break 3, Price 4, Manufacturer, Description, Location, Weight, Image and some other options. We continue adding additional items as needed.
This was the original way each record was set up. I always keep the Inventory Number in the string for identification purposes. In the beginning, I had a bad disk controller and records were sometimes swapped. This also ensures that the record gets put back in the same place each time by reading the Inventory Number from the string before restoring it. Very important!
A typical record then looks like this:
39402250SQUARESTIX40-901-50EACH0.7351.4705001.29210001.17625001.029SMITH40 (1X40) SGL ROW HDR TIN 90 DEGA77A.01
Inventory Number = 3940
The length of this String is 130 characters long. The Record Size was set at 256, since Strings require an extra 2 characters to hold the length, the maximum allowable length is 254 characters, which should be checked before trying to save the String. You could set the length at 384, 512 or whatever is needed. Always use an increment of 128, because of the Sector Size on the disk. This allows for faster Disk Access. Originally the Sector Size on a Disk was set for 128 bytes. Now they use a multiple of this for the larger disks, but the process is the same. In Visual Basic, the fixed length string is limited to aproximately 65536 because of the 2 bytes that hold the string length.
In creating disk files, a Fixed Length Record Oriented File allows Random Access. Since we use different manufacturers data, we convert their files to our string data for common processing. We also use different Record Sizes. If you set the First Record up, in the file, with the File Data, then you can determine the Record Size and various parameters by simply reading the First String in the file. We assume that the First Record length is less than 128 bytes. But that isn't important. Just so you read the entire First String. For the first example, we are predefining the Inventory Variables and setting their parameters in the routine. In the second example, this routine can be made universal by passing a File Name to it and returning the parameters in a String. I use the Val( ) function, and haven't had any problems with it, but you could also use the C???( ) functions.
Record Length, First Record Number, Last Record Number, First Inventory Number
Record Length = 256
You can add other parameters here for your special needs.
Note: In my style of programming, I usually use the identifiers $!%@ for temporary variables and for illustration purposes.
|Dim InventoryRecordLength as integer, FirstInventoryRecordNumber as integer, LastInventoryRecordNumber as long|
Dim FirstInventoryNumber as integer, SourceFile as string, InventoryFile as integer
|Public Sub OpenFile ( )|
|InventoryFile = FreeFile (1)|
|100||Open SourceFile For Random Shared As #InventoryFile Len = 128|
Get #InventoryFile, 1, a$
|110||InventoryRecordLength = Val (GetItem (a$, 1))|
FirstInventoryRecordNumber = Val (GetItem (a$, 2))
LastInventoryRecordNumber = Val (GetItem (a$, 3))
FirstInventoryNumber = Val (GetItem (a$, 4))
|120||Open SourceFile For Random Shared As #InventoryFile Len = InventoryRecordLength|
|Public Function GetItem (ByVal a$, ByVal n%) As String |
|GetItem = ""|
If a$ = "" or n% = 0 Then Exit Function
|'Default, null data returned|
|x% = 0|
|100||For i% = 1 To n%|
s% = x% + 1
x% = InStr (s%, a$, Delimiter)
If x%> 0 Then GetItem = Mid$ (a$, s%, x% - s%)
|Function OpenRecordFile (F$) As String|
|File% = FreeFile (1)|
|100||Open F$ For Random Shared As #File% Len = 128|
Get #File%, 1, a$
OpenRecordFile = a$
There you have it. Whatever the Record Length is of the file and the other data parameters. Just read in the First Record and decode it.
This may appear a slow way of obtaining data, but it is not. It's ease of use far out weighs any loss of speed.
I set up a function to calculate the Last Inventory Number, which could change at any time due to another work station entering a new record. You will have to lock the First Record, where the file parameters are kept, before the new record is added. Unlocking it allows another station to also add a new record.
|Public Function LastInventoryNumber ( ) as long|
|LastInventoryNumber = LastInventoryRecordNumber + FirstInventoryNumber - FirstInventoryRecordNumber|
|Public Function LockReadInventoryRecordNumber (ByVal Rn as long) As String|
|On Error GoTo 1000|
|LockReadInventoryRecordNumber = ""||'by interrogating the returned value, you can determine if the record was read or not|
|100|| Lock InventoryFile, Rn|
LockReadInventoryRecordNumber = GetInventoryRecord (Rn)
|1000||result = MsgBox ("Record Locked # " & Rn, vbOKCancel)|
If result = vbOK Then Resume 100
||End Function||Public Function GetInventoryRecord (ByVal RecordNumber as long) As String||GetInventoryRecord = GetRecord (InventoryFile, RecordNumber)||'It assumed that "InventoryFile" is already defined||End Function||Public Function GetRecord (ByVal FileNumber%, ByVal RecordNumber as long) As String||GetRecord = ""|
Get #FileNumber%, RecordNumber, a$
GetRecord = a$
|End Function||I have removed most of the Error Handling for the sake of clarity. This is why there are multiple functions in these examples.
Below is the function used to calculate the Record Number, since the First Inventory Number may be set to something different other than 1. Just pass the Inventory Number to it to get the the Record Number. This may seem confusing, but there times when you need to access Data eithor by Inventory Number or Record Number. Our search functions sometimes go from the First Record Number to the Last Record Number rather than by Inventory Number. Other times you request an Inventory Number from the keyboard or scanner for processing.||Function InventoryRecordNumber (ByVal InventoryNumber as long) As Long|| InventoryRecordNumber = InventoryNumber - FirstInventoryNumber + FirstInventoryRecordNumber||End Function||We used to read and decode the entire record and then rewrite it if anything changed. There are times when I decode the entire string and display it, but rarely. When this is done, you can assign the data to an array and access it by the subscript. |
Now consider this, by only replacing only the Data Item in the String that changed, you can also have a Varying Number of Items in each string. This will allow for adding new Items and not having to convert the entire Inventory File. Remember the GetItem will return a Null if there aren't enough Items in the String. So, here is the replacement function.
|Function ReplaceItem (ByVal a$, ByVal b$, ByVal i%) As String||'a$ is the entire string|
'b$ is the replacement string
'i% is the Item number to be replaced or added
|ReplaceItem = a$||'always set up to return the same data ||if b$="" or i%=0 then exit function ||'here, you could not allow specific Items to change||x% = 0|
For j% = 1 To i%
ReplaceItem = Left$ (a$, s%) & b$ & Mid$ (a$, x%)
|End Function||If you have only 10 Items in your string, and you request the replacement of Item 20, the above function will add the missing Delimiters to include Item 20.|
Here are some additional routines to Read or Write the Data to/from the Inventory File.
|Public Sub WriteUnlockInventoryNumber (ByVal InventoryNumber as long, ByVal a$)||On Error GoTo 1000|
rn& = InventoryRecordNumber (InventoryNumber)
|100||Unlock #InventoryFile, rn&|
|1000||If Erl = 100 Then Resume Next||End Sub||Public Sub WriteInventoryRecord (ByVal RecordNumber as long, ByVal a$)||Put #InventoryFile, RecordNumber, a$|
'this is where we log the data to a log file indicating the Function/Program that changed the data
|End Sub||Public Function LockReadInventoryNumber (ByVal InventoryNumber as long) As String||On Error GoTo 1000||100||rn& = InventoryRecordNumber (InventoryNumber)|
Lock InventoryFile, rn&
LockReadInventoryNumber = GetInventoryRecord (rn&)
|1000||result = MsgBox ("Record Locked Inventory # " & InventoryNumber , vbOKCancel)|
If result = vbOK Then Resume 100
|9000||End Function||Now that we have discussed the File Structure and the File Routines, consider the possibility of mixing Data Types. For years we struggled with Mixed Mode Data. We would first have to check to see how many more lines to read and hope that there wasn't an error in reading the previous Data. If there was an error, then everything was out of sync. Here is an example of our Purchase Order file, which is just a file of Varying String Data without a Sequential File Structure (non Record Oriented). Also, the order is not important for the Heading Data.|
You simply open the file and start reading in the lines.
|Dim ShipTo(6), OrderTo(6), PurchaseOrder, OrderDate, PartNumber, Mannufacturer, Description as string|
Dim InventoryNumber as long, Quantity, Cost as single
|100||Open FileName$ For Input As file%||110||If EOF (file%) Then GoTo 200
||Input #file%, a$|
Item$ = Ucase (GetItem (a$, 1))
|'Here is the routine that decodes the file.||Select Case Item$|
PurchaseOrder = GetItem (a$, 2)
Case "SHIP TO:"
| Case "#" ||'Inventory Number|| InventoryNumber = Val (GetItem (a$, 2))|
Quantity = Val (GetItem (a$, 3))
PartNumber = GetItem (a$, 4)
Manufacturer = GetItem (a$, 5)
Description = GetItem (a$, 6)
'Item 7 is the package quantity
Cost = Val (GetItem (a$, 8))
'Processing code here
|200||Close file%||Since each line is read in as a String, interrogating the First Item in the String allows for the further processing of that String regardless of the number of Items in the String. Now you can't get out of sync with this Mixed Data Structure.|
Also since this is just a Text File, you can use an Editor, such as NotePad, to change any mistakes, just as long as you don't remove any of the Delimiters.
|Sort Routines||Sorting is very important. When you need to find a Part Number or Item, you need a method of finding it in a hurry. It is very frustrating when a routine doesn't work well. This was one of the major concerns when I set up our Inventory Software. I needed a method of finding things that were in the Inventory File and I didn't know where they were. So I divided them into two categories, Type and Manufacturer. By entering either or both in a Search Routine, you can narrow down your parts rather quickly. As the Search Routine finds the Parts, they are displayed on the screen with the Inventory Number, the Part Number, the Quantity, the Manufacturer, and the Description. |
The original routine was set up to run on two 8" floppy disks, so it had to be efficient. Here is the structure of the files:
There are 3 files. The first two are ASCII files with either the Type or Manufacturers' Names and a starting Record Number in the Inventory File. After obtaining a starting record number, you open a second file, record oriented, containing matching information, parallel with the Inventory File, of Type and Manufacturer Data. Each record points to the next record of that same Type or Manufacturer.
For example, let's say that the following multiple Part Numbers exist in the Inventory File:
| This then is what the Manufacturer File contains alphabetically:|
| This then is what the Type File contains alphabetically:|
|The third file, which is a record oriented file matches the Inventory File, record for record.|
This then is what the matching file contains:
|When we encounter a zero, that is the indicator for the end of that item, Manufacturer or Type. The actual Data is a Delimited String using Long Integers, "101102", Record length of 20. It could be a bit longer, but that is what I originally set it up to be running under CPM.|
So we Daisy Chain through this file, either by Manufacturer or by Type and matching a partial Part Number or Keyword in the Description. With this method, you only read and check the least Number of Records. This improves performance and reduces the amount of data read. There is always a third option of "All". For this option we must we read each Record in the Inventory File and check for a match. This is a bit slower, but when you can't find it any other way or there is a spelling error it is very useful.
There are other search parameters that could be added so we set up the coding to allow for additions.
Private Type EachSortRecord
Subscripts could also be used.
Const NumberOfEntries As Integer = 2
This makes a Record Length of 8 bytes for just 2 entries.
Here is a typical search box and with the drop down menu shown.
I like to use the Keyboard for Entry and use the Default Option on the Command Button to move to the next Box. Here is a sample of that code:
Private Sub cmdSearch_Click()
WhoHasFocus = WhoHasFocus + 1
In Case 5, we simulate a mouse click to start the search routine.
The Text Boxes could also be indexed, if there were more options.
|The next method is a Tree Sort routine that I developed back in the mid 80's. I needed a method of keying in the Part Number and displaying only those records that matched it exactly. Also, there may be multiple matching Manufacturers Part Numbers which need to be displayed at the same time. |
First the method. I read all the Part Numbers, removing any punctuation, placing them in a temporary file with their corresponding Inventory Numbers. If there are more than one Part Number associated with a part, then it is also placed in the temporary file. Since it is difficult to remember where some Manufacturers place Dashes and Periods, I decided to remove any non essential punctuation leaving only numbers and letters to sort through. This is extremely benifictual when customers don't know if it's is a dash or period and you can't remember. Next I read all the Part Numbers, counting the first character in the Part Number. The one with the highest count gets processed first. Each pass removes the items with the current character.
I determined, through many simulations, that I needed 2 Branches for each Node and an Extension. I use Long Integers for the Record Numbers and 2 ASCII characters, one for each Branch.
Private Type EachBranch
We could repalce the 2's with NumberOfBranches.
Const NumberOfBranches As Integer = 2
This makes a Record Length of 14 bytes.
Here is a crude representation of the branching process. Each box represents a Node.
|When you reach the end of a branch, you need a special character to depict that the next value is an Inventory Number instead of another branch. Since I didn't allow spaces in the Part Number and this was in the 80's, I used a space as the defining character. I would probably use the character 127 now. |
This is very fast. Anyone who has ever searched through the Windows Registry file knows how slow a cumbersome database can be. It may take several minutes to find what you are looking for. If you applied this sort routine to the Windows Registry, it would only take a couple of milliseconds to find anything. It certainly takes longer to enter the search line than to find it with this sort routine.
I have two versions, one that is real time and the other that is not. The version that is real time sorts as each character is entered and runs out all of the Branches until the next character is entered. Then it starts over again. It always returns the closest match in the tree if the exact Part Number is not found.
I hope you find this information useful. We will continue to add to this from time to time. Beckwith Electronics