%FORMQUERY
is the basic FormQueryPlugin query mechanism. A
%FORMQUERY
works either on the whole database or, if the
query
parameter is given, the results of another query.
Parameter |
Description |
name |
Required, gives the name of the query result for use in %SHOWQUERY or another %FORMQUERY |
search |
Required, the search to perform (see Search operators). Default parameter; the keyword search may be omitted. |
casesensitive |
Optional, if missing or off search is not casesensitive. |
query |
Optional, the name of the query to refine. If missing, defaults to the whole database |
extract |
Optional, the name of a field in each of the matched maps to flatten out into the returned list. |
moan |
Optional, "on" or "off", if set to "off" will disable match failed message |
For example,
%FORMQUERY{name="AQuery" search="Owner='Main\.Fred'"}%
%FORMQUERY{name="BQuery" query="AQuery" search="Product='Boiled Egg'"}%
%FORMQUERY{name="CQuery" query="BQuery" search="" extract="CookingTimes"}%
%FORMQUERY{name="DQuery" query="CQuery" search="Time < '4'"}%
will search for all topics with a form field
Owner
set to
Main.Fred
, then filter that down to those topics that have
Product
set to
Boiled Egg
. Then it will extract and flatten out the embedded table
CookingTimes
in each matched topic. By "flatten out" we mean that future queries on
CQuery
must refer to the fields of the
CookingTimes
table, not the fields of the topic, i.e.
CQuery
will be an array of all the rows in the embedded table. Finally it will filter down to those rows that have the column
Time
< 4 minutes.
Of course there is more than one way to skin a cat. A faster way to formulate the
same query would be to say:
%FORMQUERY{name=AQuery search="Owner='Main\.Fred' AND Product='Boiled Egg'" extract=CookingTimes}%
%FORMQUERY{name=DQuery query=AQuery search="Time < '4'"}%
FORMQUERY is normally silent i.e. it generates no output unless there is an error, in which case an error description is inserted. This error message can be disabled using the
moan
parameter which makes
FORMQUERY
totally silent.
Search operators
Fields are given by name, and values by strings or numbers. Strings should always be surrounded by 'single-quotes'. Strings which are regular expressions (RHS of =, != =~ operators) use 'perl' regular expression syntax (google for
perlre
for help). Numbers can be signed integers or decimals. Single quotes in values may be escaped using backslash (\).
The following operators are available:
Operator |
Result |
Meaning |
= |
Boolean |
LHS exactly matches the regular expression on the RHS. The expression must match the whole string. |
!= |
Boolean |
Inverse of = |
=~ |
Boolean |
LHS contains RHS i.e. the RHS is found somewhere in the field value. |
< |
Boolean |
Numeric < |
> |
Boolean |
Numeric > |
>= |
Boolean |
Numeric >= |
<= |
Boolean |
Numeric <= |
@ |
Node |
Access node referenced by LHS. e.g. ProjectLeader@TopicTitle returns the formfield TopicTitle of the topic stored in the formfield ProjectLeader |
lc |
String |
Unary lower case |
displayValue |
String |
return display value of a formfield, e.g. displayValue('State') |
uc |
String |
Unary UPPER CASE |
d2n |
Number |
Convert a date string into epoch seconds |
n2d |
String |
Convert epoch seconds into a date string |
length |
Number |
Length of an array, e.g. length(attachments) to return the number of attachments |
! |
Boolean |
Unary NOT |
AND |
Boolean |
AND |
OR |
Boolean |
OR |
ALLOWS |
Boolean |
LHS is a topic that allows to perform RHS by the current user e.g. topic ALLOWS VIEW is true when the current user is allowed to view the given topic |
() |
any |
Bracketed subexpression |
IS_DATE |
Boolean |
Compare two dates e.g. '1 Apr 2003' IS_DATE '1 Apr 2004' |
EARLIER_THAN |
Boolean |
Date is earlier than the given date |
EARLIER_THAN_OR_ON |
Boolean |
Date is earlier than, or on, the given date |
LATER_THAN |
Boolean |
LHS is later than the given date |
LATER_THAN_OR_ON |
Boolean |
LHS is later than the given date |
WITHIN_DAYS |
Boolean |
Date (which must be in the future) is within n working days of todays date |
Search operators work on the fields of each map, be it a topic or an embedded table row.
Fields can be simple field names or can be more complex, and may even contain embedded searches. See
Fields below for more information.
A search defined as some text
string without any search operators is interpreted as
text=~'string'
.
At present there is no way to constrain a search on the contents of a contained table, such as an embedded table or forward relation. However there are usually ways around this; for example, a
%FORMQUERY
that uses
extract
to flatten all the sub-tables, and then use of the parent relation to complete the search. For example:
%FORMQUERY{name=AQuery search="Product='Boiled Egg'" extract=CookingTimes}%
%FORMQUERY{name=DQuery query=AQuery search="Time < '4' AND CookingTimes_of.Owner='Main\.Fred'"}%
Fields
Field references can be as simple as the name of a field, or may be more complex expressions that can even include embedded searches. The precise interpretation of the syntax depends on the type (Map or Array) of the object being referenced.
Arrays
-
N
where N
is a number will get the Nth entry in the array e.g. attachments.9
-
X
will return the sum of the subfield X
of each entry e.g. TaskTable.Effort
will sum the Effort
column in a table called TaskTable
.
-
[?search]
will perform the given search over the entries in the array. Always returns an array result, even when there is only one result. For example: attachments[?name='pitcha.gif']
will return an array of all the entries that have their subfield name
set to pitcha.gif
.
-
[*X]
will get a new array made from field X of each entry in this array. For example attachments[*size]
will get an array of the sizes of each attachment.
Maps
-
X
will get the subfield named X
. For example, Product
will get the formfield named Product
.
-
X.Y
will get the subfield Y
of the subfield named X
.
In all cases
-
#
means "reset to root". So #.Y
will return the subfield Y
of the Map at the root of the query.
See also:
FormQueryPlugin,
%SUMFIELD%,
%FORMQUERY%,
%QUERYTOCALC%,
%TABLEFORMAT%,
%SHOWCALC%,
%MATCHCOUNT%,
%SHOWQUERY%