Unable to select/filter/sort on some fields while creating ad-hoc query through the Query API

Somkala Pachhai

Using Get Query on a query, I got the full query definition that has filter/select/sort field on specific fields, but when I tried to create ad-hoc query using the same query definition, I am getting following errors:
“The provided query field ID XX in sort_fields [0] query_field_id cannot be used with the query type XX.”

“The provided query field ID XX in select_fields [0].query_field_id cannot be used with the query type XX.”

while using date field on filter:

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

If this query is executed successfully in the blackbaud database view, my understanding is that it should be run through the Query API ad-hoc endpoint with the same query definition(filter/select/sort query fields). But it gives us lots of validations errors.

Please correct me if we can't use the same query definition (select/filter/sort fields) because of strict validation, how can we know that which fields can be used with which query type?

The main issue we are having is on the date filter which is currently not supported and another issue with one specific field that has notblank operator on filter.

We are aware that blackbaud is continuously working on improving the Query API, when will we be able to test the query api ad-hoc endpoint with the date filters and with the same query definition which we received from the GET Query endpoint?

Alex Wong

@Somkala Pachhai
i started with using Adhoc querying as well, and initially when Query API in “preview” (step before public preview), I was able to do all my query using adhoc. However, Blackbaud took that away as they discover more need for validation, so many fields became unavailable for adhoc. So I had to revert back to using a saved dynamic query in dbview to run query by id instead of ad hoc.

For now, unless you have a need to “dynamically” change the filtering/output field, stay with saved query for now. If you do need to dynamically change filering/output field, then good luck, if a field is definitely not available on adhoc, then you are out of luck, for now.

Somkala Pachhai

@Alex Wong
Thank you so much for your reply.
For some reason, we really need the ad hoc to use the query definition instead of query id into our new feature but because of some limitation our feature is on the hold.
Hopefully, blackbaud will give us solutions to continue to work with ad-hoc endpoint without such limitation (date filters and such.)