Is it possible to use an attachment with a GetFile template to pass parameters in PocketQuery?

Question

I want to have users upload a file to a confluence page. Once uploaded, a template (if necessary) can call the GetFile process when executing a query and look at a column as a parameter to pass in that query. Is this able to be done in the system?

Former user · Answer

To raise a ticket at our ServiceDesk please create an account. The account has nothing to do with your Confluence or any of your licenses. It is just a free customer account at our ServiceDesk - you won't be charged or anything like that.

Once you have created an account please attach your existing query and template (feel free to censor anything sensitive) and describe exactly what you want to do.

Thank you! :)

Joe_D_37 · Answer

Thanks for the response Sven.

I tried this and it was not working. I think I might have explained this wrong. What I am trying to do is allow for the users to upload the attachment. Let's say that attachment contains just one column of records. Those records are acting as the parameter in my SQL query that I have setup in PocketQuery to look at an external database. When executed, I want that query to return the results based on what was in the attached .csv tab delimited text file.

I don't have a query set for GetFile or anything like that. All I have is the query for the external database. I have seen that we can use a template to pull in a file which is what your converter example looks like but that is a bit greek to me as I don't understand how these are all tying together.

You mentioned a ServiceDesk ticket. How might I log this or obtain a login if my enterprise already has Confluence licensing?

Former user · Answer

Okay, so, step by step - to do this you need two queries: * A "get_attachment" query, that fetches the CSV. * The "oracle_query" that takes a parameter. Im jumping over the Database/Query setup, since I guess you already have these configured. Since your "get_attachment" query gets you a CSV string and PocketQuery can't interpret that as a table (it only knows SQL tables and JSON) you need to write a Converter that parses the CSV and returns a JSON array. Parsing CSV is actually really easy, so your converter shouldn't be very complicated: // replace with whatever your file uses as a delimiter var csvDelimiter = ','; // specify your column names var columnNames = ['first column', 'second column', 'third column']; function lineToRow(line) { var cells = line.split(csvDelimiter); var rowObject = {}; columnNames.forEach(function(columnName, i) { rowObject[columnName] = cells[i]; }); return rowObject; } function convert(csv) { return csv.substring(1, csv.length-1).replace(/\r/g, '').split('\n').map(lineToRow); } Using this converter and the standard template, this file: foo,bar,baz auto,apfel,birne wolke,cloud,basto produced this table for me: Once you got that working you can now write a custom template for your "get_attachment" query that uses some value from the csv table as a parameter for your second query. The template could be as simple as this: "page": $page,
"parameters": { "your_parameter": $paramFromCsv }
})## Getting the parameter from the CSV table.
## This would be the "second column" of the second row -> "apfel".
#set($paramFromCsv = $result.get(1).get("second column"))

## Just for debugging so you can see if you got the correct cell.

Calling "oracle_query" with parameter: "$paramFromCsv"

$PocketQuery.renderPocketQueryMacro("oracle_query", {
Hope this helps! Once you understand how to correctly use $PocketQuery.executeQuery() and $PocketQuery.renderPocketQueryMacro() you'll have a hard time finding anything that you can't do with PocketQuery. :) If you want to talk about even more details, or send us some of your existing templates for concrete help, I recommend you raise a ticket at our ServiceDesk for PocketQuery. :)