XpertMart Quick Start Guide: Query by Example

XpertMart^™ Quick Start Guide

Lesson 4:
Query by Example & Copy/Delete

User Manual Chapters to Read
Topic: Query by Example

Other Resources

Lesson
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.

After several years in business your catalogs will grow in size and it will very quickly not be practical to use the green arrow keys (Next Record, First Record, etc) to find the data you are looking for. Therefore you need to learn how to do Queries.

Open 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.

There should be 11 records in the Vendors Catalog now. Press the Copy button on the toolbar. This creates a new record and copies all data into this new record. If you look at the records bar, it should now show 12 records. Notice all buttons in the toolbar are grayed-out except for Save and Delete, the same way they would be if you had clicked New Record.

Ok, now change the name of this vendor to "North American Clothing Company". Now press Save. The address information stays the same. The Copy button can save you a lot of data entry time if you are strategic in how you use it. For example, when you are ready to enter Styles, you can save time by entering all the styles form the same vendor or department and press the Copy key so you don't have to reenter this information every time. The only catch is 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 overwrite an existing record because you got distracted.

Let's do a Copy one more time. Press the Copy key and watch the record bar change to 13. Instead of "North American Clothing Company" rename this vendor "Cool Clothing Company". Now press Save.

Now that you have these three additional vendors (13 total) we can learn how to do Queries. Click on the First Record button.

Doing a Query should become second nature for you, like breathing. When you need to 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, Bob. That is incredibly inefficient. The same principle applies when working with your database: you want to find the information you are looking for as quickly as possible.

There are three steps for doing a Query. You will always follow these three steps:

1) Click on the Clear Form button. Notice every field in the catalog is now blank. The computer is now expecting a query and waiting for the next step.

2) Enter the search criteria. In this case, go to the Company field and type "North%". The % sign acts as a wild card. You are telling the computer to find every vendor in the catalog 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 criteria).

3) Click on the Lookup by Example button. Pressing this button tells the computer to execute the query and pull all records that match your search criteria.

The catalog should now be displaying the entry for North American Shoe Company (unless one of your vendors also starts with the word North in which case that vendor would be displayed). This is the first record that the computer found in the Query.

Now look at the records bar: it should show 1,2,13. What this means, reading from 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 Vendors Catalog were a file cabinet and you had pulled out 2 folders to look at. This is the subset of records you are now working with out of a total universe of 13.

Now press the Next Record button. You should be looking at North American Clothing Company now. This is the other record that matched your search criteria.

Use the Next Record, First Record and Last Record buttons to jump around. Notice that you are now only looking at those vendors whose name starts with the word "North".
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.

To go back to working with all records in the catalog you need to press the See All Records button. Now look at the records bar. Notice that the middle box has changed from 2 back to 13. If you use the green arrow keys in the toolbar you'll notice that you now more through all records in the catalog.

Now let's do another Query. Repeat the 3 steps mentioned above, only this time enter "north%" as the search criteria. You'll see an error message that says "No Record Found." This is because all searches are case-sensitive. 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 caps, etc.

Ok, 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 because without using the % wild card you are asking the computer to pull all vendors 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.

Let's use that wild card again, which can be very handy. Do a new Query, this time in 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.

The Query pulled 2 records (more if any of your first 10 vendors have the word "Clothing" in their name): North American Clothing Company and Cool Clothing Company. Use the green arrow keys to navigate through the records. Once again look at the records bar. Now press See All Records to return to working with the entire catalog.

Practice 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.

It is very tempting when your database is small to just use the green arrow keys instead of doing a Query. But this will become a problem as your database grows. A common beginner's mistake is to always use the Last Record button to jump to the end of the catalog and then work their way back with the Previous Record button.

The problem with this is that when you click on Last Record the computer will open every single record in the catalog the same way it would if 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 the computer to open all 400 vendor entries. You may be waiting for some time.
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!

In addition to doing a Query by Example as we've been doing, there is another tool to help you navigate the data in a catalog and find what you are looking for: the List View button.

Click on the List View button: this opens a new window with all of the data in the catalog in table format. Click on the Company column header. This will sort the 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 column and sort your data that way.

Look for the row in the table that shows North American Shoe Company. Double-click on this row. You'll see the Vendors Catalog jump to this record. You can toggle back and forth between the List View window and the Vendors Catalog, double-click on any row and watching the catalog open to that entry.

Let's 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 start with the letter N). Now click on the List View button. Notice that instead of seeing all 13 records you are only seeing the subset that matched your search criteria.

Doing a Query by Example and using the List View can be a very powerful combination. 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 they are called.

First 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 open the List View and sort alphabetically. You are looking at your 15 Italian vendors in alphabetical order which should make it a lot easier to find the one you're looking for!

When you've had enough practice doing Queries, you will want to delete the 3 fictitious vendors we created. Find each of those three vendors (practice those Queries yet again!) and press the Delete key. Watch the records bar change from 13 to 12 and so on. When you are done deleting these three, click the First Record button to refresh the view.

You are now back to your original 10 vendors in your catalog...and you know how to do Queries! Add two more vendors so that your Vendors Catalog now has 12 entries.

Advanced Users (Optional)

User Manual Chapters to Read
Topic: XpertQuery™

Other Resources
Report Designer Video

Advanced Lesson
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.

Notice that the 11th vendor you added at then of the lesson has a vendors_id=14. This is because the three fictitious vendors we created occupied vendors_id slots 11, 12 and 13. Those internal vendors_id numbers will never be reused. Understanding the difference between the place a record is displayed in the catalog vs. the internal id number the database uses is important if you are to create your own sophisticated queries.

Now click on the Sort tab. Under Tables select Vendors and under Fields select Country. In the Results column you'll see a drop-down menu of all counties in the catalog. Click OK. You'll see the Query has been executed in the Vendors Catalog.

Now go back and open XpertQuery™ again. Click on the SQL tab. This is the SQL statement that you created to execute the query.

*** Return to QuickStart Index Page ***