Documentation
Home Evaluation Summary for programmers Product limitations Goals of Axisbase Quick start Installation Using the launchpad and opening databases Connecting to a sample database Using building blocks Planning Define the purpose Define the requirements Borrow existing work Determine the architecture Design the data model Design the process model Deploy and maintain the product
Tutorials
building blocks
Performing a mailmerge Bulk e-mailing
Programming
Single-threaded progress indicator in c#
Reference
Database menu items
Import XML Save Copy As Integrity Check Change Password
Database Properties window
Opening the database properties window Record types tab Display types tab Roles and Users tabs Sidebar tab Database ID/Links tab Counters tab
Building blocks
Building blocks window Editing grids and cells Hyperlinks and nesting Data Subset window Data Outsource window List window Window window Report window Bulk Operation window Label Printer window Choosing a data source
Special topics
Expression syntax Browse records Storing building blocks within other building blocks Programming Using custom code in building blocks Using Axisbase as an embedded database Axis1.Util namespace reference Axis1.Data namespace reference (Fishnets) Axis1.Data namespace reference (other) Axis1.Forms namespace reference

Expression syntax

Axisbase uses expressions in several places:

  • In data subsets, to define a calculated column.

  • In data subsets, to define a complex filter.

  • In hyperlink arguments.

  • In data cells' display and default-value expressions.

Expression basics and examples

To refer to a column in a data table, use the # character followed by the complete column name, which is usually the Axis ID, record type name, and property name separated by dots. Calculated columns have whatever name you assign and don't follow this pattern. Examples:

#SATURN.person.haircolor

#mycalculatedcolumn

To refer to a parameter, use the @ character. Example:

@namestartswith

You can use basic arithmetic:

#SATURN.employee.salary * 1.05

For a filter, you can compare column values with literal values, and use && for "and" and || for "or":

#SATURN.employee.salary >= 20000 && #SATURN.employee.dept = 2

You can combine text in quotes with other things:

"Showing all customers whose name starts with " @namestartswith

Formatting

To change the default formatting for numbers and dates, add [format-codes] to the expression. Example:

#SATURN.person.birthdate [yyyy-M]

This shows the year and month of the birthdate. You can combine formats with other operators:

"born in (#SATURN.person.birthdate [M]) " of the year " (#SATURN.person.birthdate [yyyy])

