External datasource as placeholder options (PRO)

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;

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

  SQL

  SELECT CONCAT(project_name, ': ', project_id) As Text, project_id
  FROM Projects;

• Filter the results

  SQL

  SELECT project_name, project_id
  FROM Projects
  WHERE ...;

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.

PTC Placeholder Macro | Image

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)

Replace Placeholder dialog | Image

Final Result

The placeholder is replaced with the id of the query.

Example

Second column was: project_id

Page with replaced placeholder | Image