SQL Expression Builder

SQL Expression Builder
User-defined, Ad-hoc SQL Expression Builder
for Visual FoxPro

Version 1.20
Released 11/11/2004

For VFP 7 - 9

SQL Expression Builder allows users to specify one or more selection criteria
right from the existing fields on their forms.

(See the sample form demo included with the Evaluation Download)

Some ways to use SQL Expression Builder:

Call it from a "right-click" method on your form's controls. Pass it the current default value. Use the returned expression to query a subset of records to display on the form.
Call it from a "right-click" menu when users click on columns of a grid. Pass it the column and row's current default value. Use the returned expression to set a filter on the grid's Row Source table.
As the user clicks different controls and creates expressions, concatenate the expressions together to provide multiple selection criteria.
Show the generated "Nice Expression" to the user, and apply the "Real" expression using either SQL Select or Set Filter.
Use it anywhere you want users to create selection criteria, such as for reports, forms, extracts, Browse windows, etc.

SQL Expression Builder's Main Features

Expression Builder can be used to build expressions for FoxPro and non-FoxPro Databases.
Expressions built for non-FoxPro Databases are formatted for SQL pass-thru execution.
Both, the "Actual" expression, and a "Nice" expression is returned, which can be displayed to users.
Either an "SQL Select" or a "Set Filter" type formatted expression is returned by setting an option.
The Lookup Value(s) button can call up a master lookup table for selecting value(s).
The Lookup table can be FoxPro, non-FoxPro, or created on the fly using a specified SQL.
A Calendar Popup button is available for entering date and date/time values.
Options on the expression builder form can be hidden, such as the "Precede with", "Use Field Name" "See Expression" and "Lookup Fields".
The "Is" button toggles between "Is" and "Is Not".
The "Case Insensitive" check box toggles between "Same Case" and "Either Case".
The "Lookup Fields" button allows users to select other field names, rather than actual values.

Expression Builder returns the following information:
- cExpression = The user-defined "Real" expression that can be used in queries (or filters if specified).
- cNiceExpression = The "Nice" expression that can be displayed to users, such as "Project Is Equal to 'G/L Project'".
- nIsIsNot = The selected Is/Is Not option.
- cOperator = The selected Operator text. (See the list of Operators below).
- nCase = The selected Case option.
- cValues = The user-specified Value(s).
- cValuesSpec = The Value(s) specification (enclosed in quotes, braces, etc.)
- cNiceValuesSpec = The Nice Value(s) specification (ie. "G/L Project").
- cPrecedeWith = The selected Precede With option: "AND", "OR", or Blank
- lFieldNameOnly= Returns True if the user pressed the "Use Field Name" button.
Expression Builder can be customized by passing the following parameters:
- cAlias = Alias of table containing Field Name.
- cFieldCaption = Optional Field Caption to display.
- cFieldName = Field name to create expression for.
- cFieldsLookUplist = Optional list of Fieldnames and their Captions to select from.
- cLookupTableSpec = Optional master table lookup or SQL Select statement specification.
- cPrecedeWith = Optional default "Precede With" option: "AND", "OR" or Blank (Default).
- lBuildExprFor = Specifies to build a FoxPro SQL, a SET FILTER, or a non-FoxPro SQL expression.
- lVerifyExpr = Specifies that Expression Builder should verify the expression's validity.
- lShowUseFieldOpt = Set to .T. to Show the "Use Field Name" button.
- lShowSeeExprOpt = Set to .T. to show the "See Expression" button.
- uValues = Optional default value(s) for expression.
- nIsIsNot = Optional default Is / Is Not button setting.
- cOperator = Optional default Operator text. ie. "Equal to", "Like", "Contains", etc.
- nCase = Optional default "Case Sensitivity" setting.
- lShowPrecedeWith = Set this to .T. to show the "Precede With" (And/OR/Blank) option.
- lUseFieldName = Set to .T. to use the Field Name without the Alias, in the expression.

