faster and more effective searching, easier query entry
With the overhaul of the ChemFinder database machinery, it is possible to offer improvements in searching and list handling. At the same time, we have made several enhancements to make entering queries more convenient.
Updated 16 Jun 98 for Alpha2
Query Mode Enhancements
When you choose Search Enter Query, several new things happen:
If there is a subform:
Search Over Current List
With ChemFinder 5, it is easy to refine search results. Search once, get a hit list, then pose a new query and search again. If the Search Over Current List option is in effect, the second search will automatically proceed only over the hits from the first. Successive searches will keep refining the list, and the searches will become faster as the list becomes smaller.
There is a new button on the Search toolbar for Search Over Current List. The button is deactivated if there is no current list (that is, the current list is the entire database).
To search over the current list:
To restore to the full list:
Search Form and Subform Simultaneously
With earlier versions of ChemFinder, it was difficult to carry out searches which had query components referring to different tables. In ChemFinder 5, this is easy. In a form which contains subforms, you can now enter query components in any box on the screen, and ChemFinder will deliver the complete and correct results in a single search.
To carry out a combined search:
Unlimited Hit List
If you searched over large databases with earlier versions of ChemFinder, you might have encountered the limit on the number of hits allowed in a hitlist (default = 2500). In ChemFinder 5, this limitation is now lifted. Hit lists can be any size.
When a search gets a large number of hits, ChemFinder 5 creates a temporary table to store them, then joins that table with the previous hit list. If you are using a read-only database, such as one on CD-ROM, the program will be unable to create the table. You will NOT automatically be offered the opportunity to create a writable "portal" database in which to complete the search, but instead will get an error message suggesting you create one. See the Attach Table feature description for more information.
Note: in Alpha2, there is an artificially low limit on the minimum number of hits required to create a temporary table. The limit is 150. When you do a search which gets more than 150 hits, a temporary table will be created, or you will get an error message if you are using a read-only database. This limit is low so the temporary table can be thoroughly tested. We plan to raise it to around 1500 before beta.
Note also that this limit applies during the structure portion of a combined search. That is, if you have a search which involves, say, both SSS and molname, then a temp table will be created if the SSS portion finds more than 150 hits, regardless of how many hits result from the total search.
In substructure searches with ChemFinder 5, you can now specify whether or not fragments of the query are allowed to overlap within hit structures. For example:
In earlier versions of ChemFinder, query 1 would always hit both target structures 2 and 3. In ChemFinder 5, you can prevent hitting 2, in which there are atoms in common when query fragments are mapped to the target.
To specify fragment overlap preference:
Extraneous Fragments in Full-Structure Searches
In ChemFinder 5, there is a new checkbox in the Search Properties dialog, intended to allow more precise control in full-structure searches. When this feature is checked, the target is hit even if it contains extra molecules. However, it is not hit if it contains extra atoms attached to the molecule(s) that would otherwise correspond to the query. (That would require a substructure search, not a full-structure search.) This is important for reaction searching, as these often have extra unforeseen reactants or products, and for searching compounds which may be present as salts, complexes, or mixtures.
To specify the Extraneous Fragments preference:
Restore Previous List
When you do a search with Over Current List in effect, the result is a subset of the list you are searching over. Another search will yield a subset of that list, and so on, so you create a little "stack" of lists. The command Restore Previous List on the Search menu moves back one level, to the list in effect prior to your last search.
When you restore the previous list, the current list is discarded. The restored list is automatically positioned at the same structure you were viewing in the subset list. You can keep restoring previous lists until you get back to the full database.
There is a new button on the Search toolbar for Restore Previous List. The button is deactivated when there is no previous list available (that is, the current list is the entire database).
To restore the previous list:
New Combined Search Algorithm
In ChemFinder 5 Alpha2, we have redesigned the algorithm for carrying out combined searches involving both structure portions (structure, formula, mol weight) and text-only portions. This redesign also applies to searching combined forms and subforms. We are making more complete use of the Jet SQL search engine, using heuristics to carry out the search in an optimum way, and handling queries over tables with more than one structure column.
The new algorithm works as follows. Prior to the search, all components of the query are collected into a tree-structured query object. Each component is parsed and analyzed as far as possible for proper syntax. If there is an error, it is reported, the search is not carried out, and you are returned to query mode with the (faulty) query on the screen.
If the query is acceptable, then the structure portions of the query are executed, both in main form and subforms. Where possible, previous lists are used to restrict the scope of the structure searches. If there are no hits from this step, the search is aborted; otherwise, the results of the structure searches are converted to SQL as IN clauses, either giving a list of structure ID's or naming a column in a temporary table. These SQL clauses are combined (using AND) with other portions of the query, using sub-selects for subtables,. The entire SQL query is then run, and the resulting list activated.