Breadcrumbs

Advanced Format Settings (JSON)

If none of the available format presets described in Column Formatting match your use case, you can define your own format and combine multiple format options in the Advanced Format Settings field:

Active Advanced Format Settings field

Overview of Value Types

Number

Formats numeric values. Supports standard numbers, percentages, scientific notation, and fractions.

Currency

Formats numeric values as currency with currency symbols and locale-specific formatting.

Date

Formats date and time values using custom date patterns, timezones, and hour cycles.

Text

Applies text transformations like uppercase, lowercase, title case, or trimming.

Boolean

Displays boolean values as custom text (e.g., "Yes/No", "Active/Inactive").

Common Options

locale (string, optional)

Specifies the locale for formatting using BCP47 format (e.g., "en-US", "de-DE", "fr-FR").

Example:

{"locale": "de-DE"}

Default: JVM default locale of the server where the code executes

Note: Since PocketQuery runs as a Forge app in the cloud, this refers to the default locale of the PocketQuery server infrastructure, not the Confluence instance locale. It is recommended to explicitly specify the locale option to ensure consistent formatting across different server environments.

Number Formatting

Format type: Number

Standard Number Formatting

Used when none of the special options scientificExponentDigits, fractionDenominator, or percent is present.

Key

Description

Default

Available values

Example

Result

decimals
(integer, optional)

Number of decimal places to display.

2

 


{"decimals": 2, "locale": "en-US"}

1234.566 → 1,234.56

useThousandsSeparator
(boolean, optional)

Whether to use thousands separators (commas or periods depending on locale).

true

  • true

  • false

 
{"decimals": 0, "useThousandsSeparator": false, "locale": "en-US"}

1234 → 1234
(no separator)

negativeStyle
(string, optional)

How to display negative numbers.

minus

  • minus - Uses minus sign

  • parentheses - Uses parentheses

 
{"negativeStyle": "parentheses", "locale": "en-US"}

-1234.56 → (1,234.56)

roundingMode
(string, optional)

Rounding strategy for numbers.

