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:
Text: the first column will be used to help the user choose the correct replacement (e.g. project_name)
Replacement: The second column will be used as the replacement (e.g. project_id)
Name: Select Project
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
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
Add a Placeholder macro on the template page and select the type “Datapicker”
Select a query in the Placeholder macro (e.g “Select a project”)
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)
The placeholder is replaced with the id of the query.
Example: Second column was: project_id