Automatic vs. demand indexes

[Nancy Palmquist]

I just wanted to post a little filePro difference that might interest my 
collegues on the list.

We all use browse lookups in our processing and I discovered a real 
difference between how the automatic and demand indexes work in a browse.

Start with an automatic index built on two fields:

State & City
2 characters for state and 8 Characters for City

If I use a browse lookup and give a key with the state and the first few 
letters of the city, it will put the user at the first match for the 
city from the letters I gave.

For example,  PAMA  would match PA and the first city starting with MA.

This would list alphabettically and would give use a reasonable chance 
to hit near the city we need.

I have done this a million times and it always worked.

However, I had cause to try the exact same thing with a Demand index. 
Built on the same two fields: state & city
Built with the same two lengths: 2 & 8

I needed to select a small subset of items and the drop command in 
processing took too long so an index which pre-selected the required 
items was more useful.

Now here is where it gets wierd.  I give it the key "PAMA" and it 
displays the first city it PA, which is Allentown.  Not anywhere near M, 
which is what I wanted.  It did not match into the second sort at all. 
No way I could make it work.

But I was lucky, my state and city are fields 48 and 49, so I changed 
the sort to be on field 48 with length 10 to include the first 8 
characters of the city and now it works exactly like the Automatic index 
version.

Now a match in a browse with an automatic index always continues into 
the next sorted field, but I guess demand indexes will only allow a 
match in the primary sort field.  Very unexpected.

Nancy

-- 
Nancy Palmquist 		MOS & filePro Training Available
Virtual Software Systems	Web Based Training and Consulting	
PHONE: (412) 835-9417		   Web site:  http://www.vss3.com