Demo of using SQL Expression Builder

The Evaluation Download includes the above sample form.
Right-click on a field and select "Filter on this Item".
This brings up the Expression Builder.
After an expression is defined, the form re-queries the cursor bound to the grid using SQL.
The new subset of records is displayed and the current "Nice" filter expression is shown above.
There is very little code required. Download the evaluation and run the form, ExprDemo.scx.

The Expression Builder Form

The selections across the top read like an expression (State Code Is In the list ...).
Notice this form looks different from the one at the top of this page. This shows that you can optionally "hide" various user options, as needed.
The user-selected Value(s) can be entered manually, or the users can press the "Lookup value(s)" button.
The "Lookup value(s)" button is a powerful feature:
When calling Expression Builder, you can specify a "master" lookup table and field to use for selecting values from. The button lets users select single or multiple values from the source table, a master table, a codes table or even remote (non-FoxPro) tables using SQL Pass-thru. You can even specify to optionally create the lookup table on the fly, based on certain criteria, such as the user's security settings for instance.
The Available Operators to choose from are:
- "Equal to"
- "Blank, Zero or Null"
- "Containing the text"
- "In the list"
- "Less than"
- "Less than or equal to"
- "Greater than"
- "Greater than or equal to"
- "Between"
- "Like"
- "Logically true"
Users can specify how expressions are linked together by selecting to Precede the expression with AND or OR. (hidden on this form, see form at the top of this page).
The user can also select to precede the expression with "Blank", which can be used as a signal to clear the previous selection criteria.
SQL Injection type query commands are also checked for and disallowed.

Date Selector Popup

Users can easily specify dates for their value(s) using the Calendar Popup button.
The Calendar Popup is 100% FoxPro, so there are no ActiveX controls required.
Dates and Date/Times are formatted correctly for SQL and Filter expressions.
They are also formatted for SQL pass-thru to remote databases.

Lookup Value(s)

Users can easily select value(s) from "master" lookup tables by pressing the Lookup Value(s) button.
This option can be specified to point to one of your master tables.
This option can also be directed to create an SQL lookup cursor "on the fly".
Therefore, your lookup table can be anything you like, such as, a list of Active Projects, for example.

Implementing Expression Builder

Implementing Expression Builder in applications is extremely simple. Just instantiate the parameters object, set parameters, and call Expression Builder. It can be called from controls on forms, labels on report selection criteria forms, text boxes in grids, etc.

There's More...

Expression Builder is 100% FoxPro (no external binaries, libraries or ActiveX controls). It is compact, easy to use, and includes complete Documentation, and fully Commented Source code.

Price

SQL Expression Builder for VFP 7 - 9: $ 78
Purchase Now!

Includes:
Multi-developer Enterprise license with
Complete source code (for use within your organization)
Distribution Rights (in compiled format),
a 30-day money back guarantee,
and free upgrades!

Expression Builder Demo Video

Click on the Windows Media Video below to see a demo of Expression Builder.
Note: If the video does not play sound and video, you may need to update your Windows Media Player by going to: http://windowsupdate.microsoft.com

ExprBuilderDemo.wmv
(Streaming video, 9 minutes)

Evaluation Download

The following evaluation is available for download.
This is compiled under VFP version 9.
You will need the VFP9 runtime installed to execute this.
The evaluation is limited to a period of 30 days.

Download ExprBuilder09e.zip for VFP9
Includes a sample form demo
(190K)

For questions, comments or support, please email:

Support 'at' UserQ.com (Change 'at' to @)

Give it a try!
See how SEB can provide your users with the lookup ability they need.

To see UserQ and Expression Builder in action, take a look at our Vacation Rental Software for vacation rental properties at: www.VacationRentPro.com

* FoxPro, Visual FoxPro, Windows, SQL Server and Rushmore are registered trademarks of Microsoft Corporation.

Bookmark this page to check for new versions.