halfUp

  • halfUp - Round half up

  • halfDown - Round half down

  • halfEven - Round half to even (banker's rounding)

  • up - Round up

  • down - Round down

  • ceiling - Round toward positive infinity

  • floor - Round toward negative infinity

 
{"decimals": 0, "roundingMode": "halfUp", "locale": "en-US"}

1234.5 → 1235

Percentage Formatting

Activated when the percent key is present (the actual value does not matter; the presence of the key is enough).

Key

Description

Default

Available values

Example

Result

percent (boolean)

When true, formats the value as a percentage (multiplies by 100 and adds % symbol).

false

  • true

  • false

 
{"percent": true, "decimals": 1, "locale": "en-US"}

0.1234 → 12.3%

Note: When percent is true, the default for decimals is 0, and useThousandsSeparator defaults to false.

Scientific Notation Formatting

Activated when the scientificExponentDigits key is present.

Key

Description

Default

Example

Result

scientificExponentDigits
(integer, optional)

Number of digits in the exponent when using scientific notation.

2

 
{"scientificExponentDigits": 2, "decimals": 2, "locale": "en-US"}

12345.67 → 1.23E+04

Note: When scientificExponentDigits is present, the value is formatted in scientific notation.

Fraction Formatting

Activated when the fractionDenominator key is present (and scientificExponentDigits is not present).

Key

Description

Default

Example

Result

fractionDenominator
(integer, optional)

Common denominator for fraction representation (e.g., 2, 4, 8, 16, 100).

100

 
{"fractionDenominator": 8}

1.125 → 9/8

Note: When fractionDenominator is present, the value is formatted as a fraction. The fraction is automatically reduced to its simplest form.

Priority of Number Sub-Modes

If you combine several special options, the formatter chooses exactly one mode in the following order:

  1. scientificExponentDigits → scientific notation

  2. Otherwise fractionDenominator → fraction

  3. Otherwise percent → percentage

  4. Otherwise → standard number

Example:

{"scientificExponentDigits": 2, "percent": true}

→ Scientific notation is used; the percent flag is ignored.

Complete Examples


Advanced Format Settings

Input

Output

Number with Custom Formatting

 
{"decimals": 2, "useThousandsSeparator": true, "negativeStyle": "parentheses", "roundingMode": "halfUp", "locale": "en-US"}

1234.567

1,234.57

Percentage

 
{"percent": true, "decimals": 1, "locale": "en-US"}

0.1234

12.3

Scientific Notation

 
{"scientificExponentDigits": 2, "decimals": 2, "locale": "en-US"}

12345.67

1.23E+04

Fraction

 
{"fractionDenominator": 8}

1.125

9/8

Currency Formatting

Format type: Currency

Key

Description

Default

Available values

Example

Result

currencyCode
(string, optional)

ISO 4217 currency code (e.g., "USD", "EUR", "GBP", "JPY").

Currency from locale, or "USD" if locale doesn't have a currency

 


{"currencyCode": "EUR", "locale": "de-DE"}

1234.56 → 1.234,56 €

currencySymbol (string, optional)

Override the default currency symbol.

 



{"currencyCode": "USD", "currencySymbol": "€", "locale": "en-US"}

1234.56 → €1,234.56

decimals (integer, optional)

Number of decimal places.

2

 


{"currencyCode": "USD", "decimals": 0}

1234.56 → $1,235

useThousandsSeparator (boolean, optional)

Whether to use thousands separators
(commas or periods depending on locale).

true

  • true

  • false

 
{"decimals": 0, "useThousandsSeparator": false, "locale": "en-US"}

1234 → 1234
(no separator)

negativeStyle
(string, optional)

How to display negative values.

minus

  • minus - Uses minus sign

  • parentheses - Uses parentheses

 
{"currencyCode": "USD", "negativeStyle": "parentheses"}

-1234.56 → ($1,234.56)

showSpaceBeforeCurrency
(boolean, optional)

When true, adds a space between the currency symbol and the amount (accounting format).

false

  • true

  • false

 
{"currencyCode": "EUR", "showSpaceBeforeCurrency": true}

1234.56 → 1.234,56 €

roundingMode (string, optional)

Rounding strategy (same options as Number formatting).

 



{"currencyCode": "USD", "decimals": 0, "roundingMode": "halfUp"}

1234.5 → 1235

Complete Examples


Advanced Format Settings

Input

Output

Currency with Accounting Format

 
{"currencyCode": "EUR", "currencySymbol": "€", "showSpaceBeforeCurrency": true, "decimals": 2, "locale": "de-DE"}

1234.56

1.234,56 €

Date/Time Formatting

Format type: Date

Key

Description

Default

Available values

Example

Result

dateFormat
(string)

Required for date formatting!

Custom date format pattern using Java SimpleDateFormat syntax.

Common Patterns:

  • yyyy - 4-digit year

  • MM - 2-digit month (01-12)

  • dd - 2-digit day (01-31)

  • HH - 2-digit hour in 24-hour format (00-23)

  • hh - 2-digit hour in 12-hour format (01-12)

  • mm - 2-digit minute (00-59)

  • ss - 2-digit second (00-59)

  • a - AM/PM marker

 



{"dateFormat": "yyyy-MM-dd"}

{"dateFormat": "dd.MM.yyyy"}

{"dateFormat": "yyyy-MM-dd HH:mm:ss"}

1) 2024-01-15
2) 15.01.2024
3) 2024-01-15 14:30:45

timezone
(string, optional)

Timezone for date/time formatting. Can be:

  • Timezone ID (e.g., "America/New_York", "Europe/London")

  • UTC offset (e.g., "UTC", "GMT+5:30")

UTC

 


{"dateFormat": "yyyy-MM-dd HH:mm:ss", "timezone": "America/New_York"}

Date/time converted to New York timezone

hourCycle
(string, optional)

Hour format cycle.

  • When 12h is specified:

    • Converts H → h in the date format

    • Automatically adds AM/PM marker (a) if not already present

  • When 24h is specified:

    • Converts h → H in the date format

    • Removes AM/PM marker if present

