by Cal Locklin
A common issue for Alpha users is to find a specific record based on a name or other value. While this can be done easily with Find by Key, sometimes that's not very useful (i.e., finding "Smith" could mean looking through a lot of records). So, I decided to develop a method that would allow the user to select the desired value from a list that had additional information, such as the address, phone, or other field values. I also had a customer that wanted to select a record based on active records or all records. Finally, I wanted to make it as easy as possible for the user, and I've always like the auto-fill capability of Quicken (as you start typing, the next matching value fills in automatically1,2. The logical solution seemed to be an xdialog.
Cal Locklin has worked extensively with xdialog since V5 was introduced, and has shared his expertise many Alpha users. He can be reached at CALocklin@charter.net or www.aimsdc.net.
It was easy to figure out what I wanted the xdialog to look
like:
The hard part was making it work. Every time the "Find by"
radio button was changed, the list needed to be re-created using the correct
filter based on the "Status" setting of Active or All. The list
would also need to be re-created every time the Active or All radio button was
changed. In addition, whenever the list was being recreated because the "Find
by" button was changed, the fields in the list would have to be
re-arranged so the first field was the one being searched. The header would also
need to be changed to match the new order. Then we need to search the list every
time the user types a character in the Find box.
First let's look at how
the list is built and why....
The list was built using the
Table.external_record_content_get() function because it was easy and fast. I
haven't checked the limits but it is nearly instantaneous on my 2.4 Gig system
when creating a list of 2500 records - which was plenty for this membership
list. (If the list is 200,000 lines I can guarantee there will be a wait unless
another technique is used to cut the list into smaller sections.) I didn't use
the Keylist_Build method because, even though it puts things in nice columns, I
couldn't find any way to search the list on a specific column. (Yes, it finds
the first match to the first key pressed but that wasn't adequate for my
purposes.)
By separating the field values with " -- ", I could easily
retrieve any value in the selected line with a simple Word() function using " --
" defined as the word separator.
The one bad thing about this was that
the display wasn't very nice originally because the columns didn't line up when
using the default font. That was easily fixed by changing the list to Courier
font which uses fixed width characters.
Here's a
Table.external_record_content_get() function that could be used to get my list
of names from the Members table. Of course, it would all be on one line in the
A5 Code Editor:
mlist = table.external_record_content_get(
"members", "padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' --
'+Menumberf+' -- '+Licensenof+' -- '+Mmbr_Statf", "Lastnamef+Firstnamef",
"Mmbr_Statf='A'" )
Note that the name was "calculated" to combine
first and last names for display purposes and then padded with blank spaces to
maintain the alignment of the fields.
When getting a list by member
number, the function is almost the same except the sequence of fields is changed
and the sort order is changed. This was mostly a cut-and-paste operation since
all the info was already available in the other version. (Note that this is the
'real' function because it includes the variable 'filter' that is used to
determine whether to list All or only Active records.):
mlist = table.external_record_content_get( "members", "Menumberf+' --
'+padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' -- '+Licensenof+' --
'+Mmbr_Statf", "Menumberf", filter)
To rebuild the list when a
radio button was pressed, I simply set up an event processor (!fby_*) in each
radio button.
Find by: (fby:Name!fby_*) (fby:Member
#!fby_*) (fby:License #!fby_*)
(For those not familiar with xdialog,
the choice of "fby_*" was basically arbitrary. As far as A5 is concerned, it
could have been called "xyadk_*" as long as it ended with "_*".)
Note
that I used separate radio buttons because I wanted them to appear horizontally
but the event name is still identical in all three "Find by" buttons. This means
there is only one event needed to handle the "Find by" in the Code section of
the xdialog script.
I then checked for the Change event in the Code
section of the xdialog:
IF left( a_dlg_button, 4 ) = "fby_"
IF a_dlg_button = "fby_change"
<Code here to rebuild to desired list.>
END IF
a_dlg_button = "" 'Make sure the dialog doesn't close.
END IFFinally, we need to search and highlight the list as the user
begins typing a value in the Find box. To do this, we need to search the list
for the closest match every time a letter is added (or removed) in the Find box.
Just like rebuilding the list itself above, the search is triggered by an event
processor in the Find box (!fin_* in this case):
Find:
[.20,1find_name!fin_*]
For the purposes of this article, I've
included a fairly simple, sequential search routine that simply checks one line
at a time until a value equal to or greater than the Find value is located. At
that point, the 'selected' variable in the dialog box ('chc' in this case) is
made equal to the value of that line and the xdialog automatically jumps to it
and highlights it. This simple search routine works fine for demonstration
purposes up to about 40 lines. Delays start becoming noticeable after that. I
have a much faster binary search routine that theoretically works
instantaneously from a user perspective on over 1,000,000,000 lines but the
routine is longer and more complex. This binary search routine was used in my
comparison tests below. Contact me at mailto:CALocklin@chartermi.net?Subject=&Body=
for information on obtaining it as an addin.
Once the correct entry is
located, the user has three options for closing the dialog and starting the
actual Find in the table. For those who feel they must take the extra
time to use the mouse, there is always the option to grab the mouse and click
the OK button or double click the selection in the list box. However, for all
the serious data entry people who don't like switching back and forth all the
time, the fastest and easiest way is to just press the Enter key.
Comparison with Progressive Lookup described in Alpha Newsletter
#16.
This method is similar in many ways to the Progressive Lookup described
by Jim Chapman in newsletter
16. There are two main differences in the two methods - initial loading
method and search method. First, the progressive lookup uses the Keylist_Build
method for loading the list and the method presented here uses
Table.external_record_content_get(). Second, the search method used by the
progressive lookup needs to re-create the list every time a letter is typed
whereas the method presented here uses the same list and just locates a
different line.
Following are the results of some tests run on my system.
I have not tested it on a network but I believe a networked system would result
in additional time for loading the initial search list and when updating the
list as each letter is typed in the progressive lookup. It shouldn't affect the
search routine used by the method presented here because that is just a search
on a local string variable which should be handled by the
workstation.
The time to load the initial list with 5,000 records seems
to be about the same for both methods (a noticeable fraction of a second) but
the Keylist_Build method becomes noticeably faster as the number of records
increases. At 50,000 records the times were 3.5 seconds vs. 14 seconds. However,
this initial difference in load speed must be balanced off against the search
speed and the "annoyance level" of the search.
With 5,000 records there
was a perceptible delay with the progressive lookup when typing the search text
but it wasn't a problem. A similar test on 50,000 records showed some rather
significant differences. The actual search was very annoying using the
progressive lookup because after each letter was typed there was another few
seconds before the next letter could be typed. Using the binary search with the
method presented here, there seemed to be a perceptible (i.e., don't blink)
delay in the screen re-draw but it was able to keep up with my typing even when
I entered "987" as fast as I could.
The one place the method presented
here has a definite advantage is when the initial list can be filtered. For
example, if you have a list with 50,000 orders but only 2,000 are currently
active and you only need to search on the active records, the method presented
here would only have to run the search once. The progressive lookup method would
have to re-run the filter/query every time a letter was pressed. I ran a test
like this and the method presented here took about .72 seconds to present the
initial list and the search, of course, happened as fast as I could type. Using
the progressive lookup method, the initial search took about 1.4 seconds and
each letter that was typed took nearly as long because all 50,000 records had to
be queried after each letter.
Conclusion
As with so many other things in the computer world, each
method has it's advantages. The progressive lookup is probably easier for most
people to build because much of it can be generated with the genies. For smaller
lists showing all or most records in the table, the progressive lookup works
just fine. On longer lists, the method presented here, if used with a binary
search routine, would provide more speed once the initial list was created. The
question that has to be answered by you as the developer is, "At what point is
it better to make the user wait longer for the list to load in order to make the
lookup process itself work more smoothly." At 50,000 records, the total time to
complete a search based on entering 4 characters was very similar for both
methods. If everything was planned ahead of time, the progressive lookup won by
a couple seconds but I believe most users would often end up doing as I did -
making typing mistakes due to the delays thus causing even more
delays.
My personal opinion at this point is that the progressive lookup
is good up to somewhere between 3-5,000 lines and the method presented here is
better from there to about 20-30,000 lines when all records are being searched.
Beyond that, the wait times are enough to annoy anybody. At that point it
becomes a matter of deciding what will annoy the user most - then using the
other method.
However, if the situation is such that the initial list can
be filtered because the records being searched are a small part of the total,
the method presented here will be much faster when used with a binary
search routine.
Here's the complete script.
(For those who haven't tried it yet, note that the %dlg% section can now be
highlighted with a background color as shown here. Unfortunately, the %code%
section can't yet - but I'm still hoping. The color choices are up to the
user.)
'Find by name, member number, license number.
'Set up defaults for a find by Name showing Active members.
fby = "Name"
mlist = table.external_record_content_get( "members", "padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' -- '+Menumberf+' -- '+Licensenof+' -- '+Mmbr_Statf", "Lastnamef+Firstnamef", "Mmbr_Statf='A'" )
hdr = "Name Member # License # Status"
show_all = "Active"
'Show a dialog to prompt the user.
dlg_text = <<%dlg%
{ysize=.3}{units=F}{sp};
{font=MS Sans Serif,8,b}Find: {font=MS Sans Serif,8,n}[.20,1find_name!fin_*]
{font=MS Sans Serif,8,b} Find by: {font=MS Sans Serif,8,n}(fby:Name!fby_*) (fby:Member #!fby_*) (fby:License #!fby_*);{sp};
{font=MS Sans Serif,8,b}Status: {font=MS Sans Serif,8,n}(show_all:Active!sta_*) (show_all:All!sta_*);{sp};
{font=Courier,10}{text=80,1hdr};
[.80,20chc^#mlist!mli_*]{font=MS Sans Serif,8,n};
{line=1,0};
<*15OK> <15Cancel>
%dlg%
ok_pressed = .F. 'Used to verify OK button or double click.
DIM dlg_result as C
dlg_result = ui_dlg_box( "SELECT MEMBER", dlg_text, <<%code%
'Check to see if the user double-clicked a line.
IF left( a_dlg_button, 4 ) = "mli_"
IF a_dlg_button = "mli_dblclick"
ok_pressed = .T.
ELSE
a_dlg_button = ""
END IF
END IF
'If the user typed a letter, find the closest matching line.
IF left( a_dlg_button, 4 ) = "fin_"
IF a_dlg_button = "fin_change" .and. find_name <> ""
ptr = Search_str( mlist, find_name )
chc = ptr.value
END IF
a_dlg_button = ""
END IF
'If the user changed the "Find by", rebuild the list.
IF left( a_dlg_button, 4 ) = "fby_"
IF a_dlg_button = "fby_change"
mlist = Get_mlist( show_all, fby )
'Since the search is changing, blank out the name.
find_name = ""
'jump back to the Find box.
ui_dlg_ctl_goto( "SELECT MEMBER", "[.20,1find_name!fin_*]" )
END IF
a_dlg_button = ""
END IF
'If the user changed the Status filter selection, rebuild the list.
IF left( a_dlg_button, 4 ) = "sta_"
IF a_dlg_button = "sta_change"
mlist = Get_mlist( show_all, fby )
find_name = ""
ui_dlg_ctl_goto( "SELECT MEMBER", "[.20,1find_name!fin_*]" )
END IF
a_dlg_button = ""
END IF
'If the user clicked the OK button, make sure something was selected before
'allowing the dialog to close.
IF a_dlg_button = "OK"
IF chc = ""
msg = "Please make a selection or press the Cancel button."
ui_msg_box( "NOTHING SELECTED", msg, 48 )
a_dlg_button = ""
ELSE
ok_pressed = .T.
END IF
END IF
%code%)
'If the user cancelled, quit.
IF .not. ok_pressed
END'
END IF
'Parse the member number from the string. For the sake of
'convenience, the member number is always in the 2nd position
'except when looking up by member number.
IF fby = "Member #"
mem_no = word( chc, 1, " -- " )
ELSE
mem_no = word( chc, 2, " -- " )
END IF
'Set the index and find the member's record.
parentform.Index_SetExplicit( "Menumber_" )
parentform.find( mem_no )
END'
'End of program. Only functions follow.
FUNCTION Search_str as P ( Str_in as C, Find_what as C )
cnt = 0
pos = 0
value = ""
WHILE cnt < line_count( str_in )
cnt = cnt + 1
IF word( str_in, cnt, crlf() ) >= find_what
value = word( str_in, cnt, crlf() )
pos = cnt
cnt = 99999999 'Stop the WHILE
END IF
END WHILE
'If we reach the end with no match, show the last line.
IF cnt = line_count( str_in ) .and. pos = 0
pos = cnt
value = word( str_in, cnt, crlf() )
END IF
Search_str.pos = pos
Search_str.value = value
END FUNCTION
FUNCTION Get_mlist as C ( show_all as C, fby as C )
IF show_all = "All"
filter = ""
ELSE
filter = "Mmbr_Statf='A'"
END IF
SELECT
CASE fby = "Name"
mlist = table.external_record_content_get("members","padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' -- '+Menumberf+' -- '+Licensenof+' -- '+Mmbr_Statf","Lastnamef+Firstnamef",filter)
hdr = "Name Member # License # Status"
CASE fby = "Member #"
mlist = table.external_record_content_get("members","Menumberf+' -- '+padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' -- '+Licensenof+' -- '+Mmbr_Statf","Menumberf",filter)
hdr = "Member # Name License # Status"
CASE fby = "License #"
mlist = table.external_record_content_get("members","Licensenof+' -- '+Menumberf+' -- '+padr(trim(Lastnamef)+', '+trim(Firstnamef),30,' ')+' -- '+Mmbr_Statf","Licensenof",filter)
hdr = "License # Member # Name Status"
END SELECT
Get_mlist = mlist
END FUNCTION
1 For example, if you type "S", you might see Sanders (the first "S" record). When you type "M", Smedly might appear. And so on.
2 Alpha does have this capability in field rules, but it becomes unusably slow with more than a couple hundred records.