Performance of drop-down filtered value list

[Mandu]

I have a Customer table containing about 600 records, self-joined to filter out those 50 or so marked Inactive. I'm using a drop-down list with auto-complete to allow user to select from an active customer name. It works fine except that it's slow (the auto-complete can't even keep up with my typing). The culprit seems to be the filtering relationship: it's snappy enough when I use the unfiltered Customer table to populate the value list.

Adding a new customer is rare enough that I could generate a pre-filtered Active Customer table whenever a name is added, and use that for the value list, but short of that are there any speed-up ideas for the dynamic filtering I'm currently using?

Thanks,

Chap

[Søren Dyhr]

I would say it's an travesty of structured approach, who would you expect of your users would feel that 600 customers really is cool and necessary each time you should enter one in a field, you should scrutinize the needs to know vs the nice to knows. I do usually have either a series of dropdowns to narrow the choises to fall in ten categories for each selection, or a type aheadish approach filling a portal with relevant matches.

--sd

[Mandu]

I would say it's an travesty of structured approach, who would you expect of your users would feel that 600 customers really is cool and necessary each time you should enter one in a field, [...]

No offense, but I can't understand your English here.

Are you saying that 600 is too many customers to have in a pop-down list? Truthfully, about 2/3 could probably be eliminated. But I don't find 600 to be a problem - the customer identifies himself, and we start typing. After a couple of keystrokes the autocompletion has drastically shortened the list. The only problem is performance when the value list is dynamically constructed from a field through a relationship.

I'll have a look into using a portal - have never tried that before.

Thanks,

Chap

[Søren Dyhr]

Are you saying that 600 is too many customers to have in a pop-down list?

Yes! Only selections within 10-15 makes sense, when using a popup as selection thingy.

The only problem is performance when the value list is dynamically constructed from a field through a relationship

I wonder if you could explain how this selfjoin is made? There might be a point in stacking all the excemptions in a multiline key instead and use the uequal relation, or thats perhaps exactly what you do at present? In a network will a global field as key be a cause for chatter over the connection.

--sd

[Genx]

Are you saying that 600 is too many customers to have in a pop-down list?

I don't know, it's not so bad if it's auto complete... I have around 5000 entries in one dynamic list, works pretty well, narrows it down to about 10 after 5 characters are entered.

[Søren Dyhr]

Genx we're talking fast typers here

--sd

[Søren Dyhr]

Genx we're talking fast typers here

--sd

[Genx]

Don't see the relevance... but i do 80 words a minute on a good day lol.

[Mandu]

I wonder if you could explain how this selfjoin is made? There might be a point in stacking all the excemptions in a multiline key instead and use the uequal relation, or thats perhaps exactly what you do at present?

--sd

To be honest, I don't know how I originally had it. I've simplified it as follows:

Invoices are related to Customers_for_value_list:

Invoices::g_Status_Active = Customers_for_value_list::Status

AND

Invoices::g_Type_Group = Customers_for_value_list::Type

The Value List is defined as all values from field Customers_for_value_list::Customer_Name.

And it's working fine, quite snappy, etc. I know that my original solution involved "use only related values starting from..." but the rest of it escapes me now.

In a network will a global field as key be a cause for chatter over the connection.

No network in this solution [yet?] but that's good to know.

I have to agree with genx - the mere number of values, by itself, doesn't seem particularly important: the user doesn't have to be aware of the length of the list, when using auto-complete.

[Søren Dyhr]

How are these globals give value, and what values is put into each of them, and when??

I can only guess what the objective are here, to prevent writing a new invoice to inadequate payers or such??

--sd

[Mandu]

How are these globals give value, and what values is put into each of them, and when??

I can only guess what the objective are here, to prevent writing a new invoice to inadequate payers or such??

--sd

The globals are constants, initialized at file initialization time.

I am writing a front-end to an existing Peachtree bookkeeping system. It has its own Customer table, including columns like Inactive? and Individual? . I want to export the Peachtree Customers to my custom FM8 Invoice system, which creates and prints invoices and then exports the data in a form that PT can then import (obviously I think I can do a better job of writing an Invoice GUI than the Peachtree folks ). I don't want to clobber the Customers table in any way, like deleting all Inactives and non-group customers, because I may add a Customer Info interface to the FM solution and want to then be able to export that back to Peachtree. However, my interface isn't intended for Individual customers or "Inactive" customers so I want to eliminate them from view. That's the objective.

[Søren Dyhr]

I start to wonder if the multi-criteria relation based on two constants are to blame for this ...well I have no idea. But I came up with another way to establish the selection, and would be interested to know if this improves or worsen the artifacts you experienced??? ...the whole problem is due to the large set which should be availiable but at present are out of sight, if you suddenly should manipulate with the scrollbar of the dropdown exhibits.

I still think the key to matter is to approach the selection a fair bit more structured! Since whats apparently is happening is that a list is custommade just in time to be shown, and the more filtering being introduced that slower it works.

We exactly have the same thing with records having large portals with a scrollbar, here does it make sence to show a pagefull a time with a fixed lenght portal, and then either switch to a new layout or tab when the remaining data needs to be addressed.

The template attached is a test of a different approach to strain the availiable persons in a popup.... which might or might not be faster, perhaps insignificant as alternative approach??

----------

Now after some hours considerations are there something that doesn't add up. You say the relation is a selfjoin, this must mean that you look at the name entered in previous invoices, based on the status of a pair of fields to be found in each invoice... this is a bit strange how do you ensure that this status is identical in each of the same customers invoices.

With two criterias working could each individual invoice have 4 combinations or categories it belongs to if the criteria relies on knowledge a relation away of found via say GetNthRecord( or lookups.

They are bound to be lookup or autoenter otherwise won't it index as foreignkey for the multicriteria. It needs to get syncronized each time an invoice gets paid ...alright it could be done via a GTRR on a selfjoin only on the customers name followed by a relookup. But what if a company suddently changes into a chartered company with different funding, I guess you have various classes of liability in your country?

Are you getting my drift, linking on human enterable data for relations are flaky to say the least (allthough the valuelists remedy some of it), and further more will the indexing process when calling a dropdown be simpler if data is pushed into a related table. Perhaps a more by the book relational structure will improve the speed issues found??

--sd

dropdownfiltered.zip