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

[Joe_D_37]

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?

[Sven Schatter _Lively Apps_]

Okay, so you want to do the following?

• Upload an attachment to a Confluence page.
• In one of your querys call $PocketQuery.executeQuery("getAttachment") which calls the query "getAttachment" which gets the attachment and parses it for a parameter. (Depending on the attachment you might need a converter for this)
• Use the result of $PocketQuery.executeQuery("getAttachment") in your current query as a parameter.

Yes, this is absolutely possible. But when using executeQuery() / renderPocketQueryMacro() you should always keep in mind that you are executung extra queries, so it might be a good Idea to activate dynamic load for your macro.

[Joe_D_37]

Essentially, you outlined it to a T. What I need to do is have users be able to upload either a .csv file or a .xlsx file to the confluence page with a specific name. Within their sheet, there is a column that will contain the parameter(s) listed out since I am limited to the number of inputs when doing just a dynamic parameter as a String.

When the query runs, it looks at an Oracle database and bumps up against the spreadsheet the users have uploaded to return the results of those values in the spreadsheet. Like a VLOOKUP as an example.

I have not been able to find any details around this type of a template though in the community or in the documentation. I am great at SQL but coding out a template, not so much. Any direction is much appreciated.

[Sven Schatter _Lively Apps_]

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:

## 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.
<div>Calling "oracle_query" with parameter: "$paramFromCsv"</div>

$PocketQuery.renderPocketQueryMacro("oracle_query", {
  "page": $page,
  "parameters": { "your_parameter": $paramFromCsv }
})

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. :)

[Joe_D_37]

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?

[Sven Schatter _Lively Apps_]

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! :)