Dynamically populate the HTML Dropdown template using pocketquery plugin in confluence

[aakash salvi]

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 (Scandio).

[pmayne]

After looking at the support desk tips I found out that I needed to add the following code as part of the form

<form method="get" class="aui pq-dynamic-parameter-form" action="">
## store GET parameters that are not PQ specific in hidden fields so they don't get lost
#foreach($entry in $req.getParameterMap().entrySet())
#if(!$entry.key.startsWith("pq_") && $PocketQuery.getValueFromMapEntry($entry))
<input type="hidden" name="$entry.key" value="$PocketQuery.getValueFromMapEntry($entry)" />
#end
#end

<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>

It is now remembering the pageid to submit the parameter values

thanks

[Sven Schatter _Lively Apps_]

Hi @pmayne 

very cool that you figured it out yourself and also provided the solution here for others to find! :)

Best regards,
Sven

EDIT: There's now also an example in our docs that does something very similar https://help.livelyapps.com/documentation/display/PQDOC/Query+depending+on+multiple+select+boxes

[Sven Schatter _Lively Apps_]

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:

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.

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)

[pmayne]

Hi I have managed to replicate your way with our confluence page, however after initial success it is no longer working. it looks like the change button when used can not find the page anymore. do I need to add the pageid of the current page as part of the submit form?

[Sven Schatter _Lively Apps_]

Hi @pmayne 

could you raise a support ticket at our service desk for PocketQuery and tell me exactly what you are trying to achieve? Please also include your current template and query (feel free to censor sensitive data).

Best regards,
Sven

[Andrey Khaneev _StiltSoft_]

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.