Sharing my Power Automate flow: Get query results using the new Query connectors!

Ashley Moose

Description - This flow will take the results of a query and save them to OneDrive (or Google Drive). There are four versions of the flow included - one each for RENXT/FENXT for both OneDrive and Google Drive.

This simplifies and replaces my previous template as the old template used the Send an HTTP action, and this uses the official query connectors. This template also introduces an additional step to stop the flow if the query is Failed or Cancelled, and will notify you via email of the failure.

Required connections

• Blackbaud RENXT or FENXT Query
• OneDrive for Business (or Google Drive)
• Office 365 Outlook (or Gmail)

Skill level - Beginner

Instructions

• Import the attached .zip file into Power Automate (import as Package/Legacy file)
• Modify the Recurrence trigger for whenever you want the query results to be pulled
• Update the Initialize variable fileName action with the name of the file you want saved to OneDrive. The file name should include the .csv extension.
• Update the Initialize variable emailToNotify action with your email address. This is the email address that will be notified if the query fails or is cancelled.
• Update the Start a query job action to select the query you want to run.
• Update the Create file action to point to where you want the file saved (the Create file action is inside the Condition action at the end of the flow).

Note: If using Google Drive please take a look at this information about the different features that are or are not allowed when connecting different types of Google accounts to Power Automate.

OneDrive/Outlook Flows

SaveRENXTqueryresultstoCSVfile.zip

SaveFENXTqueryresultstoCSVfile.zip

Google Drive/Gmail Flows

SaveFENXTqueryresultstoCSVfileonGoogleDrive_20250204171256.zip

SaveRENXTqueryresultstoCSVfileonGoogleDrive_20250204170937.zip

Candy Reichert

@Ashley Moose
I have set up this to run. I am having a problem adding the query in the Start a query job. I can see a list of queries. However, the list is very short and does not include the query I need.

I tried adding in the Source id: 51253 for the query i need. It fails at this point.
Any help would be appreciated.

Alex Wong

@Candy Reichert
if after seleing the Type and not see the Query in the drop down list, then one of two things can be happening.

1. you do not have access to this query
2. if you confirm you have access, or you created the query, then it is likely b/c you are connected to wrong environment of RE.

In power automate, there is no way for you to tell if your connector is connected to which RE environment (i.e. Cohort, or test, or production environment). The properly way to solve this situation is too much to post here, I think there was another community post that already talked about it.

So the “fix” is to create a new connection, and make sure you select the correct environment

Candy Reichert

@Ashley Moose
I renamed the query so it showed in the list. The list of queries they choose to show seems to be pretty random. It is still throwing an error at that point. I'm not sure what info I can share with you to let you see the error.

Alex Wong

@Candy Reichert
Go to database view, and open the query property to get the Query ID.

Query Type is Gift and Query ID is 61081

in the flow:

select the same query type, then in the Query property, scroll down to the bottom and click on Enter custom value, then enter the Query ID.

Run the flow and if you still have error, paste the error message, screenshot of the action that failed, and the edit mode of the same action

Candy Reichert

@Alex Wong
It is a query I created. I deleted the Connector to RE NXT Query and added it again with our site.

Error from failed flow run.

Edit mode of Start a Query job

Alex Wong

@Candy Reichert
Looks like you are not RE solution admin?

if you are not, then your scope on developer.blackbaud.com need to be looked at and “resaved”.

see post:

https://community.blackbaud.com/forums/viewtopic/491/70374

https://community.blackbaud.com/forums/viewtopic/147/70059

Ashley Moose

