If you use Alpha Four, you are probably very aware of indexes - how to create them, how to use them for sorting and selecting records.  If you ever created a relational set, set up a database lookup or used report grouping, you had to prepare by creating an appropriate index ahead of time.

Alpha Five creates indexes automatically

With Alpha Five, indexes are a no-brainer.  Alpha Five will automatically create the indexes it needs on the fly or build a temporary "query" to do the job.  Here are two examples:

Example # 1 Field Rules Lookup

Your database keeps track of patient visits.  When you enter a new Visit record, you want to choose the patient from an alphabetical list of patient names.  To make this work, you would go to the Visit table's Field Rules and create a lookup on the Patient table.

In Alpha Four you would need to create two indexes in advance, one on Patient ID and another on Patient Last Name + Patient First Name.  In Alpha Five, all you need to do is go to directly to Field Rules.  When you set up the lookup, you can simply fill in the Linking field by choosing the Patient ID field.  Fill in the Display Expression by choosing the Patient Last Name + Patient First Name fields in the Expression Builder.  Alpha Five will automatically use existing indexes or build new ones if necessary.

Example #2 Report with Grouping

You want to group and subtotal a report on "Patient Source".  You insert a group break into the report layout.  But wait -- in Alpha Four, you would also have to create an index, range or search/sort list on the Patient Source Field.  Otherwise, the records would not be grouped correctly when the report prints.  Alpha Five, however, will automatically use an existing index or build a temporary query when the grouped report prints.

Ordering and Selecting Records

Alpha Five offers a number of new ways to sort, find, filter and query records quickly.  For example, Alpha Five does not limit you to sorting and finding on indexed fields.  You can click on any field, choose Quick Sort and then use Find by Key just like the way you did a "Find" in Alpha Four.

If you are an Alpha Four user, take the time to read Chapter 3 "Ordering and Selecting Records" of the Alpha Five User's Guide.  Access this user's guide from the Alpha Five menu by choosing Help > Books.

Look forward to even more ways to sort, find, filter and query records in the upcoming Alpha Five Version 5.

Lightening Query Optimization

Yes, it's true that you don't have to plan your indexes in Alpha Five the way you did in Alpha Four.  Alpha Five has a built-in feature called "Lightening Query Optimization" or LQO that will utilize existing indexes when you run a query or apply ordering or filtering.  Instead of processing every record in the table, Alpha Five uses the index to get to the requested records quickly.

You can take further advantage of LQO by creating additional indexes on fields that you need to query, sort and filter on frequently.  For more information about how LQO works, consult Chapter 3 of the Alpha Five User's Guide or search the Alpha Five forum on the Alpha Message Board.

Rebuilding Indexes

If you have trouble finding records that you know are there, or records appear to be duplicated when they are not, rebuilding the indexes usually solves the problem.  To do this in Alpha Five:

1.	Close all open forms and browses.  If you are on a network, make sure that the other users exit Alpha Five.
2.	On the Alpha Five Control Panel, choose the Tables/Sets tab.  Right-click on the table whose indexes you wish to rebuild.  Choose Define Indexes1.
3.	Click the Build button.  When asked "You didn't make any changes.  Would you like to rebuild all indexes?" click Yes.
4.	Close the Index Builder window.  If desired, repeat steps 2-4 for other tables in the database.

Alpha Five Fact:  In Alpha Five, all indexes for a table are stored in the same file.  For example, for table Patients.dbf, there is a single index file named Patients.CDX.  In Alpha Four, each index was stored in a separate .NDX file.

Index FAQ's

What is an index "tag"?

You may see the term "index tag" used in Alpha Five Help and documentation, especially in the Xbasic Reference Manual.  The index tag is simply the name of the index.  To see the names or "tags" for your indexes, go to the Alpha Five Control Panel, Tables/Sets tab, right-click on the table and choose Define Indexes.  In the Index Builder window, the tag name appears under the column heading "Index Name".

What if I want to choose a specific index in Alpha Five, the way I did in Alpha Four?

In Alpha Four's View or Browse mode, you can set the primary index by choosing "Index" from the menu bar.  The equivalent command in Alpha Five's menu bar is Records, Advanced, Select Index/Query.