Table Filter, Charts & Spreadsheets for Confluence

https://docs.stiltsoft.com/tfac/
Admin
Table Filter, Charts & Spreadsheets for Confluence enables users to filter, aggregate, and visualize...
Tokens:584,509
Snippets:5,067
Trust Score:9.8
Update:1 week ago
Show doc for...
Tokens:
Raw
### SQL query example with conversions and JavaScript methods

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html

Example SQL query using conversions and JavaScript methods on columns from table T*.

```sql
SELECT 
T1.'String'->length AS 'Length',
T1.'String'->split("b")->0 AS 'Splitting',
T1.'Number'->toFixed(1) AS 'Rounding to tenth',
T1.'Date'::Date->getFullYear() AS 'Full year'
FROM T*
```

--------------------------------

### BEGIN

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Starts code block for WHILE and IF statements.

```APIDOC
## BEGIN

### Description
Starts code block for WHILE and IF statements.

### Type
LOGIC LOOP

### Syntax
BEGIN ... END

### Example
IF condition THEN BEGIN ... END
```

--------------------------------

### LIKE pattern examples for matching values

Source: https://docs.stiltsoft.com/tfac/dc-server/searching-for-a-specified-pattern-in-a-column-188621395.html?scroll-versions%3Avariant-name=cloud

These examples show how to use the LIKE operator with different wildcard patterns to match values in a column. They are useful for understanding how to construct patterns for various search conditions.

```text
LIKE "a%" - Finds any values that start with "a"  
LIKE "%a" - Finds any values that end with "a"  
LIKE "_r%" - Finds any values that have "r" in the second position  
LIKE "a_%" - Finds any values that start with "a" and are at least 2 characters in length  
LIKE "a__%" - Finds any values that start with "a" and are at least 3 characters in length  
LIKE "B%s" - Finds any values that start with "B" and ends with "s"
```

--------------------------------

### LIKE pattern examples for SQL queries

Source: https://docs.stiltsoft.com/tfac/cloud/searching-for-a-specified-pattern-in-a-column-188621395.html

These are examples of SQL LIKE patterns that can be used in the Table Transformer macro to find values matching specific conditions, such as starting with 'a', ending with 'a', or having a character at a specific position. They are listed as reference patterns.

```sql
LIKE "a%" - Finds any values that start with "a"
```

```sql
LIKE "%a" - Finds any values that end with "a"
```

```sql
LIKE "_r%" - Finds any values that have "r" in the second position
```

```sql
LIKE "a_%" - Finds any values that start with "a" and are at least 2 characters in length
```

```sql
LIKE "a__%" - Finds any values that start with "a" and are at least 3 characters in length
```

```sql
LIKE "B%s" - Finds any values that start with "B" and ends with "s"
```

--------------------------------

### FORMATWIKI function syntax example

Source: https://docs.stiltsoft.com/tfac/dc-server/using-beautiful-colors-from-the-atlassian-design-system-203719047.html?scroll-versions%3Avariant-name=cloud

Shows the syntax for the FORMATWIKI function with a design token and fallback color. The token --ds-background-accent-red-subtlest is from the Atlassian Design System, and 'red' is the default Confluence color used if the token is unavailable.

```text
FORMATWIKI("{cell:background-color=var(--ds-background-accent-red-subtlest, red)}", T1.'Amount of Transactions', "{cell}")
```

--------------------------------

### SEQUENCE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Creates a grid of sequential numbers with configurable start and step values.

```APIDOC
## SEQUENCE

### Description
Returns a grid of sequential numbers starting at a specified start value and increasing by a specified step size. By default, the sequence starts at and increases by 1.

### Syntax
SEQUENCE(rows, [columns], [start], [step])

### Parameters
- **rows** (rangenumber) - Required - The number of rows in the function's resulting grid. Example: 1
- **columns** (rangenumber) - Optional - The number of columns in the function's resulting grid. If omitted, the result grid will have 1 column. Example: 1
- **start** (rangenumber) - Optional - The number, at which to start the sequence. If omitted, the sequence will start at 1. Example: 1
- **step** (rangenumber) - Optional - The amount each value in the sequence will differ by. If omitted, each value will differ by 1. Example: 1

### Return Value
Returns a grid of sequential numbers.
```

