FORMQUERY

%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
uc String Unary UPPER CASE
d2n Number Convert a date string into epoch seconds
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%
Topic revision: r1 - 17 Nov 2009, CrawfordCurrie - This page was cached on 18 Oct 2018 - 04:04.

The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. See Copyright Statement. Creative Commons License    Legal Imprint    Privacy Policy