# Syntax & Functions

## Functions index

| Instruction / Function         | Description                                            | Section                                               |
| ------------------------------ | ------------------------------------------------------ | ----------------------------------------------------- |
| `<%SystemName%>`               | Insert a field by its system name                      | [Inserting a Simple Field](#inserting-a-simple-field) |
| `<%MarkDown(SystemName)%>`     | Insert a rich text field preserving layout (Word only) | [Inserting a Simple Field](#inserting-a-simple-field) |
| Field arithmetic `<% A + B %>` | Arithmetic between fields                              | [Inserting a Formula](#inserting-a-formula)           |
| `Repeat()`                     | Repeat a table row for each submission in listing mode | [Repeat()](#repeat)                                   |
| `DISTINCT()`                   | Suppress duplicate values in a listing                 | [Repeat()](#repeat)                                   |
| `Image()` / `Img()`            | Insert a single image                                  | [Image Manipulation](#image-manipulation)             |
| `Images()`                     | Insert all images from a multi-image field             | [Image Manipulation](#image-manipulation)             |
| `UseImagesPreview()`           | Resize images before inserting to reduce file size     | [Image Manipulation](#image-manipulation)             |
| `#xComment`                    | Retrieve the comment attached to an image              | [Image Manipulation](#image-manipulation)             |
| `SetPdfJpegQuality()`          | Convert images to JPEG with a given quality index      | [Image Manipulation](#image-manipulation)             |
| `Map()`                        | Display a GPS map centred on the submission location   | [Image Manipulation](#image-manipulation)             |
| `ImageExif()`                  | Extract Exif metadata (date, GPS) from a photo         | [Image Manipulation](#image-manipulation)             |
| `BarCode()`                    | Insert a barcode                                       | [Image Manipulation](#image-manipulation)             |
| `QrCode()`                     | Insert a QR code                                       | [Image Manipulation](#image-manipulation)             |
| `ImageWatermark()`             | Overlay text on an image                               | [Image Manipulation](#image-manipulation)             |
| `Index()`                      | Auto-incremented row number in a listing               | [Utility Functions](#utility-functions)               |
| `MapLetter()`                  | Pin letter generated by Google Maps                    | [Utility Functions](#utility-functions)               |
| `Format()` / `F()`             | Format a date or number field                          | [Utility Functions](#utility-functions)               |
| `Date()`                       | Current date and time                                  | [Utility Functions](#utility-functions)               |
| `AddHours()`                   | Add or subtract hours from a date                      | [Utility Functions](#utility-functions)               |
| `AddDays()`                    | Add or subtract days from a date                       | [Utility Functions](#utility-functions)               |
| `AddMonths()`                  | Add or subtract months from a date                     | [Utility Functions](#utility-functions)               |
| `AddYears()`                   | Add or subtract years from a date                      | [Utility Functions](#utility-functions)               |
| `Add()`                        | Sum multiple numeric values, treating null as 0        | [Utility Functions](#utility-functions)               |
| `ToUpper()` / `ToLower()`      | Convert a string to upper or lower case                | [Utility Functions](#utility-functions)               |
| `List()`                       | Join multiple values with a separator                  | [Utility Functions](#utility-functions)               |
| `Split()`                      | Extract a segment from a delimited string              | [Utility Functions](#utility-functions)               |
| `ToNumber()` / `N()`           | Convert text to a number                               | [Utility Functions](#utility-functions)               |
| `ToDate()` / `D()`             | Convert text to a date                                 | [Utility Functions](#utility-functions)               |
| `ToString()` / `S()`           | Convert a value to text                                | [Utility Functions](#utility-functions)               |
| `If()`                         | Return a value based on a condition                    | [Conditional Statements](#conditional-statements)     |
| `Exists()`                     | Test whether a field exists in the form                | [Conditional Statements](#conditional-statements)     |
| `Hide()` … `<%End%>`           | Hide a document section when a condition is true       | [Conditional Statements](#conditional-statements)     |
| `Display()` … `<%End%>`        | Show a document section when a condition is true       | [Conditional Statements](#conditional-statements)     |
| `DisplayRow()`                 | Show a table row when a condition is true              | [Conditional Statements](#conditional-statements)     |
| `Contains()`                   | Test whether a string contains a pattern               | [Conditional Statements](#conditional-statements)     |
| `StartsWith()`                 | Test whether a string starts with a value              | [Conditional Statements](#conditional-statements)     |
| `EndsWith()`                   | Test whether a string ends with a value                | [Conditional Statements](#conditional-statements)     |
| `Relation()`                   | Repeat rows for each related child submission          | [Relationship](#relationship)                         |
| `Count()`                      | Count submissions in the current set                   | [Aggregation Functions](#aggregation-functions)       |
| `Sum()`                        | Sum a numeric field across all submissions             | [Aggregation Functions](#aggregation-functions)       |
| `Avg()`                        | Average value of a field across all submissions        | [Aggregation Functions](#aggregation-functions)       |
| `Min()` / `Max()`              | Minimum or maximum value of a field                    | [Aggregation Functions](#aggregation-functions)       |
| `GlobalMap()`                  | Display multiple GPS points on a single map            | [Aggregation Functions](#aggregation-functions)       |
| `Chart()`                      | Display a bar or pie chart from aggregated data        | [Aggregation Functions](#aggregation-functions)       |
| List field accessors           | Extract levels or individual items from a list field   | [Special Cases](#special-cases)                       |
| User field accessors           | Extract names and emails from a user field             | [Special Cases](#special-cases)                       |
| Workflow state tags            | Display the current workflow state and its colour      | [Special Cases](#special-cases)                       |
| `FileImage()`                  | Insert PDF pages as images in a report                 | [Special Cases](#special-cases)                       |
| `FilePageCount()`              | Count pages in an attached PDF                         | [Special Cases](#special-cases)                       |

***

## System Fields

Use the following tags to access submission metadata in any report template.

### Submission identification

| Field             | Tag                            |
| ----------------- | ------------------------------ |
| Submission number | `<%SubmissionId%>` or `<%Id%>` |
| UID number        | `<%SubmissionUid%>`            |

### Submission creation

| Field                       | Tag                          |
| --------------------------- | ---------------------------- |
| Date of first save on web   | `<%Created_At%>`             |
| Date of submission creation | `<%DataCreatedAt%>`          |
| Creator — first name        | `<%[CreateUser.FirstName]%>` |
| Creator — last name         | `<%[CreateUser.LastName]%>`  |
| Creator — email             | `<%[CreateUser.Email]%>`     |

### Last update

| Field                     | Tag                      |
| ------------------------- | ------------------------ |
| Date of last save on web  | `<%[Updated_At]%>`       |
| Date of submission update | `<%[Updated_At]%>`       |
| Last modified by (email)  | `<%[UpdateUser.Email]%>` |

### Form

| Field           | Tag                 |
| --------------- | ------------------- |
| Form name       | `<%StructureName%>` |
| Form identifier | `<%StructureId%>`   |

### Related task

| Field                        | Tag                       |
| ---------------------------- | ------------------------- |
| Planned start date and time  | `<%[Task.PlannedStart]%>` |
| Expected end date and time   | `<%[Task.PlannedEnd]%>`   |
| Deadline                     | `<%[Task.PlannedDue]%>`   |
| Actual start date and time   | `<%[Task.RealStart]%>`    |
| Effective end date and time  | `<%[Task.RealEnd]%>`      |
| State (done, to be done)     | `<%[Task.FillStatus]%>`   |
| Time status (on time, late…) | `<%[Task.TimeStatus]%>`   |

### Other information

| Field                                 | Tag                                             |
| ------------------------------------- | ----------------------------------------------- |
| Company name (from URL)               | `<%CompanyTitle%>`                              |
| Native GPS position (mobile creation) | `<%SubmissionLatitude%><%SubmissionLongitude%>` |

## Inserting a Simple Field

A form field is inserted by surrounding its system name with `<%` and `%>`. These markers tell the system where the submission data should be inserted. You can apply any formatting supported by Word/Excel (bold, italic, underline) directly to the tag.

```
<%System_Name%>
```

If your Text field uses the "Rich Text" option, use `<%MarkDown(SystemName)%>` to preserve the layout in Word reports. This is not functional in Excel.

{% hint style="warning" %}
System names are case sensitive.
{% endhint %}

## Inserting a Formula

You can apply calculations directly between fields in a report tag.

```
<%FieldName1 <operator> FieldName2 | ConstantExpression%>
```

`<operator>` can be any mathematical operation: `+`, `-`, `*`, `/`, `%`

* **ConstantExpression** is a number with or without decimal places
* Parentheses are supported
* `%` is also the modulo operator

**Examples:**

* `<%Price * 1.196%>` — With Price = 10: result is **11.96**
* `<%(Price * Quantity) – (15 / 100) * (Price * Quantity)%>` — With Price=18 and Quantity=2: result is **30.6**

Here is the complete list of supported mathematical functions. These are also **case sensitive**.

![](/files/CggIAkPgiDl7dvXAFrLb)

## Repeat()

When the report template is in **Listing Mode**, the `Repeat()` instruction repeats a section of the document for each row of data. It must be placed in the first (leftmost) cell of the table row to repeat.

```
<%Repeat(filter, sort)%>
```

| Parameter | Description                                                       | Default |
| --------- | ----------------------------------------------------------------- | ------- |
| `filter`  | Filtering condition, e.g. `Id>3` or `DateField<#date#` (optional) | None    |
| `sort`    | Field system name to sort results by (optional)                   | None    |

{% hint style="info" %}
The entire template can only contain one `Repeat()` instruction. If several are present, only the first one is detected.
{% endhint %}

Ex :

<figure><img src="/files/3I5NzP4NGni3G6GTFxob" alt=""><figcaption></figcaption></figure>

With 3 lines of data, the result is as follows:

<figure><img src="/files/s5eoLrzZKees92XuMJ2z" alt=""><figcaption></figcaption></figure>

`Repeat()` can be used in four ways:

| Usage                      | Syntax                  |
| -------------------------- | ----------------------- |
| Without any criteria       | `<%Repeat()%>`          |
| Filtering only             | `<%Repeat(Id>3)%>`      |
| Sorting only               | `<%Repeat(,Name)%>`     |
| Both filtering and sorting | `<%Repeat(Id>3,Name)%>` |

### Filtering results

Filter the repeated rows using a condition:

```
<%Repeat(DateField > #12/01/2023#)%>
```

Dates use the format `Day/Month/Year`. You can also use `#date#` as a variable for today's date:

```
<%Repeat(DateField < #date#)%>
<%Repeat(DateField < #date-5d#)%>
```

The second example means "5 days before today". The format is not case sensitive (`#Date-5D#` also works).

You can define the time interval using the units in the table below:

![](/files/AR0b2Im7d6zhYyr6S9Rn)

{% hint style="info" %}
Use `+` instead of `-` to refer to future dates. Example: `<%Repeat(DateField < #date+10d#)%>` means today + 10 days.
{% endhint %}

You can combine filter conditions using `AND` or `OR` operators:

```
<%Repeat(DateField<#date-5d# AND Id >= 3)%>
```

{% hint style="warning" %}
This filter syntax does not support functions that use Field System Names.
{% endhint %}

### Avoiding data redundancy — DISTINCT()

When using `Repeat()`, use `DISTINCT()` to avoid repeating the same value:

```
<%DISTINCT('Client')%>
```

For fields with multiple separate texts (dates, multi-level lists), use a second argument as a concatenation separator:

```
<%DISTINCT('F(\'MM yyyy\',CreatedAt)', ',')%>
```

This returns all unique months of submission creation across the repeated set.

### Sorting results

`Repeat()` accepts a second parameter to sort the results. The sorting criterion must be a column name:

```
<%Repeat(filteringCriteria, SortingCriteria)%>
```

Example:

```
<%Repeat(Id>15860, Name)%>
```

![](/files/xDRepBIIwscnLmIDIQeS)

## Image Manipulation

### Image()

Insert a single image from an image field:

```
<%Image(FieldName, Width, Height)%>
```

Shorthand alias: `<%Img(FieldName, Width, Height)%>`

| Parameter   | Description                       | Default         |
| ----------- | --------------------------------- | --------------- |
| `FieldName` | System name of the image field    | —               |
| `Width`     | Image width in pixels             | —               |
| `Height`    | Image height in pixels (optional) | Same as `Width` |

* If height is omitted, the image is placed in a square of `Width × Width` pixels
* If both width and height are specified, the image fits that rectangle without distortion

**Examples:**

Large photo: `<%Image(ImageFieldName, 200)%>`

![](/files/fyt50MDdKhVjvzgsOt74)

Small photo: `<%Img(ImageFieldName, 70, 50)%>`

![](/files/TERSdMLtYgb6duxtNkOI)

### Images()

For image fields that allow multiple images (max > 1), use `Images()` inserted in a one-column table:

```
<%Images(FieldName, start, end)%>
```

| Parameter   | Description                                    | Default |
| ----------- | ---------------------------------------------- | ------- |
| `FieldName` | System name of the multi-image field           | —       |
| `start`     | Index of the first image to include (optional) | 1       |
| `end`       | Index of the last image to include (optional)  | All     |

![](/files/Br9lFUYLW2tsS7JrN7u4)

To define a size for the images, add `<%Image()%>`:

```
<%Images(Photo)%><%Image(400)%>
```

To retrieve additional data (metadata) from the images, enter the field in a table with the following structure:

![](/files/sX0zxY8Oy2eWgH8xlNaF)

### Resizing images — UseImagesPreview()

Resize images before inserting them to reduce report weight and generation time:

```
<%UseImagesPreview('ImageSize')%>
```

Replace `ImageSize` with the attribute from the table below. All images following this tag will be resized accordingly:

![](/files/QCzSuWNP4DCaahVERz2o)

{% hint style="info" %}
`UseImagesPreview()` does not allow extraction of Exif data. There is no Exif information on preview images.
{% endhint %}

### Retrieving comments of an image

Display the comment attached to a specific image in an image field:

```
<%Value:If(Exists('ImageFieldName#xComment'), [ImageFieldName#xComment], '')%>
```

{% hint style="info" %}
**Explanation:**

* `ImageFieldName` is the system name of the image field.
* `#x` indicates the image number (1, 2, 3, etc.).
* `Comment` accesses the comment associated with the image.
  {% endhint %}

Example: `<%Value:If(Exists('Photo#1Comment'), [Photo#1Comment], '')%>`

### Convert images to JPG — SetPdfJpegQuality()

Reduce image size by converting them to JPEG. The function takes a quality index between 1 and 100:

```
<%SetPdfJpegQuality(10)%>
```

Add this tag in the Word document or in a cell of the PPActions sheet.

### Insert an image from a relationship field (Excel)

To insert a photo from a related submission in an Excel report (not in listing mode):

```
<%Img([RelationshipSystemName.ImageSystemName], 60, 60)%>
```

{% hint style="info" %}
Replace snake\_case with CamelCase: capitalise the first letter of each word in the system name and remove underscores.
{% endhint %}

### Map()

Display a map centered on the GPS coordinates of the submission. Requires the Google Maps API and a location field configured in the form.

```
<%Map(latitude, longitude, zoom, width, height, MapType)%>
<%Map(address, zoom, width, height, MapType)%>
```

| Parameter   | Description                               |
| ----------- | ----------------------------------------- |
| `latitude`  | Field containing the latitude coordinate  |
| `longitude` | Field containing the longitude coordinate |
| `address`   | Your location field system name           |
| `zoom`      | 1 to 22 (22 = most detailed)              |
| `width`     | Map width in pixels                       |
| `height`    | Map height in pixels                      |
| `MapType`   | 0 = Street View, 1 = Satellite, 2 = Mixed |

Example with default location: `<%Map(SubmissionLatitude, SubmissionLongitude, 17, 200, 200)%>`

![](/files/KdOjdCmLmAWPBYF38hUL)

Example with a location field: `<%Map(LocalizationIntervention_latitude, LocationIntervention_longitude, 17, 200, 200)%>`

![](/files/RHKX2lebyBYEzyUcWt7N)

### Exif data — ImageExif()

Retrieve Exif metadata stored in a photo (text data, GPS coordinates, etc.):

```
<%ImageExif(FieldName, 'ExifAttribute')%>
```

| Parameter         | Description                                                                                 |
| ----------------- | ------------------------------------------------------------------------------------------- |
| `FieldName`       | System name of the image field                                                              |
| `'ExifAttribute'` | Exif attribute key, e.g. `'DateTime'`, `'GpsLatitude'`, `'GpsLongitude'`, `'GpsLatitudeDM'` |

![](/files/yLjOjekXwKWoLBRSwoGv)

**Examples:**

* Image date: `<%ImageExif(Photo, 'DateTime')%>` *(Result: 08/25/2004)*
* Map from photo GPS: `<%Map(ImageExif(LocationField, 'GpsLatitude'), ImageExif(LocationField, 'GpsLongitude'), 17, 150, 150)%>`

![](/files/RHKX2lebyBYEzyUcWt7N)

{% hint style="info" %}
For Excel reporting in Listing mode, the instruction to get Exif data is: `<%Value:ImageExif([Photo],'DateTime')%>` *(Result: 25/08/2004 09:32)*
{% endhint %}

### Barcodes — BarCode()

Insert a barcode into a document:

```
<%BarCode(data, 'BarCodeType', width, height)%>
```

| Parameter       | Description                            |
| --------------- | -------------------------------------- |
| `data`          | The data to encode                     |
| `'BarCodeType'` | Barcode format — see types table below |
| `width`         | Barcode width in pixels                |
| `height`        | Barcode height in pixels               |

**Example:** `<%BarCode('123456789012', 'EAN13', 120, 120)%>`

![](/files/Sf8jhbOjJldxqg0OBCEA)

Available barcode types:

| Code                | Description                                                            |
| ------------------- | ---------------------------------------------------------------------- |
| **Code11**          | Primarily used for telecommunications equipment labeling               |
| **Codabar**         | Old code used in library and blood bank systems                        |
| **EAN13**           | European article numbering — data must be 12 digits                    |
| **EAN8**            | Compressed EAN for small products — data must be 7, 9, 12 or 13 digits |
| **Industrial2of5**  | Old code, not in common use                                            |
| **Interleaved2of5** | Compact digital code, used in industry and air cargo                   |
| **MSI**             | Variation of the Plessey code, common in the US                        |
| **Postnet**         | Used by the US Postal Service for automated mail sorting               |
| **UPCA**            | Universal product code for retail — data must be 12 digits             |
| **UPCE**            | Compressed UPC for small products — data must be 6 or 7 digits         |
| **Matrix2of5**      | Data can be any number of digits                                       |
| **IntelligentMail** | Data must be 20 digits                                                 |

### QR Codes — QrCode()

Insert a 2D QR code into a template:

```
<%QrCode(data, width, height)%>
```

| Parameter | Description              |
| --------- | ------------------------ |
| `data`    | The data to encode       |
| `width`   | QR code width in pixels  |
| `height`  | QR code height in pixels |

Example: `<%QrCode('A String To Encode 123456', 150, 150)%>`

![](/files/pKafkzs0GPRPeLE2cs4K)

### Watermark — ImageWatermark()

Add text overlay on an image:

```
<%ImageWatermark(ImageFieldName, ImageWidth, ImageHeight, WatermarkText, HorzAlign, VertAlign, ForegroundColor, BackgroundColor, BackGroundOpacity, FontSize)%>
```

| Parameter           | Description                                 | Default       |
| ------------------- | ------------------------------------------- | ------------- |
| `ImageFieldName`    | System name of the image field              | —             |
| `ImageWidth`        | Image width in pixels                       | —             |
| `ImageHeight`       | Image height in pixels                      | —             |
| `WatermarkText`     | Text to display on the image                | —             |
| `HorzAlign`         | Horizontal alignment: Left, Center, Right   | Right         |
| `VertAlign`         | Vertical alignment: Top, Middle, Bottom     | Bottom        |
| `ForegroundColor`   | Text color (#RRGGBB or constant)            | Red           |
| `BackgroundColor`   | Text background color (#RRGGBB or constant) | White         |
| `BackGroundOpacity` | Background opacity between 0 and 1          | 0 (invisible) |
| `FontSize`          | Font size in pixels                         | 12            |

**Examples:**

```
<%ImageWatermark([Support], 300, 0, 'Welcome')%>
<%ImageWatermark([Support], 300, 0, 'Welcome', 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
```

To insert a line break in the watermark text, use `\u000A`:

```
<%ImageWatermark(ImageSystemName, 300, 0, 'Hello\u000Aworld\u000A!', 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
```

![](/files/4k19eMSPfu4IksVd9I6G)

You can combine multiple data values using `List()`:

```
<%ImageWatermark([ImageSystemName], 300, 0, List(',', ImageExif(ImageSystemName, 'GpsLatitudeDM'), ImageExif(ImageSystemName, 'GpsLongitudeDM')), 'Left', 'Top', 'Red', 'White', 0.9, 14)%>
```

To use a field value as the watermark text:

```
<%ImageWatermark(ImageSystemName, 300, 0, FieldSystemName, 'Center', 'Middle', 'Red', 'White', 0.9, 14)%>
```

### Watermark on a multi-image field

When an image field contains several images, combine `Images()` and `ImageWatermark()`:

```
<%Images(SystemNameImagefield)%><%ImageWatermark(200, 0, List(',', ImageExif('GpsLatitude'), ImageExif('GpsLongitude')), 'Center', 'Middle', 'Red', 'White', 0.9, 14)%>
```

{% hint style="info" %}
The combined instructions must be placed inside a table in the template. The image field system name is mentioned only once, in the `Images()` instruction — do not repeat it in `ImageWatermark()` or `ImageExif()`.
{% endhint %}

## Utility Functions

Functions are **not case sensitive** and can be combined. We recommend using CamelCase for function names.

{% hint style="warning" %}
Field names remain case sensitive in all cases.
{% endhint %}

### Index()

Returns an auto-incremented sequence number in a listing array.

`<%Index()%>`

![](/files/ss19mTVIcqsy1lWnSmCl)

![](/files/WJl2MOWvekqwsdTO6hfb)

### MapLetter()

Returns the letter of the map pin generated by the Google Maps API for a location point. Used in combination with `Map()`.

`<%MapLetter()%>`

![](/files/lYMUBNE3ZcdMN2fTTCsM)

Example:

![](/files/ss19mTVIcqsy1lWnSmCl)

Result:

![](/files/WJl2MOWvekqwsdTO6hfb)

### Format() — dates and numbers

Convert a Date or Number field to a specific display format. `F()` is a shortcut for `Format()`.

```
<%Format('FormatString', FieldName)%>
<%F('FormatString', FieldName)%>
```

| Parameter        | Description                      |
| ---------------- | -------------------------------- |
| `'FormatString'` | Format pattern — see table below |
| `FieldName`      | Date or Number field to format   |

The available format patterns are shown in the table below:

![](/files/eTR1WQIdveHRdhkYPGH1)

**Examples** (with Price = 19.3 and Date = 02/14/2012 3:14:20 PM):

* `<%Format('0.00', Price)%>` → **19.30**
* `<%F('yyyy-MM-dd', Date)%>` → **2023-02-16**

#### Date()

Returns the current date and time:

```
<%Date()%>
<%F('dd/MM/yyyy', Date())%>
```

#### AddHours()

Add or remove hours from a date/time field:

```
<%AddHours(SystemName, NumberHours)%>
```

Example: `<%AddHours(DateTime, -6)%>` *(Result: 02/14/2023 06:00:00 instead of 12:00:00)*

{% hint style="info" %}
Use a positive number to add hours: `<%AddHours(DateTime, 6)%>`
{% endhint %}

#### AddDays()

```
<%AddDays(Date, NumberOfDays)%>
```

`NumberOfDays` can be positive or negative. Example: `<%AddDays(Date(), -5)%>`

#### AddMonths()

```
<%AddMonths(Date, NumberOfMonths)%>
```

Example: `<%AddMonths(Created, -1)%>`

#### AddYears()

```
<%AddYears(Date, NumberOfYears)%>
```

Example: `<%AddYears(Created, +2)%>`

### Add()

Returns the sum of multiple numerical values. Null or empty values are treated as 0.

```
<%Add(Value1, Value2, Value3, …)%>
```

Example: `<%Add(Size1, Size2, Size3)%>` — With Size1=5.3, Size2=2.3, Size3=null → **7.6**

### String manipulation

A character string is expressed via `<%String()%>` or its shortcut `<%S()%>`.

#### Concatenation

Use the `+` operator to concatenate strings. When mixing types, convert numbers to strings first with `S()`:

`<%S(Price) + ' Euros'%>` → **14.32 Euros**

#### ToUpper() / ToLower()

```
<%ToUpper(FieldName)%>
<%ToLower(FieldName)%>
```

#### List()

Create a separated list from multiple values. If any element is empty, its separator is omitted:

```
<%List(separator, value1, value2, …)%>
```

| Parameter           | Description                    |
| ------------------- | ------------------------------ |
| `separator`         | String to place between values |
| `value1, value2, …` | Fields or values to join       |

Example: `<%List(', ', Address, PostalCode, City, Country)%>` → **12 rue des églantiers, 17400, Lacanau, France**

Can be combined with `Map()` to produce a valid address from separate fields.

#### Split()

Extract a value from a string containing a separator. Index starts at 0:

```
<%Split(string, separator, IndexToExtract)%>
```

| Parameter        | Description                               |
| ---------------- | ----------------------------------------- |
| `string`         | The string or field to split              |
| `separator`      | The delimiter character                   |
| `IndexToExtract` | Zero-based index of the segment to return |

Example: `<%Split(Address, ',', 2)%>` — With Address = "12, rue des mines, Puteaux, 92800, France" → **Puteaux**

### Type conversion

#### ToNumber() / N()

Convert a text string to a number:

```
<%ToNumber(FieldName)%>
<%N(FieldName)%>
```

Example: `<%Format('0.00', N(Price))%>` — With Price = "127.4202" → **127.42**

{% hint style="info" %}
The converted number can use either a comma `,` or period `.` as decimal separator — both are supported.
{% endhint %}

#### ToDate() / D()

Convert a text date to a real date:

```
<%ToDate(FieldName)%>
<%D(FieldName)%>
```

Example: `<%F('dd/MM/yyyy', ToDate(Date))%>` — With Date = '02/14/2012 3:24:10 PM' → **02/14/2012**

#### ToString() / S()

Convert the content of a field to a string:

```
<%ToString(FieldName)%>
<%S(FieldName)%>
```

## Conditional Statements

### If()

Display a value based on a condition:

```
<%If(condition, 'TrueResult', 'FalseResult')%>
```

| Parameter                   | Description                                                                     |
| --------------------------- | ------------------------------------------------------------------------------- |
| `condition`                 | A boolean expression (e.g. `Field = 'Value'`). Use `&&` for AND, `\|\|` for OR. |
| `'TrueResult'`              | Value returned when the condition is true                                       |
| `'FalseResult'`             | Value returned when the condition is false                                      |
| `'NotSetResult'` (optional) | Value returned when the field is empty — enables the 3-value shorthand form     |

The condition must return `true` or `false`. The supported operators are:

| Operator | Meaning        |
| -------- | -------------- |
| =`=`     | Equal to       |
| `<>`     | Different than |
| `!=`     | Not equal      |
| `&&`     | AND            |
| `\|\|`   | OR             |

You can combine field comparisons with `&&` and `||`:

```
<%If((Boolean=='True' && Statut='Création'), 'valueIfTrue', 'valueIfFalse')%>
```

**Boolean example** — cover all three possible states (true, false, not set):

```
<%If(Booleen=='True', 'Yes', '')%><%If(Booleen=='False', 'No', '')%><%If(Booleen=='', '', '')%>
```

Or with the shorthand 3-value form:

```
<%If(MyBoolean, 'valueIfTrue', 'valueIfFalse', 'valueIfNotSet')%>
```

{% hint style="info" %}
The apostrophe character differs between Word and Excel. If you copy-paste a template from one to the other, replace the apostrophes manually.
{% endhint %}

### Exists()

Test whether a field exists in the form:

```
<%Exists('FieldName')%>
```

Returns `true` or `false`. Especially useful for checking the presence of images:

```
<%If(Exists('Photos#1'), Image([Photos#1], 180), '')%>
```

### Hide() / Display() / DisplayRow()

These functions show or hide entire sections of the document based on a condition. Always close with `<%End%>`.

#### Hide()

Hides a section when the condition is true:

```
<%Hide(Condition)%> Content to hide <%End%>
```

Supported Boolean operators:

| Operator | Meaning        |
| -------- | -------------- |
| `\|\|`   | OR             |
| `&&`     | AND            |
| `<>`     | Different than |
| `==`     | Equals         |
| `!=`     | Is not equal   |

Example with complex condition:

```
<%Hide((Field1 == 'value' || Field1 == 'value2') && Field3 != '')%> Some content is hidden <%End%>
```

#### Display()

Shows a section when the condition is true (reverse of `Hide()`):

```
<%Display(Condition)%> Content to show <%End%>
```

{% hint style="warning" %}
It is not possible to use both `Hide()` and `Display()` in the same condition.
{% endhint %}

#### DisplayRow()

Available in tables only. Displays a row when the condition is true. Must be placed in the first (leftmost) cell of the row, before any other content:

```
<%DisplayRow(Condition)%>
```

{% hint style="warning" %}
It is not possible to use both `Hide()` and `DisplayRow()` in the same condition. It is also not possible to display the *current user* in reports.
{% endhint %}

### Contains()

Returns `true` if a string contains a given pattern:

```
<%Contains(String, pattern)%>
```

| Parameter | Description                 |
| --------- | --------------------------- |
| `String`  | The field or string to test |
| `pattern` | The substring to search for |

Example: `<%If(Contains(Field, 'measurement'), 'MEASURE', 'NOTHING')%>`

{% hint style="info" %}
The comparison is case sensitive. Use `ToUpper()` or `ToLower()` in combination with `Contains()` for case-insensitive matching: `<%If(Contains(ToUpper(Field), 'MEASURE'), 'MEASURE', 'NOTHING')%>`
{% endhint %}

### StartsWith()

Returns `true` if a string begins with the specified value:

```
<%StartsWith(String, Pattern)%>
```

| Parameter | Description                 |
| --------- | --------------------------- |
| `String`  | The field or string to test |
| `Pattern` | The prefix to search for    |

Example: `<%If(StartsWith(Field, 'default'), 'Default value', '')%>`

### EndsWith()

Returns `true` if a string ends with the specified value:

```
<%EndsWith(String, Pattern)%>
```

| Parameter | Description                 |
| --------- | --------------------------- |
| `String`  | The field or string to test |
| `Pattern` | The suffix to search for    |

Example: `<%If(EndsWith(Field, 'END'), 'end', '')%>`

## Relationship

To include data from a related child form, use the `Relation()` instruction inside a table:

```
<%Relation('Relationship field label')%>
```

The table row repeats once for each child submission. If no child submissions are linked, the table is empty.

Example:

![](/files/bTjstrVmqfECSoOZQneL)

Result:

![](/files/9zIiLZlEWlrJkddRVNRJ)

{% hint style="info" %}
The "Relationship element" field type is not persistent and cannot be used in a report.
{% endhint %}

### Sort a relationship

Sort the child submissions by a field value. Add the field system name as a second parameter.

Ascending: `<%Relation('Relationship label', 'SystemNameOfField')%>`

Descending: `<%Relation('Relationship label', 'SystemNameOfDESCField')%>`

### Filter by subform

When a relationship field links to multiple subforms, filter by form name:

```
<%Relation('Relationship label', OrderBy, StructureName='Form name')%>
```

* `OrderBy` corresponds to the sorting described above (leave empty string `''` to skip)
* `StructureName` is the name of the form to filter on

Example — keep only submissions from the "Anomaly" subform:

```
<%Relation('Anomalies observed', '', StructureName='Anomaly')%>
```

### Limit the number of child submissions

Pass the limit as the 4th parameter:

```
<%Relation('Relation0', 'FieldX DESC', StructureName='Form name', 2)%><%[String0]%>
```

| Parameter                   | Description                                    |
| --------------------------- | ---------------------------------------------- |
| `'Relation0'`               | System name of the relation field              |
| `'FieldX'`                  | Field to sort by (optional)                    |
| `StructureName='Form name'` | Form to filter on (optional)                   |
| `2`                         | Maximum number of child submissions to display |

### Count child submissions

Display the number of submissions in a relationship:

`<%[Relation.Count]%>`

{% hint style="warning" %}
Capital letters are required — `Relation.Count` must be written exactly as shown.
{% endhint %}

### Nested relationships

It is possible to insert the relationship of a relationship into your report. To do this, you must create a first table and insert your first relationship there, then insert a second table inside the first table:

in the following example we will see how to insert a relationship from the sub-form into a first table, named **"relationship label"** then how to insert a relationship from the sub-form into a second nested table. another form itself being related to this sub-form. This last relationship is called "**relationship relationship wording**".

It is possible to nest a relationship inside another relationship by creating a second table inside the first. You can go up to **8 levels deep**.

![](/files/q0GIpnkIfPinfCzBbEfq)

## Aggregation Functions

{% hint style="info" %}
Arguments in aggregation functions must always be wrapped in single quotation marks. Example: `<%Sum('ToNumber(Price)')%>`
{% endhint %}

### Count()

Counts the total number of submissions for the current set:

`<%Count()%>`

This statement can be used anywhere in the document.

### Sum()

Returns the sum of a numeric field across all submissions:

`<%Sum('FieldName')%>`

If the field type may not be numeric, use `N()` to convert: `<%Sum('N(Price)')%>`

### Avg()

Returns the average value of a field:

`<%Avg('FieldName')%>`

### Min() / Max()

Returns the minimum or maximum value of a field:

`<%Min('FieldName')%>` / `<%Max('FieldName')%>`

### GlobalMap()

Displays all location points from multiple submissions on a single map. Requires a template in Listing Mode.

```
<%GlobalMap('LatitudeField', 'LongitudeField', ConnectPoints, Width, Height, MapType)%>
```

| Parameter        | Description                                               |
| ---------------- | --------------------------------------------------------- |
| `LatitudeField`  | Field name containing latitude values (in single quotes)  |
| `LongitudeField` | Field name containing longitude values (in single quotes) |
| `ConnectPoints`  | `true` to connect dots with a line, `false` for pins only |
| `Width`          | Map width in pixels                                       |
| `Height`         | Map height in pixels                                      |
| `MapType`        | 0 = Road, 1 = Aerial, 2 = Mixed                           |

Example: `<%GlobalMap('latitude', 'longitude', true, 300, 300, 0)%>`

![](/files/QbT7lbwDw9vlUddH6BNJ)

With a relationship:

![](/files/df8xCSri9BtKuffIIFsU)

### Chart()

Display a bar chart or pie chart from aggregated data:

```
<%Chart(GraphType, AggregationFunction, LabelField, DisplayValues, Width, Height)%>
```

| Parameter             | Description                                              |
| --------------------- | -------------------------------------------------------- |
| `GraphType`           | `'Pie'`, `'Pie3D'`, or `'Bar'`                           |
| `AggregationFunction` | A function such as `Count()` or `Sum(field)`             |
| `LabelField`          | Field name used to group and label results               |
| `DisplayValues`       | `true` to show values on the chart, `false` to hide them |
| `Width`               | Chart width in pixels                                    |
| `Height`              | Chart height in pixels                                   |

**Examples:**

`<%Chart('Bar', 'Count()', 'CustomerName', true, 500, 300)%>`

![](/files/UYFTAdhjuiq4SolfMDR8)

`<%Chart('Pie3d', 'Sum(Price)', 'ReparationType', false, 600, 300)%>`

![](/files/VnUahNdZVEKV1w5FDsS9)

`<%Chart('Pie', 'Count()', 'TypeReparation', true, 600, 300)%>`

![](/files/L9LbeIFStLOwTJM7FtHQ)

## Special Cases

### List fields

#### Isolate a level in a tree list

By default: `<%ListSystemName%>`

For a tree list with result A,B,C:

| Desired value    | Syntax                                                             |
| ---------------- | ------------------------------------------------------------------ |
| First level (A)  | `<%[ListSystemName.FirstLevel]%>` or `<%[ListSystemName.Level1]%>` |
| Second level (B) | `<%[ListSystemName.Level2]%>`                                      |
| Last level (C)   | `<%[ListSystemName.LastLevel]%>` or `<%[ListSystemName.LevelN]%>`  |

For a multiple-choice list (no tree structure) with result A | B | C:

| Desired value   | Syntax                       |
| --------------- | ---------------------------- |
| First item (A)  | `<%[ListSystemName.Item1]%>` |
| Second item (B) | `<%[ListSystemName.Item2]%>` |
| Nth item        | `<%[ListSystemName.ItemN]%>` |

For a multiple-choice list with tree structure (result: A,A1 | B,B1 | C,C1):

| Desired value           | Syntax                                      |
| ----------------------- | ------------------------------------------- |
| Full first item         | `<%[ListSystemName.Item1]%>` → A,A1         |
| First item, first level | `<%[ListSystemName.Item1.Level1]%>` → A     |
| Second item, last level | `<%[ListSystemName.Item2.LastLevel]%>` → B1 |

{% hint style="info" %}
To obtain both the list element and its parent, use a formula field.
{% endhint %}

#### List side values

A list value can carry subvalues (External ID, URL, image, color):

| Value                        | Syntax                                                    |
| ---------------------------- | --------------------------------------------------------- |
| External ID                  | `<%[ListSystemName.ExternalId]%>`                         |
| URL                          | `<%[ListSystemName.Url]%>`                                |
| Image (size 50px)            | `<%Img([ListSystemName.Image], 50)%>`                     |
| Color code                   | `<%[ListSystemName.Color]%>`                              |
| Value colored with its color | `<%TextColor([ListSystemName], [ListSystemName.Color])%>` |

Item-specific subvalue syntax (e.g. for the first item):

* `<%[ListSystemName.Item1.ExternalId]%>`
* `<%[ListSystemName.Item1.Url]%>`
* `<%[ListSystemName.Item1.Image]%>`
* `<%[ListSystemName.Item1.Color]%>`

{% hint style="warning" %}
The "List element" field type is not persistent — it cannot be used in a report. External IDs must not contain special characters (e.g. underscore `_`).
{% endhint %}

### User fields

#### All users in a field

In these examples, the system name of the field is `UserFieldName`:

| Information                           | Syntax                          |
| ------------------------------------- | ------------------------------- |
| All first names (comma-separated)     | `<%[UserFieldName.FirstName]%>` |
| All last names (comma-separated)      | `<%[UserFieldName.LastName]%>`  |
| All full names (comma-separated)      | `<%[UserFieldName.FullName]%>`  |
| All emails (comma-separated)          | `<%[UserFieldName.Email]%>`     |
| Full name or email (per form setting) | `<%[UserFieldName]%>`           |

#### Specific user in a field

| Information          | Syntax                               |
| -------------------- | ------------------------------------ |
| Email of user 1      | `<%[UserFieldName.User1.Email]%>`    |
| Last name of user 4  | `<%[UserFieldName.User4.LastName]%>` |
| First name of user 2 | `<%UserFieldName.User2.FirstName%>`  |

### Workflow status

| Information                      | Syntax                                  |
| -------------------------------- | --------------------------------------- |
| Current state (default language) | `<%[Submission.CurrentState]%>`         |
| Is it a final state?             | `<%[Submission.CurrentState.End]%>`     |
| State color                      | `<%[Submission.CurrentState.Color]%>`   |
| State name in French             | `<%[Submission.CurrentState.Name.FR]%>` |
| State name in English            | `<%[Submission.CurrentState.Name.EN]%>` |

Display the current state with its associated color:

```
<%Display(Exists('Submission.CurrentState'))%><%TextColor('█',[Submission.CurrentState.Color])%><%[Submission.CurrentState]%><%End%>
```

### Duration field

It is not possible to retrieve a duration field value directly in a Word report. Use a JavaScript formula field to convert it first, then include that formula field in the report.

Example JavaScript formula to retrieve the duration value:

```javascript
var given_seconds = items['SYSTEM_NAME_OF_DURATION_FIELD'] / 1000;
var dateObj = new Date(given_seconds * 1000);
var hours = dateObj.getUTCHours();
var minutes = dateObj.getUTCMinutes();
var seconds = dateObj.getSeconds();
var timeString = hours.toString().padStart(2, '0') + 'h'
              + minutes.toString().padStart(2, '0') + 'min'
              + seconds.toString().padStart(2, '0');
return timeString;
```

### Signature field

Use `<%Image()%>` with the signature field system name and the desired size:

```
<%Image(Sign1, 80)%>
```

### File field — FileImage() and FilePageCount()

#### FileImage()

Insert one or all pages of a PDF attached to a file field as images in the report:

```
<%FileImage('FileFieldName', PageNumber, Width, Dpi)%>
```

| Parameter       | Type           | Description                                     | Default |
| --------------- | -------------- | ----------------------------------------------- | ------- |
| `FileFieldName` | string         | System name of the file field                   | —       |
| `PageNumber`    | int or `'All'` | Page number to insert, or `'All'` for all pages | `'All'` |
| `Width`         | int            | Width of the preview image in pixels            | 585     |
| `Dpi`           | int            | Display resolution (dots per inch)              | 92      |

**Examples:**

```
<%FileImage('File')%>
→ Inserts the first page of the first attached PDF.

<%FileImage('File', 2)%>
→ Inserts the second page of the first attached PDF.

<%FileImage('File', 1, 300)%>
→ Inserts the first page, resized to 300px wide.

<%FileImage('File', 'All', 650, 120)%>
→ Inserts all pages, 650px wide at 120 DPI.
```

To display the second attached document:

```
<%FileImage('File#2')%>
```

{% hint style="warning" %}
Only PDF files are supported. Inserting multiple pages may increase report generation time. In listing-type reports, keep the total document size reasonable.
{% endhint %}

#### FilePageCount()

Returns the total number of pages in an attached PDF:

```
<%FilePageCount('FileFieldName')%>
```

#### Other file-related expressions

| Expression                     | Description                     |
| ------------------------------ | ------------------------------- |
| `<%[File.Count]%>`             | Total number of attached files  |
| `<%[File]%>` or `<%[File#1]%>` | Access the first attached file  |
| `<%[File#2]%>`                 | Access the second attached file |
| `<%[File#1Name]%>`             | Name of the first file          |
| `<%[File#1MimeType]%>`         | MIME type of the first file     |
| `<%[File#1Extension]%>`        | File extension                  |
| `<%[File#1Size]%>`             | File size in bytes              |

{% hint style="info" %}
Looking for complete end-to-end examples? See the [Syntax & Functions Cookbook](/user-documentation/build/building-the-reporting/syntax-functions-cookbook.md) for real-world report templates combining multiple functions.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://www.center.daxium-air.com/user-documentation/build/building-the-reporting/syntax-functions.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.