(The Comprehensive Search is available throughout the system. The example below refers to searching for Companies, but this could equully well be any other part of the system.)
If you select ‘View Companies’ from the Companies menu, and click the [More Choices] button on the first dialogue box that appears (or press <Command-M>), you will be presented with a more detailed search screen.
Here you can carry out more sophisticated queries. For example, you can find all the companies in London beginning with the letter a, who are the responsibility of a particular account executive (DAH):
Note that the [Find] button is bold, meaning that it is operated by the <Return> key - so <Tab> must be used to move between fields.
There are two menus available: Find and Functions.
The Find menu contains the names of all the related Enterprise 6 files in which one can search in order to find a list in the first file. Therefore, for example, selecting ‘Find Diary items’ from the View Companies More Choices window brings up the Diary More Choices window. The result of the search in the latter window will be ‘linked’ back to the Companies More Choices.
You can therefore search for all Companies whose Diary items meet certain criteria, producing, for example, a list of all Companies who have been called in the last month. The speed of all these cross-file queries has been optimised, and is particularly effective for multi-user systems and for ‘linking’ selections of less than 256 records.
Examples of queries where the File menu will be used are as follows: Companies can link to Products (of whom they are a Supplier), Service Calls and Jobs; the Diary More Choices can link to Products, Orders, Service Calls, Jobs, Attendees and Bookings - so you could, for example, find Diary items enquiring about Products worth more than £100, or Diary items for all Jobs Completed between two dates; the Products More Choices can link to Companies, Diary items or Order items - so you could find Products sold by Suppliers in a specific Town, or Products called about between two dates, or Products sold on a given Order or Orders.
Remember that there is, in theory, no limit to the number of links in a search, although the more links, the slower the search. For example, from the Companies More Choices window you could use the Find menu to find all Companies that have placed an order in the last month (linking to Sales Orders), have placed a service call in the last six months (linking to Service Calls) and have been to a sales seminar (linking to the Diary or the Events Manager).
When performing cross-file queries in this way, it is important to note that you should enter search criteria to the last More Choices window first. For example, if intending to list all Companies in a certain region who have been invoiced in the last month, you should go from the Companies More Choices window (leaving it blank) to the Sales Orders window (via the Find menu), enter the date criteria there, then return to the Companies window and then enter the region criteria. For cross-file queries to work properly, it is vital that this method of operation is followed.
The Functions menu of the Companies More Choices window contains the following options:
Set functions (including the Data List Manager)
This option has two modes, depending on whether you hold down the [Alt] key or not.
In Sets mode it operates in exactly the same manner as the [Sets] button from a list of records. It allows you, when searching on just one file, to go straight to an existing Set (which will have been originally saved using the [Sets] button). More significantly, though, it also allows you to use Sets when searching across files. Thus you could have at some previous time created a Set of Sales Orders meeting certain criteria, then later you could ‘View Companies’, select ‘Sales Orders’ from the Find menu, choose ‘Set Functions’, use the Set, and return to the Companies window with a list of Companies which are related to those Sales Orders.
Its other mode brings up the Data List Manager palette (see separate section).
Search operators
The ‘Equal to’, ‘Not equal to’, ‘Greater than’, ‘Greater than or =’, ‘Less than’, ‘Less than or =’, ‘Contains’ and ‘Does not contain’ menu items on the Functions menu are there really as an optional, helpful method of typing the following symbols into the beginning of any of the text (ie not number or date) search fields: =, #, >, >=, <, <=, @, and #@ (# is on the Macintosh).
If none of these symbols are typed or chosen from the menu, the queries will operate exactly as before, always performing an ‘Equal to’ search. Typing the = symbol can still be useful, though because if this is the only character typed in a field, it will find records with no data in that field.
Typing any of the other symbols performs the relevant comparison. This makes it possible, all in one search window, to find, for example, Companies that are Clients that don’t live in London (#London) and that are of Type greater than B2 (>B2). Remember that > and < queries on text fields don’t ‘understand’ numbers even if the data is looks like a number - so >100 might find 101, 102, 10A, 20 etc.
Multi-level queries
The ‘And…’, ‘Or…’ and ‘Not…’ menu items on the Functions menu are further tools permitting the building up of complex queries.
If, for example, you wished to carry out a staged mailshot, the first stage might be to mail all Companies in a certain area beginning with a or b. In this situation, you would bring up the More Choices window, type in an appropriate Area in the Area field and the letter “A” in the Company Name field, then select ‘Or…’ from the Functions menu and repeat the process, except using the letter “B”. Note that there is a Page Number in the top right hand corner of the More Choices window - this shows how many search layers you have introduced using this method.
Saving and loading search routines
You can save complicated search routines for re-use. Once you have input some criteria into a More Choice page you can choose ‘Save Criteria ...’ (<Command-4>/) on the Functions menu to take a copy of what you typed. You are asked to allocate a name to the set of search criteria to be saved. They are saved as a single record in the Macros file. The criteria are saved in the Macro Text field, while the Code allocated is the name you entered to the dialogue box with a prefix of © + the File Number, for example ©002 for the Companies More Choices.
An example might therefore be ‘©002London’ for a search to find all Companies in London. Since the Macro Code is 15 characters long and the prefix is 4 characters long, you are limited to 11 characters for the name, and a warning message will tell you of any abbreviation required. Do not include the wildcard as the last character of your name. The name last used is remembered while you are in a given More Choices screen. If you use an existing name, you are asked ‘Do you want to Replace Criteria saved with the same name?’. Remember that you may re-use names if they are for different files, as the prefix will alter. The Person who created the search criteria is saved into the Macros file for reference.
When you choose 'Load Criteria ...' (<Command-5>/), Enterprise 6 will bring back the details as saved, overwriting any search criteria that had already been typed. You are shown a window asking you to 'Load Criteria named ...' Type in part or all of the name you require, not including the prefix. If more than one set of search criteria is found, you will be asked to select the desired option from a list. This list will show the name including the prefix, and the originating Person. You can of course bring up a list of all saved search criteria for a given file simply by typing “@” or its equivalent.
Note that the saved search criteria record only the values typed into the current, frontmost More Choices window. They do not record the multiple levels that can be obtained through using either the And/Or/Not Functions or the Cross-File Find options. One therefore needs to record a set of search criteria for each of these levels.
Saving the search criteria in the Macros file ensures that they are available to all users on all client machines. You do not have to have access to Macros to make use of these functions, but if you do, you can use the Macro editing features to view, delete or even edit the text of the criteria. They are saved in the Macro recording text field, with the separate field values separated by tabs. Be careful to maintain the same number of tabs in the Text when you save any editing, otherwise the results could be unpredictable. The order that the fields appear in the text field is not necessarily that of the More Choices screen, and no labels are used to show you which is which. In other words editing the search criteria should be done with care. You can also use this feature within recorded or created Macros, as the saved Macro record can be called from other Macros. You may want to build up a 'library' of saved search criteria before proceeding to record your Macros, because you can then simply record going to More Choices, pressing <Command-5>/, typing the name of the criteria and pressing <Return> - thus skipping the need to record the typing in of the criteria into the Macro itself.
Full details of Macro creation can be found in a dedicated secton of this manual.
Note that it is possible that amendments to the More Choices screens in future versions of Enterprise 6 might render your saved search criteria incompatible for certain files. If this does happen, you will be warned on updating that the search criteria records will be deleted. It is also worth noting that the saving and retrieval of File State bullets (for example in the Orders More Choices) is based upon the File States’ listing names. This means that if a File State has been inserted or deleted, the bullets will continue to be loaded properly, but if a File State’s listing name is altered, the bullets will not appear when loading criteria saved prior to the change.
User search
Selecting this option brings up the generic User search window. Many of the Enterprise 6 search facilities are repeated on this screen. Its main advantage is that it allows you to save on disk complex queries for reuse. Note, however, that where Enterprise 6 does not require you to use a wildcard (in most cases it assumes you want to carry out a wildcard search), the User search window does not make such an assumption so you must type an @ sign wherever necessary. For example, in a Enterprise 6 search window you would usually type “A” to find all Companies beginning with A, while in the User search window you must type “A@”. Note also that if you have set up an alternative wildcard in Preferences, this will not be recognised by the User search window.
Calculations…
This option is used to perform queries based not on individual record data, but on counts and summations of that data.
For example, one could find all Companies whose outstanding Sales Ledger balance is in the range £5-10,000. To do this, go to ‘View Companies’ and click [More Choices]. Choose the ‘Sales Ledger’ option on the Find menu and click on the Total Due ‘Not Zero’ check box. Then, go to ‘Calculations…’ on the Functions menu to bring up the window shown below. Click on ‘Total Due’ in the list of ‘Available fields’ and type in “5,000” and “10,000” in the ‘Sum of Values’ From and To boxes respectively as shown in the illustration. Click [OK] and then click [Find] to close the Sales Ledger More Choices window and finally click [Find] in the Companies More Choices window.
Another example might be to find all Confirmed Orders with more than two items, where the average Sales Amount of the items is £1000 or more. From the Sales Orders More Choices window, select ‘Order items’ from the Find menu. Then select ‘Calculations…’ from the Functions menu and specify Count From as “3” (since a >= comparison is performed); click on ‘Sales Amount’ in the list of fields; specify “1,000” as the From of the ‘Average of Values’; press [OK]; press [Find] to close the items More Choices window; then click on the Confirmed check box and finally press [Find].
Further examples where this function is useful include: Contacts with whom you’ve had a certain number of sales appointments (in the Diary) in the last month; Companies with more than a certain number of outstanding Service Calls; Quotations (in the Orders file) that have seen one or less related Diary Action chasing them up during the last month; and Products which have been ordered a certain number of times in a given Period (Products to Order items, to Orders for the Period, then back to the Order items for the Calculations).
The other two From and To options allow you to find for example Companies whose highest Invoice amount is £4000 or more, or whose lowest is less than £1000. When the Count, Sum, Average Highest and Lowest options are combined in the Calculations window, it is always an AND combination.
The Top/Bottom options to the right of the From and To boxes permit you to find a specified number of records that you want to find - for example either the top or bottom ten that meet the other criteria specified. Examples of its use include: finding the top five Companies in terms of Total Invoiced Amount; finding the ten Orders that contain the largest numbers of items; finding the bottom five Products in terms of Average Order item Quantity. Note that when finding the least of whatever it is you’re adding up, items with a count or summation of zero will not be listed. Only one of the Count, Sum and Average boxes should be filled in: you can’t, for example, find a Top 5 in both Counting and Summation terms. If you do fill in more than one, only the first (working downwards on screen) will be taken into account.
The Calculations… option should only be used when you are looking at the More Choices window of a file other than the one you first went to - in other words only during cross-file Finds. In addition, it only works when using ‘One to Many’ relationships, such as from Companies to Orders or from Orders to Order items - but not vice versa. This is because it would make no sense to count or add up the values in a file if there can only ever be one.
There are also currently a few exceptions to its general use when linking via a subfile (eg Companies to Contacts and to Products) or via an indirect link (eg Companies to Order items). If the Calculations… function cannot be used a message will appear telling you as much. While these queries are comprehensive and flexible, the other side is that they can be slow. The speed will depend largely on the number of items in the many file, but also on the ratio of One to Many records (the higher the number of One’s found, the slower).
Due to the fact that these queries require the generation of complicated statistics, and that these statistics are generally quite difficult to acquire from Enterprise 6’ Report Generator, a feature has been added at the base of the Calculations window: ‘Create a Results File ...’. If this is switched on, at the point that you press the [Find] button on the More Choices window to which the Calculations belong you’ll be asked to name a file on disk in which to save the results. If you also switch on the optional ‘Including only matching results’ check box, the export file will contain the statistics for each One file record matching your requirements (in the first example Sales Invoices in the required range), whereas if unchecked statistics for all the One file records at all represented in the Many file selection will be displayed (ie the Sales Invoices listed will not necessarily be within the required range). If you are using the Top/Bottom calculations, then a list of the correct number of Top or Bottom records will appear underneath the other figures, sorted appropriately. The file on disk is a tab-delimited text file, so can be opened in any WP or Spreadsheet application.
This is a very useful tool for calculating statistics in places where they are not otherwise currently accessible. For example: one could find (and create a results file for) the Top 10 geographic Areas used in the Companies file; or all Personnel whose Orders this month total to more than £1000. Minor files such as these don’t usually have More Choices windows, so the function appears on a menu on the minor file listing screen. At the moment there is just one example of this, in the Personnel file. This allows you to Find Personnel by Companies, Contacts, Orders and Order items, including double options where for example the Companies file has both a Sales and a Taken By Person reference.
There is one very important difference between the Quick search dialogue and the Comprehensive search.
When you searched for all the companies beginning with a, you merely typed in “a”, and Enterprise 6 assumed you meant ‘beginning with a’. You could have typed “a@”, and achieved the same result.
In the Comprehensive search, the @ (wildcard) is also assumed by Enterprise 6, but there is an option to switch off the @, to create a more defined and controlled search. You may, for example, wish to find all the companies called a certain name, not just those starting with those letters. For example, in the Quick search dialogue, type “London Pride”. Note that queries are not case sensitive, so Enterprise 6 will find the information whether or not you capitalise: Enterprise 6 will find all the Companies whose names start with London Pride. In the Comprehensive search dialogue, type “London Pride” in the Company Name box. The result is identical.
Now bring up the Comprehensive search dialogue box again, but this time check the ‘No auto @’ check box on and type “London Pride” in the Company Name box. Enterprise 6 will find only one company - London Pride - not London Pride Insurance or London Pride Ventures. In other words, Enterprise 6 has found an exact match to the text you typed, rather than all Companies beginning with that text. When ‘no auto @’ is set on in the Selection Criteria Dialogue box, you need to type “London Pride@” to find all the companies whose names begin with London Pride.
Enterprise 6 also allows you to search for any word that forms part of an entry. This is a slower search than the ‘starts with’ function, because Enterprise 6 has to search all entries sequentially rather than using the index file. To search for all companies with the word London in their name, select ‘View Companies’ from the Companies menu, and in the company name box type “@London@”.
All More Choices windows also feature the ability to perform @ sign (or non-@ sign) look-ups where appropriate. For example, in the Companies More Choices window you can obtain selection lists for Status, Types, Sources, Areas and Personnel. These fields can be treated in exactly the same way as their counterparts on the input screens. For example, typing “@” followed by <Tab> in the Status field on the Companies More Choices window (and on the ‘Enter Companies’ screen) will bring up a selection list of Status records. Typing “N” will bring up a selection list of Status records beginning with N.
This also applies to the Code boxes that refer to the Major files. For example, you can get up a selection list for Companies or Contacts when in the Diary More Choices. Note that the entry screens’ feature of defaulting the Contact Code according to the Company (and vice versa) does not occur in the More Choices windows. Note also that no selection list will appear for records of the file to which the More Choices window belongs, so, for example, typing “@” in the Company Code field in the Companies More Choices will have no effect. A check box ‘No @ lists’ on the right of the More Choices windows determines whether or not you actually want to see these lists. This is necessary because if, for example, you want to find all Diary items with Action Codes that begin with ‘M’, you don’t want to be presented with a selection list that forces the selection of just one specific code such as ‘MLET’.
The ‘No @ lists’ check box always starts off as unchecked, but ‘remembers’ your last setting each time you go to a More Choices window. NOTE: Ensure you press <Tab> after typing “@”. Remember that <Return> key accepts the More Choices window and causes Enterprise 6 to perform the search.