--------------------------------

### CHOOSE

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the CHOOSE function in Table Spreadsheet.

```APIDOC
## CHOOSE

### Description
Returns an element from a list of choices based on index.

### Parameters
- **index** (rangenumber) - Required - Which choice (of the up to 30 provided) to return. Example: 2
- **choice1** (rangeall) - Required - A potential value to return. Required. May be a reference to a cell or an individual value. Example: "A"
- **choice2** (rangeall) - Optional - Additional values among which to choose. Example: "B"

### Returns
An element from a list of choices based on index.
```

--------------------------------

### MID

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Extracts a substring from a string (starting at any position).

```APIDOC
## MID

### Description
Extracts a substring from a string (starting at any position).

### Syntax
MID(string, start, length)
```

--------------------------------

### AT

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

No description provided.

```APIDOC
## AT

### Description
No description provided.

### Type
TIME
```

--------------------------------

### SQL query to calculate workdays (basic)

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-the-number-of-workdays-in-a-period-of-time-188618361.html

Use this SQL query in the Table Transformer macro to count workdays between 'Start date' and 'End date' columns in table T1. It calculates total days, subtracts weekends, and handles edge cases for start day and remainder.

```SQL
SELECT *,
'Days' -
2 * (('Days' / 7)::integer) -
CASE
	WHEN 'Days' % 7 = 0 THEN 0
	WHEN 'Start date'::Date->getDay() = 0 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 = 7 THEN 1
	WHEN 'Start date'::Date->getDay() + 'Days' % 7 > 7 THEN 2
	ELSE 0
END AS 'Work days'
FROM
(SELECT *,
ROUND((T1.'End date' - T1.'Start date') / "1d") + 1 AS 'Days'
FROM T1)
```

--------------------------------

### SQL query for Table Transformer to get latest velocity

Source: https://docs.stiltsoft.com/tfac/cloud/calculating-velocity-42242152.html

Use this SQL query in the Table Transformer macro to retrieve the latest velocity value from the table. It selects the top 1 row ordered by sprint descending. This is used in the bonus section to get the most recent velocity.

```SQL
SELECT TOP 1 'Velocity' FROM T1 ORDER BY 'sprint' DESC
```

--------------------------------

### SQL query to format due dates as quarters using TEXT and FORMATWIKI

Source: https://docs.stiltsoft.com/tfac/cloud/formatting-as-strings-188621409.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro to add a 'Due Quarter' column that displays the quarter (Q2, Q3, Q4) based on the month in the 'Due Date' column. The TEXT function converts the date to a string for pattern matching with LIKE, and FORMATWIKI applies the quarter label. Ensure the table is pasted in the macro body and the query is entered in the SQL query tab.

```sql
SELECT *,
  CASE WHEN TEXT(T1.'Due Date') LIKE "%Jun%"
  THEN FORMATWIKI("*","Q2","*")
  WHEN TEXT(T1.'Due Date') LIKE "%Sep%"
  THEN FORMATWIKI("*","Q3","*")
  WHEN TEXT(T1.'Due Date') LIKE "%Nov%"
  THEN FORMATWIKI("*","Q4","*")
  END as 'Due Quarter'
FROM T1
```

--------------------------------

### SQL query to create clickable links with FORMATWIKI and CONCAT

Source: https://docs.stiltsoft.com/tfac/cloud/creating-clickable-links-188620180.html

Use this SQL query in the Table Transformer macro's custom transformation to create clickable links from multiple text pieces. It uses FORMATWIKI to format the link and CONCAT to combine the city and folder values into a URL. The query joins tables T1 and T2 on the 'Name' column.

