Step 1: Configure your datasource

Only Confluence administrators can add an external datasource in the Datasource Settings

Step 2: Add a query to select two columns

Multiple queries can be added in the Datasource Settings.
A query needs to select two columns:

  1. Text: the first column will be used to help the user choose the correct replacement (e.g. project_name)

  2. Replacement: The second column will be used as the replacement (e.g. project_id)

Example:

Name: Select Project
Query:

SELECT project_name, project_id
FROM Projects;
SQL

Later the placeholder will be replaced by the project_id, but in the replace dialog, the user will only see the project_name. This is to make it more clear what option to choose.

More advanced SQL queries are also possible. For example:

  • Combine multiple columns with CONCAT

    SELECT CONCAT(project_name, ': ', project_id) As Text, project_id
    FROM Projects;
    SQL
  • Filter the results

    SELECT project_name, project_id
    FROM Projects
    WHERE ...;
    SQL

Step 3: Create a PTC template with a datapicker placeholder

  1. Add a Placeholder macro on the template page and select the type “Datapicker”

  2. Select a query in the Placeholder macro (e.g “Select a project”)

  3. Put your placeholder anywhere on the page or sub page

Step 4: Instantiate your page

In the replace dialog, the datapicker placeholder will appear with a single select list. The first column of your query will be displayed. Select a value from the list or type in any custom value.

Example: First column was a combination of project name and project id:

CONCAT(project_name, ' ', project_id)
CODE

Final Result

The placeholder is replaced with the id of the query.

Example: Second column was: project_id