Searching for a specified pattern in a column

Use case:

You need to find any values of the 'Customer Name' column that have "é" in any position.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following SQL query:

    SELECT *
FROM T1
WHERE 'Customer Name' LIKE "%é%"
    SQL
  6. Click Next.
  7. Save the macro and the page.

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"

You can use FORMATWIKI function for the purposes of cell formatting.

Number formatting

Use case:

You have a table or a macro outputting a table. 

You need to change the data format of the column containing numbers to add a dollar sign, a thousands separator into values and remove decimals.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table within the macro body.
  3. Select the macro and click Edit.
  4. Switch to the SQL query tab.

  5. Enter the following SQL query:

    SELECT 'Company',
"$ "+FORMATNUMBER('Profit per month') as 'Profit'
FROM T*
    SQL
  6. Click Next.
  7. Define the table settings and view options. 
  8. Save the macro and the page.

FORMATNUMBER function allows you to format numbers according to the settings in the Table Transformer macro.

You can use FORMATWIKI function for the purposes of cell formatting.

Getting local time of different time zones

Use case:

You need to get  time based on the time zones.

Solution:

  1. Switch the page to the edit mode.

  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.

  3. Select the macro and click Edit.

  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT *,
FORMATDATE(DATEADD(hour, T1.'GMT' + (T1.'Created'::Date->getTimezoneOffset() / 60), T1.'Created'))
AS 'My Time',
FORMATDATE(DATEADD(hour, T1.'GMT' + ("today"::Date->getTimezoneOffset() / 60), NOW()))
AS 'Current Time'
FROM T1
    SQL

  6. Click Next.

  7. Define the date format.

  8. Save the macro and the page.

The getTimezoneOffset() method returns the time difference between UTC time and local time, in minutes.

The NOW() function returns the current date and time.

The DATEADD() function adds a time/date interval to a date and then returns the date.

The FORMATDATE() function converts time/dates to the specified in the Table Transformer macro settings date format.

Using @currentUser and @pageTitle variables

Use case:

You have a table with the list of people responsible for publishing documentation pages.

You need to do the following:

  • find the current Confluence username (i.e. who is logged in) with @currentUser variable in the Responsible column
  • color it in blue and the others in green
  • add the current page name (i.e. where the Confluence logged in user is now) with the @pageTitle variable to all the existing page names in the table.

Solution:

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT
FORMATWIKI("{color:" + IF(T1.'Responsible'= @CURRENTUSER, "green", "blue") +"}" + T1.'Responsible' + "{color}")
AS 'Responsible', 
"@pageTitle - " + T1.'Page' 
AS '@pageTitle'
FROM T1
    SQL
  6. Click Next. 

  7. Save the macro and the page.

@currentUser, @pageTitle variables are case-insensitive

Extracting uneditable macros content

Use case:

You need to derive the content from a column of another macro output used as a source table (e.g. Jira Issues macro, Page Properties Report, Task Report, etc.).

Solution:

  1. Switch the page to the edit mode.

  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.

  3. Select the macro and click Edit.

  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query pattern:

    SELECT SUBSTRING_VIEW(T1.'Key',5,4) as 'OTS Key',
 T1.'Assignee',T1.'T',T1.'Created' FROM T1

/* SYNTAX - SUBSTRING_VIEW('YOUR COLUMN NAME', STARTING POSITION NUMBER, THE NUMBER OF CHARACTERS TO DERIVE) */
    SQL

  6. Click Next. 

  7. Save the macro and the page.

The SUBSTRING_VIEW function allows to not only extract the content of a column, but also retain the original formatting of the source(cell) so that it could be applied to the altered output, including links retaining (see screenshots). 

Replacing uneditable macros content

Use case:

You need to adjust the content in a column of another macro output used as a source table (e.g. Jira Issues macro, Page Properties Report, Task Report, etc.).

Solution:

  1. Switch the page to the edit mode.

  2. Insert the Table Transformer macro and paste the table or the macros outputting tables within the macro body.

  3. Select the macro and click Edit.

  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query pattern:

    SELECT *, REPLACE_VIEW(T1.'Status',"Done","Resolved") as 'Status' FROM T1 

/* SYNTAX - REPLACE_VIEW('YOUR COLUMN NAME', "SOURCE DATA TO REPLACE","NEW DATA REPLACING THE OLD") */
    SQL

  6. Click Next. 

  7. Save the macro and the page.

The REPLACE_VIEW function allows to not only change the content of a column, but also retain the original formatting of the source(cell) so that it could be applied to the altered output (see screenshots).