Sharing my Power Automate template: Ah-hoc Query API for NVA and RNE

Alex Wong

- Description: Use the new (still in public preview) Query API to run an ad-hoc database query (without actually having a query saved in database) to get No Valid Address and Request No Email property of constituent record that is not available from SKY API Constituent List. The flow actually run the API and get the information to store into Azure data warehouse table, but you do not have to and can instead be saved to SharePoint/OneDrive or other cloud storage for use later in your Power BI. This is a pretty quick running flow for more than 400K records, takes less than 2 mintues to run and store into data warehouse.

- Required connections: Blackbaud SKY Add-ins; MS Teams (for error handling messaging); Azure SQL (for processing into Azure SQL database)

- Suggested skill level required to use this template: Intermediate

- Configuration details:

• does not require you to have a query setup in database view first, so no need to worry about the query being accidently deleted

- Category: Automation, Data Management, Integration

- Instructions:

• Download and import and update the flow save name and connection
  Ad-hocQueryAPIExportNVAandRNE_20240229173352.zip
• if you want to save to onedrive, then delete the actions that “Select As JSON”, “Execute SQL TRUNCATE TABLE” and “Execute SQL INSERT from JSON”
• Depending on if you want the CSV saved or and Excel file in the cloud storage, refer to the following post.
  • Save to Excel: https://community.blackbaud.com/forums/viewtopic/426/65782
  • Save to CSV: https://community.blackbaud.com/forums/viewtopic/586/66942 Thanks @Ashley Moose

Alex Wong

Using the same flow, here's the Request Body to get Prospect Classification and Status:

{
"query":
{
"select_fields": [
{
"query_field_id": 349,
"user_alias": "id"
},
{
"query_field_id": 3789,
"user_alias": "classification"
},
{
"query_field_id": 3788,
"user_alias": "status"
}
],
"filter_fields": [
{
"query_field_id": 3789,
"compare_type": "None",
"filter_values": [
0
],
"operator": "NotBlank",
"left_parenthesis": false,
"right_parenthesis": false
},
{
"query_field_id": 3788,
"compare_type": "Or",
"filter_values": [
0
],
"operator": "NotBlank",
"left_parenthesis": false,
"right_parenthesis": false
}
],
"sort_fields": [],
"gift_processing_options": {
"soft_credit_option": "Donor",
"matching_gift_credit_option": "MatchingGiftCompany",
"use_gross_amount_for_covenants": false
},
"advanced_processing_options": {
"use_alternate_sql_code_table_fields": false,
"use_alternate_sql_multiple_attributes": false
},
"type_id": 18,
"format": "Dynamic",
"suppress_duplicates": false,
"constituent_filters": {
"include_inactive": true,
"include_deceased": true,
"include_no_valid_addresses": true
}
},
"ux_mode": "Asynchronous",
"formatting_mode": "None"
}

Alex Wong

Due to status like Failed and Throttled, I modified the way I do this per below, eventually i'll update the template:

• 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

Kevin Fletcher

@Alex Wong I don't have access to an SQL DB, so the flow won't import for me to look under the hood.

I'm trying to understand your follow-up post with the query_field_id's in the JSON for the Request Body ... how/where do I get the query_field_id?

I'm trying to see if I can use Query API to automate my monthly report. Currently, I have more than a dozen queries that I have to change "Between" dates each month that I output to CSV (and then re-run them to change the "between" dates to run last FY comparison). I then glue all those CSVs together in an Excel file to compute our monthly output.

Ideally, I'd have my flow read a simple Excel file of whatever my start and end dates are for my Between dates, and use variables to run ad-hoc queries.

So how can I get the query_field_id?

Alex Wong

@Kevin Fletcher
You can read this post:

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

Atsuko Umeki

@Alex Wong
Hi Alex,

Thanks so much for sharing that flow! Just a heads-up, I haven't been able to fully explore it yet as I need to request our Systems department to set up a SQL connection first. I tried to use HTTP connection, but found out that HTTP connection is not allowed in our organization.

In the meantime, I had a question about using the Query API to retrieve data. We haven't used Power Automate much for this because I was under the impression there's a 5000-row limit when connecting to the Query API.

However, it looks like your flow might be handling more than that. Could you possibly give me some guidance on how to retrieve all the rows I need from NXT, even if it goes over that 5000 limit?

Any tips or tricks you could share would be fantastic!

Alex Wong

@Atsuko Umeki
Query API does not have a 5000 limit. 5000 limit is with SKY API “list” type of endpoint (i.e. Constituent LIst, Gift List, etc). Query API does not have this limit.