Query API - Refresh static queries

Carol Grant

I was excited to see the POST action added to Refresh static queries yesterday so I tried it in Ashley's Query API template. Worked great except it fails at the sas_uri link. I'm going to assume it fails there because it doesn't really “run”. It is only refreshing so there is no output.

Let's say I wanted to run it after it refreshes. I tried to add another POST action for Execute Query by ID with a Do Until but it just spins. This is my first time really getting into the GET and POST actions. Any ideas on how to go about this?

I have a POST for query/queries/refreshstaticquery, then I use the same GET /query/jobs as Ashley had in the template with a Do Until loop. Do I add another POST for /query/queries/executebyid ?

Ashley Moose's template post is here for anyone else

Thanks,

Carol

Alex Wong

@Carol Grant
in a run history, just take a look at the status returned and condition on that instead of the sas_uri

I mentioned in the power app user group (i think) that there are more statuses that you should condition on when running query and not just based on the Completed statuses and then check the sas_uri before trying to download. (in this case, not download, but just status that is “good” to proceed).

Carol Grant

@Alex Wong

I guess I'm asking how do I access the query output after I refresh a static query. I don't think I can do this in the same flow. I tried saying if refresh is complete run another POST action for Execute Query by ID but it didn't give me any output (which I'm think is the sas_uri link).

Another question, I don't see a way to get the constituent's system id after running a query. I can loop through the csv file that is saved. It would be nice to have a system id in Query so we could connect it to other Power Automate connections. Like list gift, get constituent. In time they might have the id available.

I'm trying to do a first time donor list and it's a pain to query all gifts entered for a period of time then filter it down to see if the gift date=constituent's first gift date. I don't see any other way around it. I have a query though.

Alex Wong

@Carol Grant
I created a static query to play with so here's what I found.

After executing a the refresh job

what you get back is the same as when you execute a query by id (or adhoc). So you will use the same logic to check status in a loop. When status is completed

you will get the job id back (not useful), status, and row_count, which I can only assume it is the new # of records from the status query after it has been refreshed. If any NXT webview list is using the static query, that list should now be updated (given normal NXT list refresh timeframe).

if you want to get output form the static query, after confirming this refresh job is status=Completed, then you will need to use execute query by id to get output from the static query, follow by same logic again to loop and get status of the execute query by id job, and when status=Completed, then sas_uri will be available for use to download and process.

so the questoin is when you say “another POST action for Execute Query by ID but it didn't give me any output” what do you mean

Alex Wong

@Carol Grant

Another question, I don't see a way to get the constituent's system id after running a query. I can loop through the csv file that is saved. It would be nice to have a system id in Query so we could connect it to other Power Automate connections. Like list gift, get constituent. In time they might have the id available.

not sure what this issue is for you. You can add the constituent system id into the query output directly, all my query have system record id (be it constituent system record id or gift system record id).

I'm trying to do a first time donor list and it's a pain to query all gifts entered for a period of time then filter it down to see if the gift date=constituent's first gift date. I don't see any other way around it. I have a query though.

if you have a query, why not just run the query through query api? is some of the field you filtering and/or outputing not allowed? if you want to do first time donor and their first time gift using SKY API, it is possible, but the only way I know of is to get ALL constituent records and ALL gift records saved (the best way that i know of is to use azure data warehouse, then sharepoint excel/csv file, although the new “FABRIC” thing from Microsoft may be good idea, though I don't know much about it). Once you have all constituent and gift data (and you will have gift soft credit data too), then you can use power bi to transform and query data for first time constituent and their first time gift

Carol Grant

@Alex Wong- I did a few more tests- still no output. Here's my flow:

First part is refreshing the static query. The HTTP Request below is POST- query/queries/refreshstaticquery. I created another Refresh Status variable.

Then I have a condition where I set a Refresh Status equals Completed which is true below.

Then I have a send HTTP request again saying Run Static Query which is below.

A better view of what is in the Run Static Query.

Then I have another Do Until loop to see when it's complete and I have another Get Job results inside.

Which only returns this, no sas_uri in the output. I feel like it is still grabbing the results from the Refresh Post request and not recognizing the new POST which is execute by ID.

I can run the query fine after it refreshes in another flow but I think here it gets confused doing it in one flow using the same query id.

Alex Wong

@Carol Grant
yes, you are correct, you are checking status of the “refresh” job.

In your Send an HTTP request to get query job 2 action, what “id” are you providing it, it should be the id returned by your Send an HTTP requet - Run Static Query action.

Carol Grant

@Alex Wong- just had a lightbulb moment! Thank you!! I had the wrong Query ID in the Send an HTTP request to get query job2. It was the previous one. It works now.

Next question is the first time donor issue. I don't know how to add the ID to an existing query in the API or how to access the output fields. Would I need to do the query as an Ad hoc one and add the output fields.

Carol Grant

@Alex Wong - I figured out the first time donor and I tried created an adhoc query with the Query API thanks to your example that you posted at the link below.

I didn't get it to work correctly because Const Code (query id 2217) can't be used in an adhoc query. Neither can First Gift Date (query id 68100). Here is the error I get.

Query field 2217 referenced at filter_fields[0].query_field_id does not currently support add, edit, or ad-hoc execution

Alex Wong

@Carol Grant
then just create the query (dynamic) save it, and run it using execute query by id