Basic Query Syntax

Subject [-max (rows|no)|-demo]
[Alias-List|Aggregation]
by[asc(Alias-List) desc(Alias-List)]
(Condition)

Where:

• Subject: a table, view or form name. Example f0101 or w4312f.
• -max: option for max number of records returned, or no for return all. Example -max 100 to return maximum 100 rows.
• -demo: returns table layout.
• Alias-List: a comma separated list of table’s or view’s aliases, or Form’s control id’s to list. The table prefix can be ignored if the alias is unique, like in tables for example. Example (an8, alph).
• Aggregation*: a list of aggregate functions. A query can either be a list of aliases or group of aggregate functions. Example [max(an8) min(an8)] (see Aggregate functions below).
• by: sort orders for Alias-List (see Aggregate for details). Example by[asc(alph)] sequence by alph in ascending orders.
• Condition: optional filter conditions. Example all(an8>4000) (see Conditions below).

* Note: Aggregate is not available for Forms.

Aggregate functions

The following aggregate functions are available:

• group
• sum
• min
• max
• avg
• count
• count_distinct

• avg_distinct

  In addition there are two sequence functions:

• desc
• asc

The form of an aggregate is:

Aggreate-Function(Alias-List)

For example:

group(nxtr) sum(aopn)

For every value of nxtr, sum value of aopn.

Note: For business views, items in the Alias-List must be prefixed with the table.

Conditions

The form of a condition is:

(all|any)(Alias Operator Literal(s,)... ...)

The all prefix requires that all condidtions must be met and any requires that one of the condition must met (AND/OR equivalent).

The available Operators are:

• Equal =
• Greater Than >
• Less Than <
• Greater or Equal >=
• Less or Equal <=
• Not Equal <>
• Between bw
• In List in
• String Contains ?
• String is Blank _
• String is Not Blank !
• String Starts With ^
• String Ends With $

Two or more conditions can be chained with and or or operator.

Condition [(and|or) Condition]...

Examples

---

Table of contents

• Address Book
• Purchase Order
• Inventory
• Work Orders
• Sales Orders