Welcome!

PowerBuilder Authors: Chris Pollach, Yakov Fain, RealWire News Distribution, Al Soucy, Elizabeth White

Related Topics: PowerBuilder

PowerBuilder: Article

Advanced DataWindow Filtering with PowerFilter

A robust and proven data filtering user experience

As we all know, the DataWindow is a very powerful and flexible control used for data presentation and manipulation. For the most part, the features that you get out of the box allow you to create some very impressive applications. With that said, there is always room for improvement. Fortunately for us, the DataWindow was built with flexibility in mind. By taking advantage of this flexibility, we can extend the DataWindow to do almost anything we want.

In this article, I'm going to introduce you to PowerFilter, a control developed by Jim Reese and available for purchase from www.PowerToTheBuilder.com. PowerFilter is an easy-to-implement DataWindow filtering component that gives your application auto-filtering functionality similar to that found in Excel 2007\2010. It works with your existing DataWindows and requires as little as three lines of PowerScript to implement (see Figure 1).

Figure 1

PowerFilter contains all of the following features:

  • Multilingual support built in, no coding required. Single source code supports multiple languages at runtime.
  • Dropdown checklist with unique values for each column. Check as many items as desired in as many columns as desired to define the filter.
  • Custom Filter with pre-defined options based on data type (Last Month, Next Month, Last Year, This Year, Greater than, Contains, Above Average, etc.).
  • Supports Grid and Tabular DataWindows.
  • Supports custom column formats including format expressions.
  • Dropdown DataWindows, ListBoxes, and Code Tables display, filter, and sort on the display value, not the data value.
  • DisplayAsPicture support. Dropdown and custom filter dialogs automatically display images to select/filter if the column displays images using the DisplayAsPicture property.
  • Appeon compatibility.
  • Quick sort by column built into control, with ability to disable that feature.
  • Instance variable options definable in the painter to modify appearance or behavior.
  • Component can identify the columns and use default header text, or the developer can specify which columns to allow the user to filter and redefine header text via function calls.
  • Enhanced features in PB 11.5+ version:

o Datepicker for custom date or datetime filters.

o Treeview presentation in Dropdown, allowing selection of entire years and/or months for date and datetime columns.

o PowerTips (ToolTips) for each column describing the filter applied.

o Transparent/Gradient option highlighting, just like Excel 2007.

Implementing PowerFilter
There are two ways to implement PowerFilter: with a visual implementation and a non-visual implementation. The visual implementation comes in the form of a PowerFilter check box that you place on your window or userobject. The non-visual implementation uses a PowerFilter NVO that you use behind your own visual implementation.

To start, all you have to do is add the PowerFilter.pbl to your application's library list.

Visual Implementation
Place a copy of u_PowerFilter_checkbox on the window with the DataWindow control you wish to filter. The default name provided by PowerBuilder is cbx_1 if no other checkbox controls are on the window.

Add the following to the DataWindow constructor event:

cbx_1.of_SetDW(this)

or add  cbx_1.of_SetDW( yourdwcontrolname ) elsewhere in your application code.

Non-Visual Implementation
Declare an instance variable of type n_cst_PowerFilter:

n_cst_PowerFilter iu_PowerFilter

In your DataWindow control's constructor event, or other appropriate place, instantiate PowerFilter and identify the DataWindow to filter:

iu_PowerFilter = create n_cst_PowerFilter
iu_PowerFilter.of_setdw(this)

To toggle the filter buttons on and off, refer to this sample code from a user event on the window, called from the menu:

iu_PowerFilter.checked = NOT iu_PowerFilter.checked //turn filter on or off depending on current state.
iu_PowerFilter.event ue_clicked()

Required DataWindow Code
Add the following to the DataWindow resize event:

  • Visual: cbx_1.event ue_positionbuttons() OR
  • Non-visual: iu_PowerFilter.event ue_positionbuttons()

Create a DataWindow user event named ue_leftbuttonup, mapped to event ID pbm_dwnlbuttonup, and add the relevant line of code below to that event. If you've already mapped that event ID to your own event, add the line of code to that event:

  • Visual: cbx_1.event post ue_buttonclicked ( dwo.type,dwo.name ) OR
  • Non-Visual: iu_PowerFilter.event ue_buttonclicked ( dwo.type,dwo.name )

For PB versions below 9, use the following syntax:

  • Visual: cbx_1.event post ue_buttonclicked( dwo.name ) OR
  • Non-Visual: iu_PowerFilter.event post ue_buttonclicked( dwo.name )

Distribution of PowerFilter with Your Application
A collection of graphic images are included with PowerFilter in the PFGraphics directory. They need to be in a directory visible to your application when compiling your .exe/.pbd or when running in production. A sample resource (.pbr) file is provided for saving the objects into your executable or .pbd file. For the 11.5 or later versions, three DataWindow objects must also be defined in your resource file if you don't create a powerfilter.pbd file. Earlier versions only require the first DataWindow to be added:

powerfilter.pbl(d_powerfilter_languages)
powerfilter.pbl(d_powerfilter_datetree)
powerfilter.pbl(d_powerfilter_datetimetree)

Other PowerFilter Functions
There are many functions you can use to change PowerFilter's behavior:

