Sharing my Power Automate template: Get Query results from the new Query API

Ashley Moose

As of 10/23/2024 this is officially out of date. Please refer to my newest post which offers a template using the new Query Connectors.

Description - This flow will take the results of a query and save them to OneDrive.

Required connections -

• Blackbaud SKY Add-ins
• OneDrive for Business

Skill level - Beginner/intermediate

Note: More of a beginner skill level to run this template but moving into more intermediate skills to manipulate the data from the query.

Configuration details - You need to identify the ID of the Query to run in the flow. There are two ways to identify the Query ID:

1. From the query link:

• Open the query into the Query editor
• Click Copy Link
• Paste the copied value somewhere (i.e. a notepad)
• Copy the value after queryid=.

https://host.nxt.blackbaud.com/query/renxt?queryid=252&envid=p-sadasdasdsadas-_qw&svcid=renxt

2. Using the Get Query List endpoint

Instructions

• Import the attached .zip file into Power Automate
• Modify the Recurrence trigger for whenever you want the query results to be pulled
• Add the Query ID to the Initialize variable queryID action
• Update the Initialize variable fileName action to include the name of the file you want saved to OneDrive. The file name should include the extension.
• Update the Initialize variable product action to reference your product.
  • Note: At the time of this posting, 02/29/2024, only RE is supported.
• If this is for FE, update the Initialize variable module action to reference the name of the module. If this is not for FE, this step can be skipped.
• Update the Create file action to point to where you want the file saved.

Note: For information on the valid values to use for Product and Module refer to the Get Query Execution Job endpoint documentation.

GetQueryTemplate_20240320163026.zip

Alex Wong

@Ashley Moose
you are quick, only just got the announcement on the public availability.

Ashley Moose

@Alex Wong I may have had a head's up about when the announcement was coming out ?

Alex Wong

@Ashley Moose
it's all cool. It's great for others who wasn't part of EAP that get some good info to start off with. Query API wasn't the easiest to use after all.

Great work ?

Alex Wong

@Ashley Moose
I have modified all my Query API flow after getting some error last week due to “status”=Failed or Throttled.

Here are my modification, and reason for this is so that the flow doesn't run “forever” (X number of minutes or X number of times wasting more API calls and not knowing it failed for too long).

• execute query
• do until status = Completed or Failed or Cancelled
  • there is no need to loop and check when any of these 3 statuses are reached
  • delay X seconds (min 30 seconds)
  • get job status
  • condition if status = Throttled
    • if yes, do an extra delay of X seconds
• once outside the do until loop condition on sas_uri being empty
  • if yes, send error message (for error handling) and terminate the flow
• download (this step will not happen if sas_uri is missing b/c it would have been terminated in the last step)
  

the 4 steps mentioned above

inside the Do Until

Linnea Wang

@Ashley Moose Thanks so much Ashley! This is brillant! Love it so much and it is so sleak and easy to use. Thanks for sharing.

Todd Peyton

@Ashley Moose Thank you so much! It was quick and easy. I got stuck on the related resources and I had to go to Microsoft Power Automate Blog for Importing the package steps.

Erik Leaver

@Todd Peyton Glad you figured it out! Want to share that we have an on-demand BBU course that walks through importing a template into Power Automate in case others run into challenges with the import.

Mark Palfrey

@Ashley Moose this is brilliant thank you so much! I tested it on a small query and it worked great but when I tried it with a query with lots of data being generated it came back with an error

Http request failed as there is an error:
'Cannot write more bytes to the buffer than the configured maximum buffer size: 104857600.'.

Is there any way around this for larger query results?

Ashley Moose

Hi @Mark Palfrey - Unfortunately there is not, this is a Power Automate limitation, not a Query limitation. That said, there are 2 things to consider:

1. On the Create file action, make sure Allow chunking is enabled. This can allow larger files to be created (Microsoft documentation for reference: https://learn.microsoft.com/en-us/power-automate/limits-and-config#message-size )

2. If the error still occurs, the only other solution is to break your query up into smaller parts.

Mark Palfrey

@Ashley Moose thanks for getting back to me so quickly. I had chunking already enabled but to be honest with you looking at it more closely I was trying to query out 800k rows of data so I think I can streamline that a bit. I ran another test and managed to generate a file of assigned appeals with 500k rows and the flow took about 1min to run! This will be so powerful for us in our PowerBI reporting requirements at the moment.

Louise Williams

@Ashley Moose, This is amazing thank you! It is working perfectly for me. I am wondering though, if I swap out One Drive for Sharepoint is an additional step required for the file to work in sharepoint.

I modified this flow to include both one drive and sharepoint and the one drive file works and opens correctly however the sharepoint file just gives a URL but doesnt actually transfer to data.

Any idea what additional step i might need to use to get it to work in sharepoint?

EDIT: I was able to figure it out! it is working perfectly. Thank you for this template!

Ashley Moose

@Louise Williams Glad to hear you got it working!! ?