Value List - Too many records

[nelliott]

I use a drop down menu that lists the name field from a different database. The problem is that this database has about 20,000 records. The result is that when you try to drop the list down it takes 15-20 seconds (sometimes longer!) to appear.

Does anyone have a solution for speeding this up or narrowing the list that is displayed so that I can find the required name faster etc.

Thanks

Nick

[LiveOak]

The primary approach would be to reduce the one large selection to a number of smaller selections. For instance, if you had a complete list of cities in a country, you might instead select a state and show a smaller list. Some type of categorization to reduce the 20,000 item list into a number of smaller lists.

-bd

[The Bridge]

I implemented a filter function for a client who wanted to limit the number of companies appearing in the drop-down list in a related file. This may help you:

In your main file, define field:

List_Filter_Key (global, text)

In your related file (the value list source), define field:

List_Filter_Foreign_Key (text, indexed) = Left (FieldName, 1)

Define a relationship between Main List_Filter_Key to Related List_Filter_Foreign_Key.

Then base your value list in Main on that relationship, drawing values from FieldName.

This is a basic filter which will populate the value list with entries beginning with the letter appearing in List_Filter_Key.

I hope this gives you a good start.

[nelliott]

Thanks Peter, I'll give it a go!

Nick

[nelliott]

I've implemented it and although it works fine, it doesn't seem to have made it any faster and I still have to sit there with the sqiggley line for about 15 seconds - Any ideas?

[The Bridge]

Well, off the top of my head you could eliminate the value list altogether and use the Paste from Index script step. That may be more your speed, heh heh. hm.

[danjacoby]

Maybe indexing the appropriate fields would speed things up?

[Pupiweb]

I'd use a filtered portal: get a tip file named Clairvoyance at www.databasepros.com and see how it's done

Basically you'd have a global text field in the master file related to a field in the related file, calculated as:

Left (TheField,1) & "PP" &

Left (TheField,2) & "PP" &

Left (TheField,3) & "PP" &

Left (TheField,4) & "PP" &

...

[PP is a carriage return]

So if the field has value "abcd" the calc will return

a

ab

abc

abcd

.....

When in the master file you enter "abc" in the global field all records whose field starts with "abc" will be displayed in a portal

Entering more digits will filter more accurately; clicking on an portal row will launch a script that will select the item

There are then plenty of variations you might want to add, like filtering on more than one field, filtering also records who have the string in the middle of the field, moving the filtering to a Dialog file (my favorite) ...

In general it makes for a great interface, users love it and it's a real life saver when replaces value lists with hundreds of items