Viewer > Mass Update & QBE

You can build in this screen either the QBE queries or the Mass Update ones

dmc_viewer_qbe_filled

Depending on which (Mass Update or QBE) is used the EXPRESSION field will open or not.

To check if your EXPRESSION or CONDITION is good and valid simply use the "TAB" key OR use the button on the right of the entry field and an icon on the right will show the result

General Technique to build your queries :

Both these features work from the current Table's COLUMNS - OPERATORS and FUNCTIONS

COLUMNS (expression) : are the current Table's fields

OPERATORS : such as = , > , < , <> , + , - , % , / , AND , OR etc ....

FUNCTIONS : such as LEFT(Expression,Length) - RIGHT(Expression,Length) - INSTRING(Text,Expression,1,1) - CLIP(Expression) - CLOCK() - TODAY() - SUB(Expression,Start Pos,Length) - UPPER(Expression) etc ....

For all DATE and TIME columns you have specific functions which MUST be used as the values displayed 01/12/2008 are NOT the ones stored in files (when stored as a LONG)

DEFORMAT('Place/Date/Here',@d017) must be used for any DATE columns

DEFORMAT('PlaceTime:Here',@t07) MUST be used for any TIME columns

From version 1.6.0.3 when you double-click on any function "Expression" will be replaced automatically by the column NAME you are working on

and in QBE you simply need to select the COLUMN then double-click on the function to pre fill it also with that column NAME

This means that in 4 mouse clicks you can fill both Expression and Condition (single functions) :

1. Select Expression entry field

2. Double-Click on the function to use

3. Select Condition entry field

4. Double-Click on the function to use

NOTE : to use any Expression on ALL records, you simply double-click on the FIRST function ie: (Expression) <> ''

In both QBE and Mass Update you can fill by hand the query expression or condition OR use all supplied List Boxes to build it by double clicking on any record :

For example : if you want to build an expression for a column STATE containing 'AL' and the column InvoiceDate after the 1st Jan. '08 and before the 30th March '08 you simply use this technique :

Start by selecting the 'STATE' column in the main List and click on the right hand side icon to open the query builder.

Select and double-click on "INSTRING" from the FUNCTIONS list, this will place in the selected field (expression or condition) :

INSTRING(Text,STATE,1,1)

Select with the mouse "Text" and replace it with 'AL' in our example

Now the field will show as

INSTRING('AL',STATE,1,1)

(you can also type this directly in the Expression text field)

Double-click on "AND" to add it at the end of your query

Double-Click on the COLUMN "InvoiceDate" to add it at then end of your query

Double-Click on ">" to add it at then end of the query

Enter by hand whatever value in quotes like this '01/01/2008' (depending on the format of that column)

Double-Click on "AND" in the OPERATORS to again add it at the end of the query

...... carry on till your query is built

ANY double-Click will automatically place the content of the list in the selected entry field - adding a SPACE before

Mass Update Queries :

In Mass Update when you are working on a column, it's NAME will be displayed for your visual help like in this screen :

When you have defined such a COLUMN query you can save it for further use BUT you have to save ONLY when ALL columns expressions and eventual conditions are defined

You can click on "Cancel" to revert o the previous screen.

You can click on "Apply" to add these to the Mass Update and you will then be shown this screen :

As you can see in the column, your expression and condition have been added and evaluated : it this case the return a SUCCESS showing with the Green Icon AND the AFTER column value has changed to what you will get.

Carry on building your Mass Update queries and conditions till you are satisfied with them

QBE Query :

In QBE's there is no expression field and only the QBE field is open for input

Build the query like described here

Again using the TAB key will tell you if the QBE is valide or not with the appropriate icon

You can Save this QBE like this

You click on "Apply" to process this query and obtain the result set (or on "Cancel" to revert to previous screen)

Pervasive Online Order #30580 ReceiptYou have a 'DELETE' button which will delete ALL the records of the query subset - you may need to filter a Table on some values to bulk delete records like this

You also have a Save button which will let you save to file ONLY the subset resulting of your Query - ALL other records will be deleted !

The query is displayed onscreen for your information

You can use the Reinitialize button to reset all data to original sate

You can then apply another stored QBE or create a new one and Apply it .....

When you click on the button 3 (apply) you will be prompted to save it for further use

You can EXPORT this subset to CSV - XML or any of the SendTo possibilities also at this stage without tampering with your Table

List of all OPERATORS :

Operators.Func = '='

Operators.Func = '+'

Operators.Func = '-'

Operators.Func = '*'

Operators.Func = '/'

Operators.Func = '%'

Operators.Func = '&'

Operators.Func = '>'

Operators.Func = '<'

Operators.Func = '<>'

Operators.Func = 'AND'

Operators.Func = 'OR'

Operators.Func = '=>'

Operators.Func = '<='

List of all FUNCTIONS :

Functions.Func = 'Upper( Expression )'

Functions.Func = 'Lower( Expression )'

Functions.Func = 'Left( Expression )'

Functions.Func = 'Center( Expression )'

Functions.Func = 'Clip( Expression )'

Functions.Func = 'Right( Expression )'

Functions.Func = 'Format( Expression , Picture )'

Functions.Func = 'Clock()'

Functions.Func = 'Today()'

Functions.Func = 'Int( Expression )'

Functions.Func = 'Sub( Expression , StartPosition , Lenght)'

Functions.Func = 'Instring( Text , Expression ,1,1)'

Functions.Func = 'Left( Expression , Lenght)'

Functions.Func = 'Right( Expression , Lenght)'

Functions.Func = 'Format( Expression , @d6)'

Functions.Func = 'DeFormat( Expression , Picture )'

Functions.Func = 'DeFormat( <39>Place/Date/Here<39> , @d017 )'

Functions.Func = 'DeFormat( <39>PlaceTime:Here<39> , @t07 )'

Functions.Func = '(Expression) = <39><39>'

Functions.Func = '(Expression) <> <39><39>'

Functions.Func = 'Len(Expression)'

Functions.Func = 'Age(Birthdate[,BaseDate])'

Functions.Func = 'All(Expression,Length)'

Functions.Func = 'Choose(Expression,Value,Value)'

Functions.Func = 'Day(Date)'

Functions.Func = 'Month(Date)'

Functions.Func = 'Year(Date)'

Functions.Func = 'Inlist(Expression,liststring,liststring)'

Functions.Func = 'InRange(Expression,low,high)'

Functions.Func = 'Random(Low,High)'

Functions.Func = 'Round(Expression,Order)'

Functions.Func = 'Capitalize(Expression)' - NOTE : this function cannot be used in a CONDITION