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

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

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

you do not have access to this query
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

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

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

2bd3b8d724211578787c14bf8e0d1609-huge-im — Query Type is Gift and Query ID is 61081

in the flow:

25f7ffeadaf93e7478e9e0ad2f8cd726-huge-im

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

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

f6fd6e670c1d4c1c112f4de015ef0291-huge-im

Edit mode of Start a Query job

1094d0b24fc834d85a6f727d1e39dd35-huge-im

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

@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/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.

8cde8a53512289ebc6d74836da0c7693-huge-im

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

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

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

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

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

Thanks!

2b320adc53ae508b34096cc45dee6533-huge-im

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

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

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

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

d30e0c8427a8eeead93e06165dd09b35-huge-im

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

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

@Alex Wong

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

Very appreciate your help!

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

3504ef4a8ee8882b976cd21f5fc3bfad-huge-im

Appreciate it in advance.

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.

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

determine how many gift in total (gift list api limit 1 will give you a total count)
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
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)
check job status
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.

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

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

f3b1bd62f8254c8de8fbea393e502380-huge-im

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

6db2544b44d101434fc479b39e90758d-huge-im

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:

c818ecfe232b162a539282ed744aa9e0-huge-im

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?