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 <=
lc	String	Unary lower case
uc	String	Unary UPPER CASE
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
!	Boolean	Unary NOT
AND	Boolean	AND
OR	Boolean	OR
()	any	Bracketed subexpression

Dates for the date operators (IS_DATE, EARLIER_THAN etc) must be dates in the format expected by Time::ParseDate (like the ActionTrackerPlugin). WITHIN_DAYS works out the number of working days assuming a 5 day week (i.e. excluding Saturday and Sunday). Apologies in advance if your weekend is offset ± a day! Integers will automatically be converted to dates, by assuming they represent a number of seconds since midnight GMT on 1st January 1970. You can also use the d2n operator to convert a date string to such an integer.

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%