```SQL
SELECT T1.'Name', T1.'Phone Number', T1.'City', FORMATWIKI("[" + CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') , "]") AS 'Workspace', FORMATWIKI("[The link|" , CONCAT("https://", T1.'City', ".mycompany.com/", T2.'Folder') , "]") AS 'Workspace 2' FROM T1 OUTER JOIN T2 ON T1.'Name' = T2.'Name'
```

--------------------------------

### WORKDAY

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Calculates the date after a number of working days from a specified start date.

```APIDOC
## WORKDAY

### Description
Calculates the date after a number of working days from a specified start date.

### Parameters
- **start_date** (rangeall) - Required - The date from which to begin counting. Example: DATE(1969
- **num_days** (rangenumber) - Required - The number of working days to advance from `start_date`. If negative, counts backwards. Example: 7
- **holidays** (rangeall) - Optional - A range or array constant containing the dates to consider holidays. Example: 16)

### Return Value
Number of working days from start date.
```

--------------------------------

### CEIL

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Gets the smallest integer which is greater than, or equal to, the specified numeric expression.

```APIDOC
## CEIL

### Description
Gets the smallest integer which is greater than, or equal to, the specified numeric expression.

### Type
MATH

### Syntax
CEIL(number)

### Parameters
- **number** (numeric) - Required - The numeric expression.

### Example
CEIL(4.2) returns 5
```

--------------------------------

### AND

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

```APIDOC
## AND

### Description
Returns true if all of the provided arguments are logically true, and false if any of the provided arguments are logically false.

### Parameters
- **logical_expression1** (rangeall) - Required - An expression or reference to a cell containing an expression that represents some logical value, i.e. `TRUE` or `FALSE`, or an expression that can be coerced to a logical value. Example: A2 = "foo"
- **logical_expression2** (rangeall) - Optional - More expressions that represent logical values. Example: A3 = "bar"

### Example
AND(A2 = "foo", A3 = "bar")
```

--------------------------------

### SQL query to set custom variables and format a project link

Source: https://docs.stiltsoft.com/tfac/cloud/custom-user-variables-203729910.html?scroll-versions%3Avariant-name=cloud

Use this SQL query in the Table Transformer macro's Custom transformation to set a URL prefix and a project value from the table, then format a clickable link. The result is a link like https://www.123.com/portal/TFCS-324.

```SQL
SET @var1 = "https://www.123.com/portal/";
SET @var2 = (SELECT T1.'Project' FROM T1);

SELECT FORMATWIKI("[Link|", @var1, @var2, "]") AS 'Project Link';
```

--------------------------------

### OFFSET

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=cloud

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

```APIDOC
## OFFSET

### Description
Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

### Parameters
- **cell_reference** (range) - Required - The starting point from which to count the offset rows and columns. Example: A2
- **offset_rows** (rangenumber) - Required - The number of rows to offset by. Example: 3
- **offset_columns** (rangenumber) - Required - The number of columns to offset by. Example: 4
- **height** (rangenumber) - Optional - The height of the range to return starting at the offset target. Example: 2
- **width** (rangenumber) - Optional - The width of the range to return starting at the offset target. Example: 2

### Return Value
A range reference offset relative to a cell.

### Argument Count
2 to 5 arguments.
```

--------------------------------

### FROM

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Specifies which table to select or delete data from.

```APIDOC
## FROM

### Description
Specifies which table to select or delete data from.

### Syntax
FROM table_name

### Parameters
- **table_name** (string) - Required - The name of the table.
```

--------------------------------

### OFFSET

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html?scroll-versions%3Avariant-name=dc-server

Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

```APIDOC
## OFFSET

### Description
Returns a range reference shifted a specified number of rows and columns from a starting cell reference.

### Function Signature
OFFSET(cell_reference, offset_rows, offset_columns, [height], [width])

### Parameters
- **cell_reference** (range) - Required - The starting point from which to count the offset rows and columns. Example: A2
- **offset_rows** (rangenumber) - Required - The number of rows to offset by. Example: 3
- **offset_columns** (rangenumber) - Required - The number of columns to offset by. Example: 4
- **height** (rangenumber) - Optional - The height of the range to return starting at the offset target. Example: 2
- **width** (rangenumber) - Optional - The width of the range to return starting at the offset target. Example: 2

### Return Value
A range reference offset relative to a cell.

### Example
=OFFSET(A2, 3, 4, 2, 2)
```