@Candy Reichert I know @Alex Wong has you moving in the right direction here, but adding another option in case others encounter the same issue (i.e. you don't see your query in the drop-down).

The List queries action can be added before the Start a query job action and searched based upon the query name using the Search text parameter (and/or search by any of the other parameters). As long as you are only getting one result back, you can then use a first() expression to get the ID in the Start a query job action. If multiple queries are returned from the List queries action you may need an additional Filter array action to pin down the correct query.

Here the first expression is: first(outputs('List_queries')?['body/queries'])?['id']

Candy Reichert

@Ashley Moose
Thank you. This works well, I think.

@Alex Wong

I am an Admin in Sky Developer. I have tried to resave but I think I need to have my DBM do this to make it work. Will try again after he refreshes it.

Thanks for all your help.

Candy Reichert

@Alex Wong
My DBM and I have looked through the Marketplace SKY API Console and do not see any way to refresh or resave to this. The only thing left seems to be to disconnect it and reconnect it. NOt sure what that would do. We have looked through the other message strings you linked to and still do not see how to do this.

Do you have a step by step process to resave it so the Query API will run?

Alex Wong

@Candy Reichert
I am full environment admin for my org, so I have no restriction at all, and did not need to do any “refresh”.

@Erik Leaver is this something you can help Candy with?

Erik Leaver

@Candy Reichert I'm thinking the issue here is still with permissions. If you don't have the correct marketplace permissions, you won't be able to approve the scope changes.

Can you confirm you or your DBM have the correct marketplace permissions so you can update the scope? Relevant documentation is here:

Jacob Dong

@Ashley Moose @Alex Wong I don't have available options to click or modify the FENXT Query connector. Any thoughts of it?

Thanks!

Alex Wong

@Jacob d***
I am not sure what you are trying to do.

The screenshot you post said there is no trigger for this connector, which is true. There are no trigger (trigger is “when something happen”), there is no “when something happen" for Query API. It is all actions: do this; do that.

So instead of trying to add a “trigger” and select the Blackbaud FENXT Query connector, try to add an “action” and select the Blackbaud FENXT Query connector.

Jacob Dong

@Alex Wong
Thanks for your reply.

I was going to import the template and trying to fetch the query results from FENXT Query API.

I got some difficulties to modify the Query connector credentials when importing Ashley's template.

Now I figure out that I have to upgrade to Power Automate premium first. I'll work with our DBA on the licensing.

I have a question about the Blackbaud Query API. According to the document, Blackbaud only support POST request for the Query execution job. How do we Get the data of query results?

Thanks again.

Alex Wong

@Jacob d***
Query API works like this:

• Execute Query
  https://developer.sky.blackbaud.com/api#api=query&operation=StartQueryExecutionJobByID
  or
  https://developer.sky.blackbaud.com/api#api=query&operation=StartQueryExecutionJob
  • either using query id to execute a saved query in dbview
  • or execute adhoc query using a JSON definition that represent query info (filter, output, sort, query option, etc)
  • this step will give you a job id
• using job id, you will execute another endpoint to check status of the run
  https://developer.sky.blackbaud.com/api#api=query&operation=GetJob
  • this step can result in success or fail, you have to consider the possibility
  • if successful, you will get a sas_uri which is the URL link that will download the result
• use the sas_uri on success run to download the result
  • result can be in CSV or JSON, or JSONL (don't worry about JSONL, you will likely better off in your case to use CSV or JSON)

Jacob Dong

@Alex Wong
Thanks Alex. That is very helpful.

When I trying the "Query execution job (by ID)” endpoint, I got 404 response. Do you have any idea of it?

Alex Wong

@Jacob d***
if you got the query id, did you first execute the query by id?

if you did, you should get an job id, this job id is what you need to provide to get query execution job status to get the status of the running query.

Jacob Dong

@Alex Wong
Yes I did. The “Query execution job (by ID)” only requires two parameters.(product and module).

I'm not sure how to specify the query id.

Jacob Dong

@Alex Wong

Ok Alex. I think I figured it out. I got the sas_uri now.

Very appreciate your help!

Jacob Dong

@Alex Wong
I imported Ashley's template and it ends up with an error when (HTTP)getting file from sas_uri.

I built a similar work flow and gets the same error. Now I'm stuck here.

Do you have any idea about this?

Appreciate it in advance.

Jacob Dong

I figured it out. There is a 100 MB limit for the message size in Power Automate. Someone may suggest “Allow chunking” which could not be supported for every action in this case. Another solution is to limit the data returned by specifying "Select columns" and "Filter Rows" in the List Rows action of Dataverse.

Alex Wong

@Jacob d***
Depending on number of columns and rows you are exporting, your data size may be too big for power automate to handle at “once”. Also CSV will use less byte size than JSON.

The way I deal with this is issue 3 query executions. For example, my org has ~1.8M gift records. My data sync only need 3 extra data point per gift that I can't get from gift list api. So I did 3 query executions (didn't need 3, but this is to future proof a bit better). I do Ad-hoc query execution so I can determine my “filter” on the fly dynamically. So basically:

1. determine how many gift in total (gift list api limit 1 will give you a total count)
2. logically determine the 2 numbers to filter with
   1. for example, 1.8M gift record means ~ 600 rows per execution
   2. that means my 2 numbers are 600,000 and 1,200,000
3. execute adhoc query 3x
   1. filter on gift system record id less than first number (600000)
   2. filter on gift system record id greater than or equals to first number (600000) and less than 2nd number (1200000)
   3. filter on gift system record id greater than or equals to 2nd number (1200000)
4. check job status
5. download each file

Sarah Homan

How do I get the results in JSON using Ashley's template for the Query Connector? I have tried changing settings and different actions, but the results are always in a CSV file. I must be missing something. Thank you, as always, for the help.

Alex Wong

@Sarah Homan
Unfortunately you cannot. Using the official connector for RE NXT Query does not give you the option to select output option.

You will need to use the the Blackbaud Send HTTP Request action in the Blackbaud Add-in connector (Ashley first template).

@Heather McLean @Erik Leaver I think the output option is significant enough to not default to CSV, but give user option to select. Not sure if this is something you can push for.

Sarah Homan

@Alex Wong
Thank you, Alex. I will continue to use the HTTP request version.

Glen Hutson

@Sarah Homan / @Alex Wong - There is quite the process to get an updated connector through the MS certification steps. The CSV vs JSON was added after the process had already started with the first version. Similar is true for any part of the API: the connectors won't always have the latest bit, but that's where the Send HTTP Request will come in handy.

Heather McLean

@Alex Wong - we have already submitted this change to include Output option to Microsoft and it is in deployment.

Alex Wong

@Heather McLean
nice

Advancement Division

@Sarah Homan
Unfortunately you cannot. Using the official connector for RE NXT Query does not give you the option to select output option.

You will need to use the the Blackbaud Send HTTP Request action in the Blackbaud Add-in connector (Ashley first template).

@Alex Wong I'm not an HTTP Request guru… where can I either request the output in JSON, or somehow parse the CSV contents?

When I run a compose on the body of the HTTP - Get query results, I get what looks like CSV content:

Yet when I try to run various composes to split the data and convert into a JSON array, I get an error that the original CSV is an object that can't be converted to a string:

So… how can I either get the output of the HTTP request to be a JSON, or how can I convert the existing CSV output?

Alex Wong

@Advancement Division
It is not the HTTP action to download that you specify the format of the output.

Use this action to make the call to execute the query (by id or adhoc), it is this action that you can specify the output format.

when using JSON as output and use the regular HTTP to download

You can directly use the json data by using Parse JSON or in my case, pass the JSON directly into Execute SQL action to insert into my data warehouse.

base64ToString(body('HTTP_Get_Query_Result')?['$content'])

The expression to directly use the HTTP downloaded data is by using base64ToString() expression. You can also use the “Compose” action, that does the decoding for you, but why use an extra action when you don't need to right =D

Also note that you are passing in the body('HTTP_Action_To_Download_Name')?['$content'], b/c the HTTP action that download the data, is actually itself a JSON object with 3 properties:

It is the $content property that is the actual JSON data.

Getting a little long for 1 post, i'll do another for CSV