U_PowerFilter_checkbox.of_SetDW( Datawindow a_dw )
Identifies the DataWindow to be filtered. This can be called in the DataWindow's constructor event, or some other point in processing. The original Filter and Sort definitions are captured in this function, so you may want to call this function from another location after the dataobject has been defined and any initial sort/filter has been established.

U_PowerFilter_checkbox.of_SetOriginalFilter()
Captures and saves the current Filter defined for the DataWindow. Called in of_SetDW(), but can be called later by the developer if the dataobject is not yet established when of_SetDW() is called, or the filter definition changes after of_SetDW() is called, but before PowerFilter is invoked.

U_PowerFilter_checkbox.of_SetOriginalSort()
Captures and saves the current Sort defined for the DataWindow. Called in of_SetDW(), but can be called later by the developer if the dataobject is not yet established when of_SetDW() is called, or the sort definition changes after of_SetDW() is called, but before PowerFilter is invoked.

U_PowerFilter_checkbox.of_SetColumns( string as_Columns[] )
Pass in an array of column names to filter on. This is an optional function, useful if the header objects are not using the standard naming convention of "columnname_t", or to limit filtering to particular columns only.

U_PowerFilter_checkbox.of_SetTitles( string as_Titles[] )
Pass in an array of column titles. This is an optional function that allows you to override the default behavior of using the text found in the columnname_t object in the header band normally identified as the column title. If used, the number of elements in the array must equal the number of columns identified by the default logic, or the number of columns identified in of_SetColumns().

U_PowerFilter_checkbox.of_GetDropDownOffset ( ref integer ai_Xoffset, ref integer ai_Yoffset )
Function to read the horizontal and/or vertical offset position from the default calculated position for the dropdown. Generally, this function does n't need to be used, but if the default placement of the dropdown is not correct or at the desired location, this function reads the offset instance variables set at design time or via of_SetDropDownOffset. This function is called from u_PowerFilter_dropdown.of_open(), which calculates the position of the dropdown. The default dropdown position is right-aligned with the associated button, with the top of the dropdown positioned at the bottom of the DataWindow header band.

U_PowerFilter_checkbox.of_SetDropDownOffset ( integer ai_Xoffset, integer ai_Yoffset )
Function to change the horizontal and/or vertical offset position from the default calculated position for the dropdown. Generally, this function does not need to be used, but if the default placement of the dropdown is not correct or at the desired location, this function allows the developer to change the offset instance variables from their default values (0) to new values, which are included in the equations to calculate the position of the dropdown.

U_PowerFilter_checkbox.of_SetLanguage ( integer ai_languagenumber )
Sets the language for all visible text in PowerFilter. PowerFilter supports up to six different languages, designated 0 through 5. 0 is English, the default language. The remaining five languages are pre-defined but can be changed by the developer. The language phrase list is maintained in d_PowerFilter_languages, an external DataWindow that is pre-filled with the following languages: 0-English, 1-French, 2-German, 3-Italian, 4-Brazilian Portuguese, and 5-Russian (Russian is available in version 11.5 and above). Some of the translations were defined using Google's translate page (http://translate.google.com/# ), and may need refinement by someone who is fluent in the language. This function should be called before the U_PowerFilter_checkbox control is visible or checked, since it does change the text property of the control. If there is a need to support additional languages, this can be accommodated by replacing existing languages not in use, or simply adding additional columns to d_PowerFilter_languages. When updating d_PowerFilter_languages, it's imperative that the existing sortkey values are maintained for the existing rows. If future versions of PowerFilter require new phrases, they will be appended to the bottom of the list.

U_PowerFilter_checkbox.of_QuickFilter (string as_colname, any aa_item)
This function can be called to quickly set a filter on a single column to a particular value. It doesn't clear other column filters; it just defines a filter for the particular column as if the user selected the single value from the dropdown. It is up to the developer to determine the column and the value to pass in to this function.

U_PowerFilter_checkbox.of_SetParentWindow(window aw_parent)
This optional function can be called to set the parent window to open the custom visual objects on. PowerFilter will determine the parent window by default, but this function can be used to override the default behavior if desired.

Conclusion
Building Filter expressions in PowerBuilder can be tricky. For example, using the IN clause with negative numbers "item_id IN (-1,-2,-3)" returns an error unless you wrap each individual negative number in parentheses or leave spaces after the commas. Filtering strings with embedded quotes in them can be challenging (How many tildes do I need to add?) So can building compound filters on multiple columns. PowerFilter handles these and other issues automatically while providing an elegant, easily recognizable user interface.

With one PBL and a couple lines of code, you could instantly offer your users a robust and proven data filtering user experience. Evaluation versions of PowerFilter in PB 6, 8, 9, 10, 10.5, 11.1, 11.5 and 12 (classic) are available for download at PowerToTheBuilder.com.

More Stories By Brad Wery

Brad Wery is the President of Werysoft Inc. (www.werysoft.com) and the creator of www.PowerToTheBuilder.com, a site dedicated to helping PowerBuilder developers create visually appealing user interfaces. He has been a member of TeamSybase since 2006 and is an active participant in the PowerBuilder Newsgroups.

More Stories By Jim Reese

Jim Reese is a Software Engineer living in Florida. He has over 25 years of experience in the IT industry, and began his PowerBuilder career with version 4 in 1994. Jim is the author of PowerFilter, available at www.PowerToTheBuilder.com.

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.