Search command, tips and tricks in order to go further

The Search  command offers extensive possibilities for searching for information in your database: it offers numerous fields for each file, various comparison operators and Boolean operators for combining search criteria.

This article gives you some additional information to help you get the most out of this command.

Search in linked files

When you call the Action>Search command, the search formula editor spontaneously displays the fields in the current file. However, you can also find records in the current file on the basis of records in a related file. Here are a few examples:

• In Business, find addresses based on linked invoices with certain characteristics, such as an invoice total above or below a certain amount.
• In Finance, find accounts based on linked entries with certain characteristics, such as the VAT code used or the date of the entry.
• In Staff, find employees on the basis of linked payslips with certain characteristics, such as withholding tax rate or accounting date.
• In BiblioMaker, find loans based on linked address files with certain characteristics, such as the class to which a pupil belongs or the type of reader (young/adult).

You select the related file's field by selecting the related file from the pop-up menu above the fields area, which displays the related file's fields in the area.

In most cases, an alternative would be to search the linked files for the desired files, then call the Action>Linked records command, but this operation would require more steps to achieve the desired result.

Search mode

By default, the search result creates a selection of records corresponding to the defined search formula. However, it is possible to take into account the current selection of records and link several searches together, combining the results to refine your selection.

In the Mode pop-up menu, select the wanted value :

• Normal : the program searches for records according to the defined search formula, regardless of the current selection of records: the records found create a new selection of records.
• Within selection : the program searches for records according to the defined search formula, but limits its search to the current selection of records. This mode therefore refines a record selection already made.
• Add to selection : the program searches for records in the entire file according to the defined search formula. The records found are added to the current selection of records. In this way, you accumulate the results of two successive searches.
• Take out of selection : the program searches for records according to the defined search formula and removes them from the current selection of records.

When you select a mode other than Normal, a padlock icon appears to the left of the menu. Clicking on the padlock will "lock" the selected mode for the next search.

Save a search formula

A search formula can be saved for later reuse, which can be useful for frequently used searches.

Once the search formula has been defined, use the Save button at the bottom of the formula editor to give the search formula a name. If you don't want other users to have access to this search formula, check the Private access box.

The saved search formula can be loaded from the search formula editor via the Load button.

The same search formula will also be available in the favorites bar  of the main window. Simply click on the name of the formula to execute it and find the desired records.

Reusing a search formula may require you to adapt the formula over time. For example, if you use a Date field in your formula, you may wish to adapt the date. Rather than making this adaptation each time the formula is used, you can make use of symbolic dates.

Symbolic dates

When composing the search formula, if you choose a Date field, the program displays a series of symbolic dates such as Today, Beginning of current month, End of previous year, etc. Using one of these symbolic dates, the formula will always be current, whatever its execution date.

Open formulas

In some cases, when creating a search formula, some of the formula's parameters may be unknown to you or subject to change. Rather than "freeze" the formula with parameters that you'll have to modify each time you run it, you can leave them open by using the "?" sign. When the search is executed, the software will display a dialog asking you to complete the formula with the missing parameter.

Example: in Business, you're looking for the addresses of customers who have generated sales of a certain amount. By entering the search formula Turnover current year is superior or equal to ?, the program will ask you to enter the desired amount when executing the formula, making it more flexible than using a fixed amount.