Note: The AM/PM marker is automatically added when using 12h if the format contains time components (h, m, or s) and no a marker is present.

No conversion (uses format as specified)

  • 12h - 12-hour format (1-12) with AM/PM marker

  • 24h - 24-hour format (0-23)

With 12h:

{"dateFormat": "yyyy-MM-dd HH:mm:ss", "hourCycle": "12h", "locale": "en-US"}

With 24h:

{"dateFormat": "yyyy-MM-dd hh:mm:ss a", "hourCycle": "24h"}


With 12h: 2024-01-15 14:30:45 → 2024-01-15 02:30:45 PM

With 24h: 2024-01-15 02:30:45 PM → 2024-01-15 14:30:45 (AM/PM removed)

Note: If dateFormat is not provided or empty, the value is returned as-is.

Complete Examples


Advanced Format Settings

Input

Output

Date with Timezone and 12-Hour Format

 
{"dateFormat": "yyyy-MM-dd HH:mm:ss", "timezone": "America/New_York", "hourCycle": "12h", "locale": "en-US"}

1705327845000 (timestamp)

2024-01-15 02:30:45 PM

Text Formatting

Format type: Text

Key

Description

Default

Available values

Example

Result

textTransform (string, optional)

Text transformation to apply.

none

  • none - No transformation

  • upper - Convert to uppercase

  • lower - Convert to lowercase

  • title - Convert to title case (first letter of each word capitalized)

  • trim - Remove leading and trailing whitespace

Uppercase:

{"textTransform": "upper"}

Lowercase:

{"textTransform": "lower"}

Title Case:

{"textTransform": "title", "locale": "en-US"}

Trim:

{"textTransform": "trim"}

Uppercase: "hello world" → "HELLO WORLD"

Lowercase: "HELLO WORLD" → "hello world"

Title Case: "hello world" → "Hello World"

Trim: " hello world " → "hello world"

Note: Multiple transformations can be combined. The order is: trim → upper/lower/title.

Boolean Formatting

Format type: Boolean

Key

Description

Default

Example

Result

booleanTrue
(string, optional)

Text to display for true values.

"Yes"

 
{"booleanTrue": "Active", "booleanFalse": "Inactive"}

true → "Active", false → "Inactive"

booleanFalse
(string, optional)

Text to display for false values.

"No"

 
{"booleanTrue": "Enabled", "booleanFalse": "Disabled"}

true → "Enabled", false → "Disabled"

Note: Boolean values can be provided as actual booleans or as strings ("true", "false"). Both are handled correctly.

General Behavior and Notes

  1. Combining Options: Most options can be combined. For example, you can use decimals, useThousandsSeparator, and negativeStyle together for number formatting.

  2. Empty or missing field input: If Advanced Format Settings is empty or "{}", no formatting is applied. null values become an empty string; all other values are shown as they come from the query.

  3. Invalid values: If a value does not match the expected type (for example, text in a number column), the original value is shown instead of failing.

  4. Per-cell fallback: If formatting for a single cell fails for any reason, that cell falls back to the original value, while other cells in the same column are still formatted.

  5. Locale impact: All number, currency, date, and text transformations that depend on the locale honor the locale option when provided; otherwise they use the default locale of the PocketQuery server.

If Custom is chosen as Format Preset, but the Advanced Format Settings field is empty or {}, the value is returned as-is without any formatting.

Quick Reference

Format Type

Key Options

Number

locale, decimals, useThousandsSeparator, negativeStyle, roundingMode

Number (Percent)

all from Number, percent

Number (Scientific)

all from Number, scientificExponentDigits

Number (Fraction)

all from Number, fractionDenominator

Currency

locale, currencyCode, currencySymbol, decimals, showSpaceBeforeCurrency, negativeStyle, roundingMode

Date

locale, dateFormat, timezone, hourCycle

Text

locale, textTransform

Boolean

booleanTrue, booleanFalse