SQL Expression Builder

Home

Query and Reporting

Harp1  Business Solutions

Products
F.A.Q.
Suggestions
Downloads
Purchase
Contact Us

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

See the Demo Video

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:

bulletCall 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.
bulletCall 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.
bulletAs the user clicks different controls and creates expressions, concatenate the expressions together to provide multiple selection criteria.
bulletShow the generated "Nice Expression" to the user, and apply the "Real" expression using either SQL Select or Set Filter.
bulletUse it anywhere you want users to create selection criteria, such as for reports, forms, extracts, Browse windows, etc.


 


SQL Expression Builder's Main Features

bullet

Expression Builder can be used to build expressions for FoxPro and non-FoxPro Databases.

bullet

Expressions built for non-FoxPro Databases are formatted for SQL pass-thru execution.

bullet

Both, the "Actual" expression, and a "Nice" expression is returned, which can be displayed to users.

bullet

Either an "SQL Select" or a "Set Filter" type formatted expression is returned by setting an option.

bullet

The Lookup Value(s) button can call up a master lookup table for selecting value(s).

bullet

The Lookup table can be FoxPro, non-FoxPro, or created on the fly using a specified SQL.

bullet

A Calendar Popup button is available for entering date and date/time values.

bullet

Options on the expression builder form can be hidden, such as the "Precede with", "Use Field Name" "See Expression" and "Lookup Fields".

bullet

The "Is" button toggles between "Is" and "Is Not".

bullet

The "Case Insensitive" check box toggles between "Same Case" and "Either Case".

bullet

The "Lookup Fields" button allows users to select other field names, rather than actual values.

 

bullet

Expression Builder returns the following information:

bullet

cExpression = The user-defined "Real" expression that can be used in queries (or filters if specified).

bullet

cNiceExpression = The "Nice" expression that can be displayed to users, such as "Project Is Equal to 'G/L Project'".

bullet

nIsIsNot = The selected Is/Is Not option.

bullet

cOperator = The selected Operator text.  (See the list of Operators below).

bullet

nCase = The selected Case option.

bullet

cValues = The user-specified Value(s).

bullet

cValuesSpec = The Value(s) specification (enclosed in quotes, braces, etc.)

bullet

cNiceValuesSpec = The Nice Value(s) specification (ie. "G/L Project").

bullet

cPrecedeWith = The selected Precede With option: "AND", "OR", or Blank

bullet

lFieldNameOnly= Returns True if the user pressed the "Use Field Name" button.

 

bullet

Expression Builder can be customized by passing the following parameters:

bullet

cAlias = Alias of table containing Field Name.

bullet

cFieldCaption = Optional Field Caption to display.

bullet

cFieldName = Field name to create expression for.

bullet

cFieldsLookUplist = Optional list of Fieldnames and their Captions to select from.

bullet

cLookupTableSpec = Optional master table lookup or SQL Select statement specification.

bullet

cPrecedeWith = Optional default "Precede With" option: "AND", "OR" or Blank (Default).

bullet

lBuildExprFor = Specifies to build a FoxPro SQL, a SET FILTER, or a non-FoxPro SQL expression.

bullet

lVerifyExpr = Specifies that Expression Builder should verify the expression's validity.

bullet

lShowUseFieldOpt = Set to .T. to Show the "Use Field Name" button.

bullet

lShowSeeExprOpt = Set to .T. to show the "See Expression" button.

bullet

uValues = Optional default value(s) for expression.

bullet

nIsIsNot = Optional default Is / Is Not button setting.

bullet

cOperator = Optional default Operator text. ie. "Equal to", "Like", "Contains", etc.

bullet

nCase = Optional default "Case Sensitivity" setting.

bullet

lShowPrecedeWith = Set this to .T. to show the "Precede With" (And/OR/Blank) option.

bullet

lUseFieldName = Set to .T. to use the Field Name without the Alias, in the expression.

 

 

Demo of using SQL Expression Builder

bullet

The Evaluation Download includes the above sample form.

bullet

Right-click on a field and select "Filter on this Item".

bullet

This brings up the Expression Builder.

bullet

After an expression is defined, the form re-queries the cursor bound to the grid using SQL.

bullet

The new subset of records is displayed and the current "Nice" filter expression is shown above.

bullet

There is very little code required.  Download the evaluation and run the form, ExprDemo.scx.



The Expression Builder Form

bullet

The selections across the top read like an expression (State Code Is In the list ...).

bullet

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.

bullet

The user-selected Value(s) can be entered manually, or the users can press the "Lookup value(s)" button.

bullet

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.

bullet

The Available Operators to choose from are:
bullet

"Equal to"

bullet

"Blank, Zero or Null"

bullet

"Containing the text"

bullet

"In the list"

bullet

"Less than"

bullet

"Less than or equal to"

bullet

"Greater than"

bullet

"Greater than or equal to"

bullet

"Between"

bullet

"Like"

bullet

"Logically true"

bullet

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).

bullet

The user can also select to precede the expression with "Blank", which can be used as a signal to clear the previous selection criteria.

bullet

SQL Injection type query commands are also checked for and disallowed.

 

Date Selector Popup

bullet

Users can easily specify dates for their value(s) using the Calendar Popup button.

bullet

The Calendar Popup is 100% FoxPro, so there are no ActiveX controls required.

bullet

Dates and Date/Times are formatted correctly for SQL and Filter expressions.

bullet

They are also formatted for SQL pass-thru to remote databases.

 

Lookup Value(s)

bullet

Users can easily select value(s) from "master" lookup tables by pressing the Lookup Value(s) button.

bullet

This option can be specified to point to one of your master tables.

bullet

This option can also be directed to create an SQL lookup cursor "on the fly".

bullet

Therefore, your lookup table can be anything you like, such as, a list of Active Projects, for example.

 

Implementing Expression Builder

bulletImplementing 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

horizontal rule

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

 Bookmark this page to check for new versions.

(C) Copyright 1996 - 2008 Mark Harpenau, All Rights Reserved.

 

To see UserQ and Expression Builder in action, take a look at our Vacation Rental Property Management Software: www.VacationRentPro.com

horizontal rule

Home | Products | F.A.Q. | Suggestions | Downloads | Purchase | Contact Us

Questions or problems regarding this web site should be directed to support 'at' userq.com.
Copyright 2008 Mark Harpenau. All rights reserved.
Last modified: 06/05/08.