Query API

Laura Hill

Good morning! For those of you using query api to do pull data for Power BI financial reporting, I can't seem to pull the fields soft/match credit flag and Linked to Planned Gift in a query. How are people getting these data points, as they are necessary for our reporting and I assume for your financial reporting. Thanks in advance.

Laura Hill

@Laura Hill also, I can't seem to get the head of household indicator in a query, which is another data point we need. Any ideas? Thanks

Alex Wong

@Laura Hill
financial reporting, so first question is, are you trying to get data out of RE or FE?

Am I understanding your “soft/match" means soft credit and matching gift? not sure what you are looking for.

Laura Hill

@Alex Wong I am pulling data out of the gifts table in RE.

Alex Wong

I can't seem to get the head of household indicator in a query, which is another data point we need. Any ideas? Thanks

head of household information can be obtained from Constituent List API endpoint directly.

If you have to use Query API, it is available under Relatinship > Individuals > This Individual Is the head of household.

Alex Wong

I am pulling data out of the gifts table in RE.

what info you trying to get?

Soft credit is available under Gift Query > Soft Credits section. But you need to pay attention as Query will give you duplicate rows based ONE-to-MANY relationship. Trying to output multiple ONE-to-MANY relationship will be difficult to manage. (though this isn't a Query API issue, but a Query usage issue)

what “match” info you are looking for. (Matching Gifts is also available under a section in Gift Query)

Carol Grant

@Laura Hill- Hi Laura- In reading this thread, I want to express that you should use caution with the Query API. It is not like an export. The soft/hard credit indicator is only available in export in DB View not query.

Also, when you pull on head of household in query it will duplicate based on relationships. This field might be something you would put in a constituent query but even then it can duplicate, export is your best bet with HOH also. Unless you have queries (not exports) that already pull this information accurately I would not build a new query with these fields in the Query API. You may have to wait for the Export API to come out.

I can see Gift linked to a Planned gift in the Query API.

Good luck!

-Carol