Using filters to find information

As the size and scope of a database grows, finding specific information can become difficult. Large amounts of data become confusing and awkward to manipulate. It is critical that users be able to limit the amount of data under consideration so that they don't become overwhelmed by vast data sets. System considerations also favor smaller data sets to improve response times and stability.

Filters give users the ability to set criteria by which data sets may be reduced in size to include only relevant information. Filter settings can be made and adjusted in a natural and intuitive manner, easily changed by the user and stored locally between editing sessions.

Filter concepts and operation

The concept of filtering data is abstract and may initially be somewhat confusing. The basic idea is to take a large data set and apply a condition that must be met in order for individual data elements to be retrieved.

An example of a filtering operation might be to take all of the names in a phone book and select only those whose last name is "Smith". This will produce a data set far smaller than all of the names in the phone book. The filter could be refined by including the additional requirement that the first name start with the letter "J". This will produce an even smaller data set from which the specific phone number of John Smith might be found.

Filter screens

In the DbNet system, filter screens are based on the data entry screens. This makes available to the user the maximum number of filter criteria by which filters may be set. Almost any data field that can be set may be used as a filter. Filter settings are made, then the system will return a spreadsheet that includes those data elements meeting filter criteria.

In the example filter screen above, the settings will return a data set for the line items on sales orders for the customer Widgets of Wichita written after April 1, 2004 that are for item code 12345.

Cross table filtering

Perhaps the most common type of filtering, and the easiest to use, is cross table filtering. This may also be referred to as reference filtering. In the example above, the customer setting is a cross table filter. The settings are made in exactly the same way you would make a data entry for the field, using the selection box. This type of filtering is very simple, robust and powerful. A close relative of this type of filtering is attribute filtering, which is discussed later.

Date filtering

Another common type of filter is the date filter. This is used extensively during the course of business operations to view only current data. For example, as each month begins, sales reps may reset their filters to reflect only orders that are being written during the current month. This keeps the data set small and managable in a simple way.

Another common use of data filters is to analyze time periods of data for summary information. For example you might compare sales data from a month long period for last year versus this year. To do this, you would run the filter twice, once with last years settings, then again with this year.

It is important to note that the system will include the date of the setting in the data. In the example above, this implies that orders written on the First of April will be included in the returned data. Likewise, the before date setting will include data written on that date as well. If you wanted all of the orders written in April, 2004, the after date would be 4/1/04 and the before date would be 4/30/04.

Wildcard characters

Text and number data fields may be filtered using exact matching, or using wildcards. This type of filter is somewhat more complex than either cross table or date filtering. The wildcard characters allowed are the star *, less than <, and greater than > symbols.

The example above shows a star used to allow any characters in the order field at the beginning, requiring that the last character be a 3. This filter will return order numbers 00003, 00013, 00023, etc. You can use this technique to avoid typing in preceding zeros when looking for a specific order number

Multiple stars may be used in a field search. In the example above, any code with the string sequence 2345 will be return in the result set. This would include 12345, 22345, 23456, etc.

Filter setting persistence

Filter settings will be remembered by the system after the program is closed. This means that the next time the spreadsheet is called up, the previous filter settings will still be in effect. This behavior may at times be confusing, as you may have forgotten what the filter settings were if the program had not been recently run. You can force the filter screen to be displayed prior to the opening of the spreadsheet by clicking on the "Show filter screen first" check box on the main screen.

This check box can be found at the bottom of the right hand side of most main screen panels.

Using attributes

Attributes are a type of cross table field found on several different screens in the system. For example, the Status field of the Sales Order screen is an attribute. Attributes are a kind of reduced funcionality cross table field, in that they only have one value, which is the name. These types of fields are useful for collecting data elements into groups based on some characteristic. In the example of the Sales Order status, you can make groups of orders by assigning the status value based on their degree of completion in your business process. In the simplest case, you might have to status values, open and closed. Orders not yet shipped would be open and those having completed shipment would be closed.

The beauty of attributes is that you can arbitrarily extend their functionality by adding more values. For example, you might have an approval process that requires a manager to review the order before it may be submitted for fulfilment. In this case, you could add an Approved status for orders accepted.

Adding values to attribute sets is somewhat different than other cross table fields. You can add from the selection box of an attribute field on a particular data item by using the Add button. Alternatively, you can work with attribute values from the spreadsheet of the data by using the "Attrib" button.