Here are the format characters that can be applied to numeric data types:

  • Use "C"  for currency; "C3" to format currency with three decimal places (or any other number)
  • Use "F4" to format with four decimal places (or any other number)
  • Use "N" to format large numbers with commas
  • Use a pattern like "#,###,##0.#" to control formatting more precisely. The difference between 0 and # is that using 0 causes the number to be shown with leading zeroes, while # does not. So, the number 123 formatted with [0,000.00] would appear as 0,123.00.
  • Use two formats separated by a semicolon to provide a different format for negative numbers. For example the number -9 formatted with [#;(#)] would appear as (9).

Here are the format characters that can be applied to datetime data:
  • Use "d" for the standard short date pattern or "D" for the standard long date pattern. (no time component)
  • Use "F" for the standard full date and time pattern.
  • Combine characters for year, month, day, hour, minute, and second components to format the datetime any way you like. For example [yyyy - d/M] would format a date as "1999 - 12/31". The codes for each component are:
yy
 year - two digits
yyyy
 year - four digits
M
 month
MM
 month with leading zero
MMM
 month name - abbreviated
MMMM
 month name
d day
dd
 day with leading zero
ddd
 day of week - abbreviated
dddd
 day of week
h
 hour (1-12)
hh
 hour (1-12) with leading zero
HH
 hour (0-23) with leading zero
mm
 minute with leading zero
ss
 second with leading zero
The "leading zero" means that if the number is less than 10, it will be shown with two digits, as in 01, 02, etc.

  • bool type – If the format is two values separated by commas, then they are used instead of true and false (listed in order of true, false). Example: [yes,no]

Note to programmers: The characters within the square brackets are used in the .NET ToString() call. To find a complete list of format strings, see http://msdn2.microsoft.com/en-us/library/427bttx3.aspx, or search msdn2.microsoft.com for "format strings".

Literals

Literals are constant values that are not variables. Axisbase understands these literal types:

  • strings: text in double quotes, such as "hello"

  • decimal: number with decimal point, such as 2.0

  • int64: number without decimal point, such as 2

  • bool: the words "true" or "false"

Identifiers

Identifiers must start with a prefix character: # for column names, @ for parameters, or $ for some special values.

Identifiers may contain letters, digits, and the three symbols . _ ^

The available special values are:

  • @page = the current page number in a report

  • @pages = the total number of pages in a report

  • $now = the current date and time

Operators

To concatenate two values as strings, separate them with a space.

If a format string is followed by another format string or a string value, they are combined into a longer format string. For example, if the value of the parameter @fmt is "dd", then the expression $now ([M-] @fmt) would be the same as $now [M-dd].

Operators to compare equality are: =, <>, >, >=, <, <=

The "and" operator is &&. The "or" operator is ||.

Arithmetic operators are +, -, *, /, %. (% calculates the remainder of a division; for example 10 % 3 = 1.)

Functions

The following built in functions are used without a prefix character.

  • length(string) = the length of a string or blob field.

  • date(year, month, day) = the given date. Example: date(1999, 12, 31). This returns a date data type.

  • datetime(year, month, day, hour, minute) = the given date and time. Example: datetime(1999, 12, 31, 23, 59). This returns a datetime data type.

  • year(d), month(d), day(d) = the year, month, or day of the given date. Example: year($now)

  • floor(f) = the nearest integer less than or equal to the given floating point number. Example: floor(4.9) results in 4.

  • round(f) = the nearest integer to the given floating point number. Example: round(4.9) results in 5. If the argument f is double, the result type is integer; if the argument type is decimal, the result type is decimal.

  • iif(condition, truevalue, falsevalue) = truevalue if the condition is true, else falsevalue. Iif stands for "immediate if". Example: iif(@deptno=0, "no department", "dept " @deptno)

  • convert(condition1, result1, condition2, result2, ...) = finds the first condition that is true and returns the corresponding result. Example: convert(1>2, "impossible", 2>3, "never", 8>7, "this will be returned")

  • decode(source, option1, result1, option2, result2, ...) = finds the first option whose value equals source, and returns the corresponding result. Example: decode(@deptno, 1, "sales", 2, "shipping", 3, "art")

  • now() = the current date and time, same as $now

  • today() = the current date with no time (that is, 12:00 AM)

  • match(data, pattern) = true if the data matches the regular expression pattern (see section below)
  • matchf(data, terms) = the number of search terms within 'terms' that exist in data (see section below)

pattern matching & full text searches

There are two functions that allow you to do full-text searches - that is, searching for words anywhere in a string. These are match and matchf. (Note that these functions are only available in a complex filter; the fast filter can only compare the beginning of a string, not do a full text search.)

Match is used with wildcard characters to give you control over exactly how a single term is matched. An example would be

match(@SATURN.person.name, "smith")

The example expression would return true if the person's name contained "smith" anywhere in it. However, this would not match "Smith" with a capital S. For that you would need the more complex:

match(@SATURN.person.name, "(?i:smith)")

The sequence (?i: means "ignore upper/lower case", and requires a closing parenthesis. In practice you would allow the user to enter a search term as a parameter, and combine that with the "ignore case" construct, like this:

match(#SATURN.person.name, "(?i:" @namelike ")") 

The example immediately above is similar to the SQL "LIKE" operator. But it is a lot more powerful since it allows you to use all regular expression syntax provided by Microsoft .NET. Learn all about it here: http://http://msdn2.microsoft.com/en-us/library/hs600312.aspx

The other function, matchf, is a "fuzzy" search, a very basic version of what you would expect with a search engine like Google. Here is an example:

matchf(#SATURN.product.notes, "contrast paint thinner")  > 0

The function matchf returns the number of matches; so in a particular record, if the notes field was "The painter is thinner than the sculptor", then matchf would return 2. In the example expression, including >0 means to return true if one or more words are matched.

c# expressions

Expressions in the c# language can be embedded in Axisbase expressions. Use the delimiters <% and %> to mark c# expressions. To obtain the value of columns and parameters within c# expressions, you have to embed Axisbase expressions within c# expressions (which are embedded in Axisbase expressions!) Use methods stringex, intex, doubleex, decimalex, dateex, datetimeex, and boolex to evaluate Axisbase expressions from within c# expressions. intex returns the type Int64, while the other methods return types consistent with their names.

Examples:

  • <% 2 + 2 %> + 2 would evaluate to 6

  • <% intex("1+1") + 1 %> would evaluate to 3

  • <% stringex("#SATURN.person.name").Substring(0, 3) %> would evaluate to the first three letters of a person's name

  • "In a fortnight, the year will be " ( <% DateTime.Today.AddDays(14) %> [yyyy] )

(c) 2014-2015 Divergent Labs, Inc.