User Manual Chapters to Read
Topic: Query by Example
Now that you've gotten your feet wet with some basic data entry we're going to learn how to lookup data once it's in a catalog.
several years in business your catalogs will grow in size and it will
quickly not be practical to use the green arrow keys (Next Record,
Record, etc) to find the data you are looking for. Therefore you need
how to do Queries.
the Vendors Catalog. There should be 10 records in the Catalog. Let's
add a new
Vendor. Click the New Record button and add a vendor called "North
American Shoe Company". In the address field put "123 East 45th
Street". You do not need to enter any more information. Now press Save.
should be 11 records in the Vendors Catalog now. Press the Copy button
toolbar. This creates a new record and copies all data into this new
you look at the records bar, it should now show 12 records. Notice all
in the toolbar are grayed-out except for Save and Delete, the same way
would be if you had clicked New Record.
now change the name of this vendor to "North American Clothing
Company". Now press Save. The address information stays the same. The
button can save you a lot of data entry time if you are strategic in
use it. For example, when you are ready to enter Styles, you can save
entering all the styles form the same vendor or department and press
key so you don't have to reenter this information every time. The only
to keep track of the records bar to make sure you don't get lost and
accidentally enter the same record multiple times or accidentally
existing record because you got distracted.
do a Copy one more time. Press the Copy key and watch the record bar
13. Instead of "North American Clothing Company" rename this vendor
"Cool Clothing Company". Now press Save.
that you have these three additional vendors (13 total) we can learn
how to do
Queries. Click on the First Record button.
a Query should become second nature for you, like breathing. When you
lookup a phone number (for example, Bob Samuelson) in the phone book
you do not
flip through every single page in the book until you get to Samuelson,
That is incredibly inefficient. The same principle applies when working
your database: you want to find the information you are looking for as
are three steps for doing a Query. You will always follow these three
Click on the Clear Form button. Notice every field in the catalog is
The computer is now expecting a query and waiting for the next step.
Enter the search criteria. In this case, go to the Company field and
"North%". The % sign acts as
a wild card. You are telling the computer to find every vendor in the
that starts with the word "North". (If you are running XpertMart on
Java 5 you need to press <Return> after you've entered the search
Click on the Lookup by Example button. Pressing this button tells the
to execute the query and pull all records that match your search
catalog should now be displaying the entry for North American Shoe
(unless one of your vendors also starts with the word North in which
vendor would be displayed). This is the first record that the computer
look at the records bar: it should show 1,2,13. What this means,
right to left, is that out of a total of 13 records, 2 match the search
criteria, and you are looking at the 1st of these 2. It's as if the
Catalog were a file cabinet and you had pulled out 2 folders to look
is the subset of records you are now working with out of a total
press the Next Record button. You should be looking at North American
Company now. This is the other record that matched your search
the Next Record, First Record and Last Record buttons to jump around.
that you are now only looking at those vendors whose name starts with
Using Queries can be very useful when you want to work within a narrow set of records instead of the entire catalog which can be unwieldy as it grows.
go back to working with all records in the catalog you need to press
All Records button. Now look at the records bar. Notice that the middle
changed from 2 back to 13. If you use the green arrow keys in the
you'll notice that you now more through all records in the catalog.
let's do another Query. Repeat the 3 steps mentioned above, only this
enter "north%" as the search criteria. You'll see an error message
that says "No Record Found." This is because all searches are
There are not any vendors that begin with the word "north" only those
that begin with "North". Because it is case sensitive it is very
important that you be consistent in how you enter data: all caps, mixed
now let's do another Query. This time use "North" as your search
criteria. Once again you'll see that no records were found. This is
without using the % wild card you are asking the computer to pull all
that are simply called North. On the other hand, when you use
"North%" you are asking for
all vendors that start with the word North.
use that wild card again, which can be very handy. Do a new Query, this
Step 2 use "%Clothing%" as your search criteria. By using two % signs
you are telling the system to find all vendors that have the word
"Clothing" anywhere in their name, not just at the beginning.
Query pulled 2 records (more if any of your first 10 vendors have the
"Clothing" in their name): North American Clothing Company and Cool
Clothing Company. Use the green arrow keys to navigate through the
Once again look at the records bar. Now press See All Records to return
working with the entire catalog.
a few more queries. For example, you could do a Query for "%American%".
Try finding some of your own vendors this way. A Query for "T%" will
pull any vendors that start with a "T" and so on.
is very tempting when your database is small to just use the green
instead of doing a Query. But this will become a problem as your
grows. A common beginner's mistake is to always use the Last Record
jump to the end of the catalog and then work their way back with the
The problem with this is that when you
click on Last
computer will open every single record in the catalog the same way it
you looked at each record individually. If you have 400 records in your
catalog, pressing the Last Record button means you will be waiting for
computer to open all 400 vendor entries. You may be waiting for some
Advanced users will quickly learn to rely on Queries to navigate through the catalog. In fact, there is a configuration option (hyper link) in XpertMart that deactivates the Last Record button so it cannot even be used. If you have a database with 12,000 items and you press the Last Record button in the Items Catalog...you might as well brew some coffee while you wait. Or learn to stop using the Last Record button!
addition to doing a Query by Example as we've been doing, there is
to help you navigate the data in a catalog and find what you are
the List View button.
on the List View button: this opens a new window with all of the data
catalog in table format. Click on the Company column header. This will
vendors in the catalog alphabetically in ascending order, A-Z. Now
click on it
again: it will sort in descending order Z-A. You can click on any
sort your data that way.
for the row in the table that shows North American Shoe Company.
on this row. You'll see the Vendors Catalog jump to this record. You
back and forth between the List View window and the Vendors Catalog,
on any row and watching the catalog open to that entry.
do a Query again, this time for "N%". As expected, this will pull a
subset of 2 (or more if you have other vendors in your catalog that
the letter N). Now click on the List View button. Notice that instead
all 13 records you are only seeing the subset that matched your search
a Query by Example and using the List View can be a very powerful
Let's say you have 400 vendors in your catalog and you need to find a
particular vendor that you know is in Italy but you are not sure what
do a Query by Example entering "Italy" in the Country field. In the
example we are using, this would narrow the search from 400 to 15. Now
List View and sort alphabetically. You are looking at your 15 Italian
in alphabetical order which should make it a lot easier to find the one
you've had enough practice doing Queries, you will want to delete the 3
vendors we created. Find each of those three vendors (practice those
yet again!) and press the Delete key. Watch the records bar change from
12 and so on. When you are done deleting these three, click the First
button to refresh the view.
are now back to your original 10 vendors in your catalog...and you know
do Queries! Add two more vendors so that your Vendors Catalog now has
Advanced Users (Optional)
User Manual Chapters to Read
Report Designer Video
Advanced users will also want to lear how to use the XpertQuery™ tool. After reading the XpertQuery™ chapter go back to your Vendors Catalog. Click on the XpertQuery™ button. This will open the XpertQuery™ screen. Click on the Execute Query button (lighting bolt) and then click on the Results tab. This is exactly what your data looks like in the database.
that the 11th vendor you added at then of the lesson has a
is because the three fictitious vendors we created occupied vendors_id
11, 12 and 13. Those internal vendors_id numbers will never be reused.
Understanding the difference between the place a record is displayed in
catalog vs. the internal id number the
database uses is important if you are to create your own sophisticated
click on the Sort tab. Under Tables select Vendors and under Fields
Country. In the Results column you'll see a drop-down menu of all
the catalog. Click OK. You'll see the Query has been executed in the
Now go back and open XpertQuery™
Click on the
SQL tab. This is the SQL statement that you created to execute the
*** Return to QuickStart Index Page ***
Copyright © 2000 - 2005 Dinari Systems LLC