--------------------------------

### GET /rest/table-filter/1.0/service/export/csv

Source: https://docs.stiltsoft.com/tfac/cloud/how-to-export-macros-and-results-42239877.html?scroll-versions%3Avariant-name=dc-server

Exports a macro's table to CSV format via REST API. Requires page ID and optionally macro ID, macro name, macro number, and delimiter. Returns a CSV file.

```APIDOC
## GET /rest/table-filter/1.0/service/export/csv

### Description
Exports the table of a specified macro on a given page to CSV format. The endpoint accepts parameters to identify the macro and returns the CSV file.

### Method
GET

### Endpoint
/rest/table-filter/1.0/service/export/csv

### Parameters
#### Query Parameters
- **pageId** (integer) - Required - The page ID number where the required macro is located.
- **id** (string) - Optional - Unique identifier of the required macro on the page.
- **macroName** (string) - Optional - The name of the required macro on the page. Default is "table-filter". Available values: "table-filter", "pivot-table", "table-chart", "table-joiner" (for Table Transformer), "table-excerpt-include".
- **macroNumber** (integer) - Optional - The serial number of the required macro on the page. Default is 1.
- **delimiter** (string) - Optional - The output data delimiter. Default is comma.

### Request Example
http://my-confluence-instance.com/rest/table-filter/1.0/service/export/csv?pageId=1147061&id=69a5f057-38eb-4af5-989b-e9aba26ea1e7&macroName=table-joiner&macroNumber=2&delimiter=;

### Response
#### Success Response (200)
Returns the CSV file.

#### Error Response (404)
Returned if the macro is not found on the specified page.

```

--------------------------------

### GETDATE

Source: https://docs.stiltsoft.com/tfac/cloud/general-syntax-and-sql-functions-available-42246251.html?scroll-versions%3Avariant-name=cloud

Returns the current system date and time.

```APIDOC
## GETDATE

### Description
Returns the current system date and time.

### Syntax
GETDATE()
```

--------------------------------

### SMALL

Source: https://docs.stiltsoft.com/tfac/cloud/functions-available-in-table-spreadsheet-107742583.html

Documentation for the SMALL function.

```APIDOC
## SMALL

### Description
Returns the nth smallest element from a data set, where n is user-defined.

### Parameters
- **data** (range) - Required - The array or range containing the dataset to consider.
- **n** (rangenumber) - Required - The rank from smallest to largest of the element to return.

### Argument Count
[2,2]
```

--------------------------------

### REST API CSV export request URL

Source: https://docs.stiltsoft.com/tfac/cloud/how-to-export-macros-and-results-42239877.html?scroll-versions%3Avariant-name=dc-server

Example URL for exporting a table to CSV via the /rest/table-filter/1.0/service/export/csv endpoint. Requires pageId and optionally id, macroName, macroNumber, and delimiter. The user must have view permission on the page.

```http
http://my-confluence-instance.com/rest/table-filter/1.0/service/export/csv?pageId=1147061&id=69a5f057-38eb-4af5-989b-e9aba26ea1e7&macroName=table-joiner&macroNumber=2&delimiter=;
```

--------------------------------

### SQL query when column labels differ

Source: https://docs.stiltsoft.com/tfac/cloud/default-automatic-presets-42241562.html?scroll-versions%3Avariant-name=dc-server

Use this query to join tables when the matching columns have different labels. The example matches Table 1's 'Id' with Table 2's '#' and Table 3's 'N'.

```sql
SELECT
   T1.'Id',
   T1.'Full name',
   T2.'Login',
   T3.'Personal space',
   T3.'Create space',
   T3.'Confluence admin',
   T3.'System admin' 
FROM T1 
JOIN T2 ON T1.'Id' = T2.'#' 
JOIN T3 ON T1.'Id' = T3.'N'
```
Tokens:
Raw