4D Search Editor
There are three Search Editors in the program and scripts which executes searches. This outlines the nature of searching and its results on:
Action Menu > Search > Execute button without making any selection.
Action Menu > Show Current Selection > "Shift"- click on the Search button
The "Search Editor" contains the:
Criteria area that displays the Search Conditions as you create them or after you load them from a disk file.
Field area that displays the names of the fields in the current file . Indexed fields are shown in boldface.
Operator area that displays a list of comparison operators.
Conjunction area that contains three "radio" buttons that correspond to Conjunction Operators you can use to join several Search Conditions into a Compound Condition.
The Popup File Choice is where the desired file is chosen. If a different file is needed click and hold the mouse down in the "Popup File Choice" area. A list of the files appears. Scroll down and highlight the desired file and let the mouse up.
Value area where values are entered for Searching.
Search in Selection check box that permits performing a Search only on records in the Current Selection.
If Search in Selection is de-activated, the entire file is Searched.
Action buttons, that are used to save the Search Conditions, load previously saved Search Conditions, cancel the Search or perform the Search.
A "Search Condition" always has three elements: field name, comparison operator and value. The field name is from the current file such as Customers. The comparison operator (equal to, greater than, less than, and so forth) tells CommerceExpert/WebClerk to compare the contents of the field to some specified value. The value is the value to which each record is compared, then, selected or not included in the current selection of records.
Suppose all the records of Orders with need date greater than 1/1/94 are needed for a report. The field name used is "Need date", "is greater than" is the comparison operator, and "1/1/94" is the value.
When the database is Searched, CommerceExpert/WebClerk compares the contents of the field in the Search Condition with the value specified. The new current selection is made up of records that meet the Search Conditions. The new Current Selection can include no records, one record, a group of records, or all the records in the file. The new Current Selection is displayed in the Output layout.
In multiple-file databases, Searching on fields from other files is possible, provided that an automatic relation between the files has been established. The appendix contains information on the relations between the files.
Indexed and Sequential Searches
CommerceExpert/WebClerk can carry out Searches very quickly if it has an ordered list, or index of the records from which to work. An ordered list is called an "Index". An index is associated with a particular field and is stored on disk as part of the data file. In the "Search Editors" "Index" fields are either bold or have an "i" next to the field name.
A Search that is done without an index is slower because the program must start at the beginning of the file and examine each record until it finds the required record. To be sure that it has found all the required records, it must examine every record in the file sequentially. This process is called a "Sequential Search".
If an index is available, the program "knows" where the target records are located. If all contacts with the last name Smith are being Searched for, the program knows where in the file the Smiths are located. Thus, it doesn’t need to examine every record in the file.
A good analogy to an index is a card catalog in a library. The card catalog is an alphabetized list of all the books in the library. Each record in the catalog contains information about where the book is physically located. If you are looking for a particular book, it would be very inefficient to conduct a "Sequential Search" of the library’s entire holdings. It is much faster to consult the card catalog, obtain the location of the book, and then Search the particular shelf on which the book is stored.
CommerceExpert/WebClerk has indexed those fields that are likely to be used often for Searching. As records are imported or created, CommerceExpert/WebClerk automatically updates all indexes. When searching, CommerceExpert/WebClerk automatically uses indexes if they are available. Indexes fields show up in bold type or with an "i" in the "Search Editor".
When a Search Condition is written, CommerceExpert/WebClerk is told how to compare the value specified to the contents of the database. For example, the Search Condition, "Last Name equals "Smith" uses the "is equal to" comparison operator. It tells CommerceExpert/WebClerk to compare the values in the Last Name field to the value "Smith".
The following comparison operators are available:
Is equal to
Is not equal to
Is greater than
Is greater than or equal to
Is less than
Is less than or equal to
Does not contain
Wild Card Search
CommerceExpert/WebClerk also has a wildcard character (@) that substitutes for one or more characters in the value being Searched.
Use the wildcard character to do a "Begins with" search. For example, to find everyone whose last name begins with "S," use the Search Condition, "Last name is equal to S@." That is, use "S@" as the value used for Searching. Using the wildcard character with the "is equal to" comparison operator is equivalent to a "Begins with" comparison.
The @ symbol is used throughout CommerceExpert/WebClerk as the wildcard character, and is not limited to Search conditions.
The wildcard character can be used only with "Alpha" or "Text" fields.
Simple and Compound Searches
Searching can be done on one or more fields. A Search on one field is called a "Simple Search". For example, the search "Last name equals 'Smith'" is a Simple Search. When a Simple Search is performed, CommerceExpert/WebClerk examines the contents of one field when Searching the database.
A Search on two or more fields is called a Compound Search. When a Compound Search is performed, separate Search Conditions are combined using a Conjunction Operator. The Conjunction Operator tells CommerceExpert/WebClerk how to combine the results of the individual Searches. There are three Conjunction Operators:
And finds all the records that meet two conditions simultaneously. For example, the Search Condition "Find all the Customers who have NST for a Rep and who's Sales YTD is over $500.00", finds the records of only those NST Customer's who's Sales YTD are over $500.00.
Or finds all the records that meet either of two conditions. For example, the search condition "Find all the customers who have NST for a Rep or who's Sales YTD is over $500.00", finds the records of all the Customers who have NST for a Rep, regardless of the Sales YTD, as well as all the Customers who's sales YTD are over $500.00 regardless of the Rep they have.
The Conjunction Operators create a Compound Search, such as "Find the Customers in New York who's date of last sale is less than 1/1/94 and last year's sales were over $500." CommerceExpert/WebClerk finds all the New York Customers who had purchased over $500 of goods last year but have not Ordered this year.
Using the Search Editor
The "Search Editor" is a "General-Purpose" editor that can be used to create Simple or Compound Search conditions.
The "Search Editor" has the option of Searching through the Current Selection of records, or all the records in the file.
The "Search Editor" has a "Save" feature which saves Search Conditions to disk. The saved search conditions can be loaded when the search needs to be repeated.
The "Search Editor" allows Searching on any field in the current file and any fields in related files. For information on which files have a relation see the Appendix. Search on subfields in the current file or subfields in related files is also possible but is slow.
If the database includes subfiles, they can be used in your Search Conditions. A search on sub-records creates a new current selection of "Parent" records, not sub-records. This group of "Parent" records contains at least one subrecord that meets the Search Condition. However, the Search does not remove the other sub-records from the "Parent" record. All sub-records stay attached to their "Parent" records.
For example, Line Items are a subfile of "Order" records. The fields in the subfile, called subfields, are Item Number, Description, Price, etc. To find all the Orders with a Item number "475", involves searching the Line Item subfiles for "475" in the Item number subfield. CommerceExpert/WebClerk finds all the Order records with at least one Line Item subfile with the Item number "475" in the Line Item, Item number subfield. The selection is not just the sub-records themselves.
To create a search condition, follow these steps:
1. Action menu > Search
CommerceExpert/WebClerk opens the "Search Editor", highlighting the portion of the "Criteria" area where a field name will appear. There is a blinking marquee around the "Field" area.
2. Click a field name for the Search Condition.
CommerceExpert/WebClerk displays the field name and highlights comparison operator spot in the "Criteria" area. The blinking marquee moves to the "Operator" area.
3. Select a Comparison Operator.
CommerceExpert/WebClerk adds the comparison Operator to the "Criteria" area and advances the highlight to the spot to where the value will appear. The marquee moves to the "Value" area.
4. Type the Value for which to Search. In a "Text" or "Alpha" field the wildcard character, @ can be used following the Value to request a "Begins with" search.
If the field selected is a "Boolean" field, CommerceExpert/WebClerk displays a pair of "radio" buttons. If the field selected is a subfile, a window listing the subfields is displayed.
5. If a Compound Search condition needs to be created, click one of the "Conjunction" buttons (And/Or) and repeat steps 2 through 4.
Note: When you build a Compound Search Condition, CommerceExpert/WebClerk evaluates the conditions in the order in which they appear in the "Search Editor", i.e., from top-to-bottom. That is, there is no precedence among the conjunctions. Thus, if you have used more than two Simple Search Conditions in building the Compound Condition, the order in which you enter Search Conditions can affect the results of the Search.
As the Search Condition is built, it can be modified. Modify existing parts of the condition by clicking the area to change and selecting or typing a new entry.
The Search Condition can be removed by choosing: Edit menu > Clear. In a Compound Search Condition, one line of the condition can be removed by choosing: Edit menu > Cut.
6. To save the Search condition as a disk file, click on the "Save" button and enter a file name in the "Create-File" dialog box.
Search condition do not need to be saved to perform the Search.
7. To Load a previously saved Searched Condition, click on the "Load" button and locate and open the condition.
8. Click the "OK" button to perform the Search or click "Cancel" to quit the "Search Editor".