Dynamically populate the HTML Dropdown template using pocketquery plugin in confluence Edited

Hi i am new to pocketquery and till now i just know how to write and MySQL query to read the MySql db and populate the table.I want to populate a dropdown list with the table data.

The Query to populate data is quite simple 

' select distinct AccountId from Accounts ' I want the values returned by this query to be the options of select html tag (dropdown).

After the dropdown is populated i would like to select one one value and based on the selected value it will fire a query to MySql DB with selected value as input parameter the 2nd Query is something like below 

'select AccountId,AccountName from Accounts where AccountId=the input parameter selected in dropdown'.

Please help me @Felix Grund.

2 answers

This widget could not be displayed.

Hi, 

I can suggest you a workaround: you can pull the whole table with all AccountIds and then filter it with Table Filter dropdown filter.

This widget could not be displayed.

Hey Aaksh,

sorry that you didn't receive an answer yet. Felix is currently very busy and we have basically moved our support to our service desk for PocketQuery.

---

Regarding your question:

To achieve what you want you basically have to use 2 PocketQuery Macros. One for building the dropdown menu and then the other for using the selected value to run a new query.

So let's start by creating the template for the first Macro which will build your dropdown list.

For the first query i'm just going to use a very simple example and get all the spacekeys from my Confluence database with this query:

SELECT SPACEKEY FROM spaces;

We now want to build a dropdown menu from the results, which also passes the results to the other Macro. For this we will adjust the "change params"-template a little:

<form method="get" class="aui pq-dynamic-parameter-form" action="">
<button type="submit" class="pq-change-button aui-button aui-style aui-button-primary">Change</button>

<div>
<label for="pq_spacekey">spacekey</label>
<select id="pocketquery-select-example" name="pq_spacekey">
#foreach ($row in $result)
<option value="$row.SPACEKEY">$row.SPACEKEY</option>
#end
</select>
</div>
</form>

This is just a very simple standard HTML dropdown menu. Remember: If you want it to look more fancy you can always use the AUI styling elements. So this is what you should get:

Unbenannt.png

Now we need to build the second query. Again, i will use a very simple example just to show that it works. This is where you need to include a dynamic parameter. (make sure the NAME of the dynamic parameter matches the pq_NAME in the template of the first query, or else this won't work)

SELECT * FROM confluence.spaces
WHERE SPACEKEY = :spacekey

Now simply insert the macro below your first macro and make sure that you enable dynamic parameters in the macro parameters.

You can now choose something from the dropdown menu and the second query will use it to get its results.

result.PNG

If you need any further help, please don't hesitate to raise a ticket at our service desk.

---

Thank you for using PocketQuery!

Best regards,
Sven (Scandio)

Suggest an answer

Log in or Sign up to answer
Atlassian Summit 2018

Meet the community IRL

Atlassian Summit is an excellent opportunity for in-person support, training, and networking.

Learn more
Community showcase
Published Tuesday in Confluence

Add-on evaluation with confluence templates

Atlassian market place contains number of Apps/Addons which improves the capability of out of the box Atlassian products. It is good to follow a plugin evaluation process before install add-ons. So t...

107 views 12 6
Read article

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you