Efficient Search

[JetLi]

I'm new to alpha five, my question is, can someone please suggest the most efficient way to search a table or a set with millions of records,
i saw a desktop tutorial which says the dropdown browse control is good for a small table, what is the best way to design a form so that end users will efficiently
search a table or set with millions of records faster? What is also the recommended way of setting up a lookup table with a million record e.g. a product lookup table in an invoice application.Thank you!

[MoGrace]

Re: Efficient Search

Hi Jetson,
Welcome to the board.

I suppose first off is knowing how these records are defined. Do you have a unique product id field? Is it possible to group or subgroup the products by a type or class or vendor field? Is there an order or orders placed first that can be converted to an invoice? Lots of questions to be considered first to find the answer!

[Ted Giles]

Re: Efficient Search

Jet,
As Robin says, the structure of the data is important when designing searches.
If the search is simple - like Last Name and First Name - then an Index of the data would work.
If the search is more complex - like "Show me the name of all the companies who purchase socks AND scarves, by Town or City within State in descending $value order" there are other ways.

Please let us have an example and the search criteria.

[Ray in Capetown]

Re: Efficient Search

"search a table or set with millions of records faster? What is also the recommended way of setting up a lookup table with a million record e.g. a product lookup table in an invoice application."
In that simple sense, for a product table, I would use a defined index on the product field and a Record list combo box lookup
That will allow users to start typing to get to the region of the query if not the exact item and the lookup will appear instantaneously.
A query could be as fast - but may occasionally perform a recreate. Index is always uptodate, and a routine reindex facility will ensure that in a controlled manner.
Anyone else s opinion and other factors should also be considered

[JetLi]

Re: Efficient Search

Thank you all for the replies, maybe i'l put the question this way, what data entry design would be best if the lookup table contains many many records, e.g. in an invoice app, how will you define the item as a lookup field for the items to be invoiced? will i use the super control drop down browse search? what will i use to lookup for the items table?

[Ted Giles]

Re: Efficient Search

Jet, a bit more information please.
Are the items single entities, like
Cup/Saucer/Plate, or are they more like
Shirts/Beige/Blue/Yellow
Sized 14/14.5/15

In the Cup example your lookup would be
Cup
Saucer
Plate

In the Shirts example, it would be more like
Shirts
..Beige
..Blue
..Yellow
...15
...15.5
...16

Each line in the invoice could be made up of ONE item "Cup", or one item with three components, e.g. Shirt, Blue, Size 16.

What's the application?
Lookup from an Items table is easier to maintain as a user. It allows them to add/change/delete lookup items.

[JetLi]

Re: Efficient Search

Thanks Ted, It is just a simple patient management and pharmacy inventory, e.g. a patient is prescribed with a medicine and the user will select from thousands of medicines, I'm just wondering if thousands of items will slow down the application if the items are loaded from start to a combo box.

[Ted Giles]

Re: Efficient Search

Jet, the easiest way to do this, IMHO is to have a simple formulary in a Table. This has a number of benefits, like ease of maintenance and the Table can be put into a Lookup in the Field Rules.
Having worked in a surgery and pharmacy, the end result might be a bit more complex.
If the scrip is specific and calls for a named medication, the user can start typing the name of the med and it will populate the field. The danger with this is that Alphastatin 123 and Alphastatin 124 might get
mixed up. In this case you might require double entry for security reasons.
If the med is "generic" and there is an optional list, then the Lookup gets a little more complicated - but not much.

I presume that you have a means of managing contraindications outside of the pharm, so having gone slightly off track, the easiest way is to have a Lookup with a maintainable table into which you can put "alternatives" in case the med prescribed is out of stock.

[JetLi]

Re: Efficient Search

Thanks Ted, I have a problem on how to bill a patient with deductions and automatic bills applied everyday.I'm trying to create a Hospital management app that is mainly focused on billing and recording of patients diagnosis.