by Dr. Peter Wayne
In this article, we will develop a single button that will search
by any of 3 predefined indexes. This will insulate your user from having
to understand the Find by Key dialog in Alpha Five.
You will also learn a handy new function defined in version 3.04, the
eval_valid() function. |
Let's say your company has a typical invoicing application. In the
invoice header, you store the customer_id, the invoice_date,
and the invoice_number, along with other invoice-specific fields.
The data entry personnel need to pull up invoices in response to telephone
inquiries and for posting purposes, and they are constantly switching from
one index to another.
Is there any way you can code a search button that will let you enter
either a date, a customer id, or an invoice number, and then Alpha Five
will automatically seek by the appropriate index?
The answer is Yes. We will use the same logic that a human operator would
use in deciding what kind of search to use. If the entry looks like a
customer_id, then search by customer code. If it looks like a date, then
search by date. And if it looks like an invoice number, search by invoice
number.
Let's use the invoice.adb that comes with Alpha Five. We'll add a
Search button to the Invoice form that comes with Invoice.set.
The logic of the OnPush event script for the button will be:
Here is the code. I have added line numbers to aid in the discussion. Of course, these line numbers should not appear in your code!
1. dim reply as c
2. dim q as p
3. reply=ui_get_text("Search","Enter customer #, invoice #, or date")
4. if reply="" then
5. end
6. end if
7. select
8. case eval_valid("{"+reply+"}")=.t.
9. ' then it's a date
10. query.filter="between(date,{"+reply+"},{12/31/2099})"
11. query.order="date"
12. q=table.current().query_create()
13. case len(reply)=6
14. ' it's an invoice_number
15. query.filter="between(invoice_number,reply,'999999')"
16. query.order="invoice_number"
17. q=table.current().query_create()
18. case else
19. ' it's a customer
20. query.filter="between(customer_id,reply,'99999999')"
21. query.order="customer_id"
22. q=table.current().query_create()
23. end select
24. if q.records_get()>0 then
25. parent.resynch()
26. end if
27. q.drop()
28. end |
The explanations of each set of lines follows:
| Lines | Explanation |
|---|---|
| 1-2 | declare variables for later use |
| 3 | get a search string into the variable, reply |
| 4-6 | if nothing is entered, then quit |
| 8 | see if it's a date |
| 9-12 | if it is a date, then do a query by date. Note that use of the between() function in the query allows for Lightning Query Optimization if an index exists on the search field. |
| 13 | it's not a date, see if it's an invoice number of length 6 |
| 14-17 | if it is an invoice number, query by invoice number |
| 18-22 | if it's not a date and not an invoice number, do a customer number query |
| 24-26 | if the query found a matching record or records, then show them |
| 27 | drop the query so the user can navigate in the form |
You may have noticed that I used an unfamiliar function in line 8, the
eval_valid() function. This is a new, built-in function that is
not in the manual or the on-line help. Its syntax is
truth_val=eval_valid(expression). You can
use this handy function to see if any expression results in something
Alpha Five considers valid. I am using this function to check whether the
user enters a valid date. For example, here is the output of the Interactive
window:
x="2/2/98"
y="2/29/98"
z="000006"
? eval_valid("{"+x+"}")
= .T.
? eval_valid("{"+y+"}")
= .F.
? eval_valid("{"+z+"}")
= .F.
Of the 3 variables x, y, and z, only x
can be construed as a valid date.
Can we make this script even more user-friendly? You betcha! As is, the
user has to enter a full 6-digit invoice number or an 8-digit customer
number to perform a search. It's easy to make a mistake when typing in
lots of leading zeroes. Let's simplify the user's job by allowing her to
enter a customer number as "C6" instead of "00000006".
Any number entered without a leading "C" will be interpreted as
an invoice number, so that "26" will be interpreted as invoice
number "000026". Here is the improved code to handle speedy
entry of customer and invoice numbers:
dim reply as c
dim q as p
reply=ui_get_text("Search","Enter customer #, invoice #, or date")
if reply="" then
end
end if
select
case eval_valid("{"+reply+"}")=.t.
' then it's a date
query.filter="between(date,{"+reply+"},{12/31/200})"
query.order="date"
q=table.current().query_create()
case left(reply,1)="C"
' it's a customer
' strip off the leading "C"
reply=right(reply,len(reply)-1)
' now pad on the left with enough zeroes to make it 8 digits in all
reply=padl(reply,8,"0")
query.filter="between(customer_id,reply,'99999999')"
query.order="customer_id"
q=table.current().query_create()
case else
' it's an invoice_number
' pad on the left with leading zeroes to make it 6 digits
reply=padl(reply,6,"0")
query.filter="between(invoice_number,reply,'999999')"
query.order="invoice_number"
q=table.current().query_create()
end select
if q.records_get()>0 then
parent.resynch()
end if
q.drop()
end
Script 2. Improved script for performing search.
I put this code onto a Search for invoice button on the Invoice
form in Invoice.adb.
Figure 1. Search button in action.
Do you want to see the Search button in action? If your browser
supports Javascript, then Click on the yellow Search button
on the form for a demo!
I'm sure that you can find many uses for a multipurpose Search
function in your applications.
7/4/98 - pkw
Don't forget, we need your feedback to make this site better!