"Start an ad hoc query job" query type error

Ryan Carr

Newbie with RENXT Query Connector - I tested my below json code through the API Reference page and while using a “Send an HTTP request” action, and both came back with no errors. When I try to run it through Power Automate using the “Start an ad hoc query job”, I am getting an error saying, “Invalid query type”. Do we not just dump all of this json into the “Query definition” field?

{
"query": {
"select_fields": [{
"query_field_id": 536
},{
"query_field_id": 649
},{
"query_field_id": 639
}],
"filter_fields": [{
"query_field_id": 536,
"compare_type": "None",
"filter_values": ["7/1/2022", "6/30/2025"],
"operator": "Between",
"left_parenthesis": false,
"right_parenthesis": false
},{
"query_field_id": 639,
"compare_type": "And",
"filter_values": [1,3,8,9,10,31],
"operator": "OneOf",
"left_parenthesis": false,
"right_parenthesis": false
}, {
"query_field_id": 1057,
"compare_type": "And",
"filter_values": [11495],
"operator": "Equals",
"left_parenthesis": false,
"right_parenthesis": false
}, {
"query_field_id": 4539,
"compare_type": "And",
"filter_values": ["4803"],
"operator": "Equals",
"left_parenthesis": false,
"right_parenthesis": false
}],
"sort_fields": [{
"query_field_id": 536,
"sort_order": "Ascending"
}],
"type_id": 20,
"sql_generation_mode": "Query",
"gift_processing_options": {
"soft_credit_option": "Both",
"soft_credit_sub_option": "UseAmountInGrid",
"matching_gift_credit_option": "Donor",
"use_gross_amount_for_covenants": false
},
"advanced_processing_options": {
"use_alternate_sql_code_table_fields": false,
"use_alternate_sql_multiple_attributes": false
}
},
"ux_mode": "Asynchronous",
"output_format": "Json",
"formatting_mode": "UI"
}

Alex Wong

@Ryan Carr
if you are the connector for query to run ad hoc query, it does not exact the “full json body”, ONLY the “query” part of the property, so:

{
"select_fields": [
{
"query_field_id": 536
},
{
"query_field_id": 649
},
{
"query_field_id": 639
}
],
"filter_fields": [
{
"query_field_id": 536,
"compare_type": "None",
"filter_values": [
"7/1/2022",
"6/30/2025"
],
"operator": "Between",
"left_parenthesis": false,
"right_parenthesis": false
},
{
"query_field_id": 639,
"compare_type": "And",
"filter_values": [
1,
3,
8,
9,
10,
31
],
"operator": "OneOf",
"left_parenthesis": false,
"right_parenthesis": false
},
{
"query_field_id": 1057,
"compare_type": "And",
"filter_values": [
11495
],
"operator": "Equals",
"left_parenthesis": false,
"right_parenthesis": false
},
{
"query_field_id": 4539,
"compare_type": "And",
"filter_values": [
"4803"
],
"operator": "Equals",
"left_parenthesis": false,
"right_parenthesis": false
}
],
"sort_fields": [
{
"query_field_id": 536,
"sort_order": "Ascending"
}
],
"type_id": 20,
"sql_generation_mode": "Query",
"gift_processing_options": {
"soft_credit_option": "Both",
"soft_credit_sub_option": "UseAmountInGrid",
"matching_gift_credit_option": "Donor",
"use_gross_amount_for_covenants": false
},
"advanced_processing_options": {
"use_alternate_sql_code_table_fields": false,
"use_alternate_sql_multiple_attributes": false
}

Ryan Carr

@Alex Wong Got it, that worked, thanks!