Queries let you work with or print a subset of the data on an editing screen. You can use queries to find incomplete specifications (such as where the catalog cost is "0") or to report on just certain areas or rooms.

Note that when printing reports, there are some "quick filters" available to print only the items associated with the current record you were on when you clicked on the Print button (for instance, the current object or current room). These filters vary depending on which form you are printing from, but often produce the results you want without need for a query.

Simple Queries

To create a query, go to the editing form where you want to create a query and, under "Commands," click or select the Query button to display the Build Query tool for that form. Just like all other commands in FF&EZ, Query operates in a specific way for the screen on which you are currently located, showing only the fields that make sense for that screen.

Most queries are fairly simple and you will probably only use the upper half of the tool ("Query Group 1" in the illustration) to create them.

The basic procedure for creating a query works like this (refer to the numbers shown above):

1. Select whether the records must meet all of the conditions you create (fewer will match) or any of them (more will match), by checking the appropriate option next to "Search for items that meet".
2. Then, using the upper "New condition" section, select a field from those in the first pick list. The available fields come from the table you are editing as well as from related ones (this also means that you need to make sure that the field you pick is from the table you want). 
   
   You can instead use the "Add current" option to pick a common field and quickly create a condition based on its current value. When you click on the separate Add button next to it, the next three steps are done for you (see next section).
3. Then, in the second pick list, select a comparison "operator" such as "starts with" or "is greater than or equal to." Numeric comparisons are fairly straightforward, however using character comparisons can be a little tricky—the operators that are easiest to use are "starts with," "contains" or "does not contain." Some fields will limit the comparisons available (because the others will not make sense for that field).
4. Finally, supply a value to which the field will be compared. If the field you selected is a logical field (yes/no, true/false), this field isn't needed because the comparison operator will already offer the appropriate choice. Note that character values are not case-sensitive, so your value can be in upper or lower case.
5. Click on the Add button (or press [Enter] while on the value field). The new condition or "clause" will appear in the condition box above. 

To add more conditions, just repeat steps 2 through 5 as needed. Remember that if you selected the "all" option (step 1), the records must meet all criteria you define and each additional condition makes the query more exclusive. If you selected "any," then each condition makes the query more inclusive. When you have added all the conditions you need, click the Run Query button to display the results

If matching records are found, you will return to the form from which you called Query and they will appear in the List Box. If no matches are found, a warning message will appear instead and you will remain on the Query Form. 

Note: When you build a query on the Worksheet using fields from either the spec or the vendor, the system will return all objects with at least one match in any of the objects' components, even if another of those objects' components does not match. This is because the system is designed to assume that the complete object should be included, not partial ones since that might cause costs or prices to be under-reported or not all products to be ordered.

Queries based on the current value of a field

If you want to create a query to show all records that match the one that you have currently highlighted, you can use the "Add current" option. When you select one of the common fields listed in this control, the system will look up the current value of that field based on the "current" data record. When you click the Add command to the right of the dropdown list, a new condition will be created automatically using the "is equal to" comparison. 

Editing Queries

The editing form will "remember" the last used query for a screen as long as you do not close the editing form, even if the query is not active. You can change an existing query by changing or deleting individual clauses or by adding new ones. To change or delete a clause, highlight it by clicking on it, then select either the Edit or Delete button to the right. If you select Edit, the clause will be copied to the condition definition boxes below so that you can make changes (double-clicking the clause will also edit it). You can also create a new clause by selecting the Copy button. This copies the highlighted clause into the "New condition" boxes, where you can change one of the values.

Complex Queries

Although simple queries will probably be sufficient for 90% of your query situations, you may run into situations where something more complex is needed. For instance, what if you wanted to look at all chairs costing less than $250 from several different manufacturers?

This type of query is called "complex" when it requires that you use both "exclusive" conditions (the items must meet both the type and cost conditions) and "inclusive" conditions (they can be from more than one vendor). Fortunately, the plain English options that accompany the two query list boxes in FF&EZ make this easy: You put one group in the first box and the other group in the second, then tell the program how you want to relate them.

In the example shown, notice that the upper condition box is using the "all" option (1) to look for object tags that start with "CHR" and have a cost at or less than $250 (that is, they meet all the conditions in that box), while the lower condition box is using the "any" option (3) to allow the specifications to be from any of the vendors shown. The "and" option (2) just above the second condition box makes sure that the selected records meet the "chair" and "less than $250" conditions and meet any of the three vendor conditions. Another way of saying this is: 

(Tag starts with "CHR" and Cost <= $250) and (Vendor = "SHELBY" or Vendor = "BROYHILL" or Vendor = "AMERHOTEL"). 

Note that everything within the parentheses would be evaluated as a group.

Deactivating and Reactivating Queries

When a query is active, only records meeting the query will be displayed on the editing form. If you use the Print button to print any reports, the reports will only show the records in the query (unless you override this in the report options). To remind you when a query is in effect, a "Query Active" note will appear in the upper right hand corner of the editing form. It will blink each time you move to a screen with an active query. 

Next to it is the Show All button, which deactivates the query and displays all records. As long as you do not close the editing form, the current query remains available. All you have to do is use the Find/Query button and run the query again.

Alternatives to Complex Queries

If you have a group of items to print that would require a fairly complex query, one alternative is to use a code of your own choosing in an available screen field to create a temporary group that can be identified with a simple query.

The most common method of doing this is to use one of the "Code" fields on the Area, Room, Object or Specification screens (if they are not being used already). For instance, if you wanted to print a group of rooms, you could those rooms with a simple letter (like "A") in the Room Code field. Then you can use a simple query with a single condition:

       Code (Room) = "A"

You can also use the room's "Phase" code for this purpose, although the word "phase" implies a specific meaning. Use codes in area or room fields if you are trying to print a group of areas or rooms. Use codes in object or spec fields if you are trying to print a group of objects or specs. Note: It does not really matter which fields you use, as long as they are not being used for other data that might confuse the query results.