By Barry Rochford
A few days ago an Alpha User from the UK put a Message on the Alpha Message Board asking for assistance in reporting Missing numbers within a Range. Normally finding missing numbers isn’t very difficult, but he added a new twist. The table to be checked contains Manufacturers Serial Numbers (“MSN”) with a full range of mixed Alphanumeric characters
Well, that certainly does make the task more difficult. This did look like a challenge, so I looked at it and came up with a test table containing a sampling of MSN’s.
Figure 1 – GRAHAM Table (indexed by C_number)
Next I needed a form asking for the Range to be checked for missing numbers. (Note that this form is attached to my START Table, it contains only 1 dummy record.)
Figure 2 – Main Form
The red INFO button does list some additional information, including the fact that validation should be done on the starting/ending numbers entered. Pressing the “START CHECKING” button excecutes the Script which does the checking and creates an Extract record for each missing number. “MISS_EXTR” is the extract table, it will be used for the Report.
When the first range has been checked, additional options appear on the Form’s Conditional object. You can request another Range, or just go ahead and Print/Preview the Report. There is no limit on the number of Ranges that can be selected.
Figure 3 – Conditional Object
Pseudo code for the Start Checking button
Target_numbern = start_no High_limit=end_no Zap the extract table Open Graham table Do a query using “between(,c_number,start_no,end_no) Display error if no records Fetch first graham record While not end of file Must check, first few numbers of range could be missing If c_number equals target_numbern. increment by 1, get next graham record If c_number is greater than target_numbern, create extract, increment by 1, get next graham record when end of file, check for missing numbers at end of the range also if target_numbern less or equal to high_limit create extract, increment target_numbern by 1, then check again. Drop the query Close tables Show options on form
Here is the Xbasic for the Start Checking Script
''XBasic
' Barry Rochford 8-18-2000
dim extr as p
dim input as p
dim high_limit as n
dim target_numbern as n
dim shared start_no as c
dim shared end_no as c
dim shared left_pos as n
dim shared right_pos as n
dim shared more as c
dim shared co_1 as c
target_numbern=val(substr(Start_no,Left_pos,(Right_pos-Left_pos)+1))
high_limit=val(substr(end_no,Left_pos,(Right_pos-Left_pos)+1))
'NOTE: You should always use an OnError routine when
' manipulating tables "behind the scenes"
extr=table.open("miss_extr", file_rw_exclusive)
if more<>"Yes" then
'will be "Yes" if doing another range
extr.zap(.t.) 'empty extract table
end if
input=table.open("Graham")
input.index_primary_put("c_num_seq") 'set index
query.description="" 'create a query
query.order="c_number"
query.filter="between(c_number,'"+ start_no +"','"+end_no +"')"
indx=input.query_create()
rec_count=indx.records_get()
if rec_count=0 then
code=ui_stop_symbol+ui_ok
ui_msg_box("Error","No records within your range!",code)
indx.drop() 'drop the query
end
end if
input.fetch_first() 'get 1st input record
while .not. input.fetch_eof() 'create a while loop until done
check:
if val(substr(input.c_number,left_pos,(right_pos-left_pos)+1))=\
target_numbern then
target_numbern=target_numbern+1
else
if val(substr(input.c_number,left_pos,(right_pos-left_pos)+1))>\
target_numbern then
extr.enter_begin() 'you have a missing number, create extract
extr.start_no=upper(start_no)
extr.end_no=upper(end_no)
extr.missing_no=target_numbern
extr.enter_end(.t.)
target_numbern=target_numbern+1 'increment target
goto check 'go back, ck. again with new target number
end if
end if
input.fetch_next()
end while
flush:
if target_numbern <= high_limit then
extr.enter_begin() 'handle missing last number(s)
extr.start_no=upper(start_no)
extr.end_no=upper(end_no)
extr.missing_no=target_numbern
extr.enter_end(.t.)
target_numbern=target_numbern+1 'increment towards limit
goto flush
end if
indx.drop() 'drop the query
input.close() 'close both tables
extr.close()
co_1="B" 'set conditional object
cond1.refresh()
end
The Report is printed with 3 columns in the detail section, grouped by Start_no+End_no
Figure 4 – Report
A Zip file is available for you to download.
Barry is an Alpha Developer living between Newtown & New Hope in Southeastern PA. He can be reached at: brochford@enter.net
8/21/00
Don't forget, we need your feedback to make this site better!