Whilst working with an internal project it became apparent that, much to our surprise, the existing documentation for QBF is partial at best. We've used QBF to good effect in the past so it seems a shame not to have the full glory of this useful feature exposed, so we thought we'd write a quick exposé - it seemed appropriate.
QBF is simply a method to use an existing entry form as the template for a lookup query on data contained in that table. You simply initialise the query, execute it, then browse the resultant data set using the appropriate commands. There are operators to filter the result set, sort the result set and even customise the query that is to be executed before it is run.
The QBF commands can be found on the MDI frame under QBF:
So, for example, say we wanted to use the CUSTOMERS screen to find all customers in California, we could initialise QBF then enter "=CA" in the STATE editline
and execute the query, resulting in a browse list of the 8 Customers in the Customers table who were based in California.
The operators are outlined below :-
For example, say we want to sort all Customers in states beginning with "N" by State
we'd get
Note that the OR operator can also be used to OR between different columns - by default they will be ANDed. So "=CA" in the STATE column and "[]Soft" in the NAME column would look for all Customers in CA with "Soft" in their name. By comparison "=CA" in the STATE column and ";[]Soft" in the NAME column would look for all Customers in CA or all with "Soft" in their name.
Please note that some of the features described above will only work in versions of OI above 10.0.7.
QBF is simply a method to use an existing entry form as the template for a lookup query on data contained in that table. You simply initialise the query, execute it, then browse the resultant data set using the appropriate commands. There are operators to filter the result set, sort the result set and even customise the query that is to be executed before it is run.
The QBF commands can be found on the MDI frame under QBF:
and execute the query, resulting in a browse list of the 8 Customers in the Customers table who were based in California.
The operators are outlined below :-
QBF Operators
Operator
Keystroke(s)
Usage
EQUALS
=
"=CA" - returns all rows where the column is CA.
Note that "=" is the default operator so we could also just use "CA"
Note that "=" is the default operator so we could also just use "CA"
NOT EQUALS
#
"#CA" - returns all rows where the column is not CA.
Note that this operator can be combined with other operators. For example to find rows where the column is not ending "Ltd" use "#[Ltd"
Note that this operator can be combined with other operators. For example to find rows where the column is not ending "Ltd" use "#[Ltd"
GREATER THAN
">"
"> 10000" - returns all rows where the column is greater than 10000.
GREATER THAN OR EQUAL TO
">="
">= 10000" - returns all rows where the column is greater than or equal to 10000.
LESS THAN
"<"
"< 10000" - returns all rows where the column is less than 10000.
LESS THAN OR EQUAL TO
"<="
"<= 10000" - returns all rows where the column is less than or equal to 10000.
STARTING
"]"
"]Sky" - returns all rows where the column starts with "Sky".
ENDING
"["
"[Soft" - returns all rows where the column ends with "soft".
CONTAINING
"[]"
"[]BLUE" - returns all rows where the column contains "blue".
FROM/TO
"..."
"2000...4000" - returns all rows where the column is from 2000 to 4000.
Note that there is no BETWEEN operator".
Note that there is no BETWEEN operator".
OR
";"
"GA;CA" - returns all rows where the column is either GA or CA".
Debug/Alter Query
"?"
"?" - shows the query statement that will be executed in an editable message so that you can see what is about to happen, and more importantly, modify the statement before execution.
This is especially useful when a query is not returning the expected results For example selecting "United Kingdom" from a drop down list would fail to return the correct values. Using ? allows us to see that the query has only taken the first word because the string is not quoted, so it looks for "WITH COUNTRY = "United".
This is especially useful when a query is not returning the expected results For example selecting "United Kingdom" from a drop down list would fail to return the correct values. Using ? allows us to see that the query has only taken the first word because the string is not quoted, so it looks for "WITH COUNTRY = "United".
ORDER BY
"BY"
"BY" - returns all rows sorted by the column with the "BY".
MULTI ORDER SORT
"BYn"
"BYn" - returns all rows sorted in the order of columns specified. So if "BY1", "BY2" and "BY3" are specified, the sort will proceed on column 1, then 2 then 3.
Note, this order does not have to match the order of prompts on the screen
Note, this order does not have to match the order of prompts on the screen
ORDER BY SPECIFIC COLUMN
"BY aaaaaa"
"BY STATE" - returns all rows sorted by the column specified, in this case STATE.
This is especially useful when you want to have a comparison operator against a column that you also wish to order by.
This is especially useful when you want to have a comparison operator against a column that you also wish to order by.
For example, say we want to sort all Customers in states beginning with "N" by State
we'd get
Note that the OR operator can also be used to OR between different columns - by default they will be ANDed. So "=CA" in the STATE column and "[]Soft" in the NAME column would look for all Customers in CA with "Soft" in their name. By comparison "=CA" in the STATE column and ";[]Soft" in the NAME column would look for all Customers in CA or all with "Soft" in their name.
Please note that some of the features described above will only work in versions of OI above 10.0.7.
| Making Databases Happen |
UK +44 (0) 20 8912 1010 USA +1 267 238 3814 |
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home