Ack Letter for Donors and Soft Credits- Certain Level

Carol Grant

I'm trying to get a flow working for donors who have given $2,500k and above including soft credits. I've been trying many different things in the past few weeks and decided to use a NXT Constituent List. The issue is getting the gifts to list out correctly. Also to get the donors who give through Fidelity Charitable (DAF) and sum up the soft credit donors for $2.5k and up.

1. I'm using List Constituents to get the people but then I need to get their gifts.
2. Then I have List Gifts with the ConsID limited by the IDs in the NXT List. Then Get a Gift to have an array to grab the Soft Credit ID and Gift ID then another array to get any other donors that has ConsID and GiftID.
3. I have a union that combines these 2 arrays. Which I wonder about because it removes duplicates. (?)

4. Then I'm putting the combined array back through to get the gifts for both soft credit and regular gifts using a Select. This seems redundant but I don't know how else to connect the soft credit gift and the other gifts. The problem with this is it pulls all of the Fidelity gifts in for the week not just the ones that belong to the $2,500 soft credit donors. There is a disconnect when I do it this way.

Does anyone else have any ideas on how to accomplish this? Besides creating my own database in Azure.

1. Get donors who have soft credit gifts of over $2.5k
2. Get donors who have gifts over $2.5k (not Donor Advised)
3. Show their gifts in a merge

Thanks,

Carol

Alex Wong

@Carol Grant
Without saying “Azure Data warehouse”. You can use Query API.

• save a query of Constituent Query of summary of gift amount > $2.5K, with soft credit option that is right for your org. You only need to output constituent id (not system record id).
• Run the query using Query API and use JSON output option.
• Use “Select” to select only the constituent id to be used in a filter (one of) operator.
• do a adhoc gift query with filter being constituent id is one of the constituent id from the select OR soft credit recipeitn id is one of the constituent id
  • what I don't know is if “one of” has the same limit as in db view query (500 is limit per one of).
• run query… get result.. then it is a simple matter of using filter array on constituent id and soft credit reciepient id

this is all in theory should work. try it and see if you hit any issue

Alex Wong

@Carol Grant
So i just gave this theory a run…

AND IT WORKS!

Query ID: 56168

json(base64ToString(body('HTTP_Get_Query_Result_C')?['$content']))

{
"select_fields": [
{
"query_field_id": 4539,
"user_alias": "cid"
},
{
"query_field_id": 9307,
"user_alias": "scid"
},
{
"query_field_id": 4572,
"user_alias": "name"
},
{
"query_field_id": 6006,
"user_alias": "gid"
},
{
"query_field_id": 536,
"user_alias": "gdate"
},
{
"query_field_id": 639,
"user_alias": "gtype"
},
{
"query_field_id": 649,
"user_alias": "gamount"
},
{
"query_field_id": 9298,
"user_alias": "scamount"
}
],
"filter_fields": [
{
"query_field_id": 4539,
"compare_type": "None",
"filter_values": @{body('Select_Constituent_ID')},
"operator": "OneOf",
"left_parenthesis": false,
"right_parenthesis": false
},
{
"query_field_id": 9307,
"compare_type": "Or",
"filter_values": @{body('Select_Constituent_ID')},
"operator": "OneOf",
"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": 20,
"format": "Dynamic",
"suppress_duplicates": false,
"constituent_filters": {
"include_inactive": true,
"include_deceased": true,
"include_no_valid_addresses": true
}
}

Filter Field is using one of and the array of cid (constituent id) from the Select Constituent ID action. You can do more filter on gift type and gift date range if you want. (if you do, make sure the filter on constituent id and soft credit receipent id is in paranthesis as they are OR condition).

(cid one-of OR scid one of) AND gift type one-of AND gift date between xxx and yyy

“query” property is using the Compose Query Definition and you can use JSON output if you want.

After the download of the gift adhoc query, you can then use Filter array to condition on cid=someone_id OR scid=someone_id to get all gift direct or soft credit to someone_id.

To loop through all the “constituent id” that is in your original constituent query of those that gave $X and up, you can loop through the output of Select Constituent ID which gives you an array of all the constituent id to work on, then use each constituent id in the Filter Array.

json(base64ToString(body('HTTP_Get_Query_Result_G')?['$content']))

Alex Wong

@Carol Grant
oh forgot to mention:

Query API one-of operator allows MORE THAN 500 values (unlike the limit on query in dbview). My test had 800 constituent, and I can confirm all 800 constituent id was filtered on and I get gift adhoc query returning gifts for all 800 constituent id

Carol Grant

@Alex Wong- thank you for putting all of this effort in last night for me! I will definitely give this a try. I had used a Query but using credit to both and was still getting stuck. The way you laid this out and using Recipients instead was not something I thought about.

Alex Wong

@Carol Grant
which “soft credit option” you use on the constituent query affects if a constituent “qualifies” into your $2.5K+ so you need to think about your org's soft credit usage and how you normally report.

The 2nd adhoc gift query you will use “soft credit option” to direct credit only, as you are going to output soft credit info. You can try this out on an actual db gift query first to see how it turns out for a constituent that has both direct credit gift and soft credit gift.

Constituent ID	Soft Credit Recipent ID	Gift ID	Gift Amount	Soft Credit Amount
111		8000000	$5,000.00
555	111	8000001	$10,000.00	$3,000.00
555	222	8000001	$10,000.00	$4,000.00
555	333	8000001	$10,000.00	$2,000.00
555	444	8000001	$10,000.00	$1,000.00

the gift query will look something like above. Assuming this is all the gifts there is, 111 and 222 qualifies as $2.5K+. Your constituent query will produce 111 and 222 only. (555 is the DAF)

so when ad-hoc querying, you are only getting the first 3 rows. since you are looking at Constituent ID one-of 111, 222 OR Soft Credit Receipent ID one-of 111, 222. (by the way, if not apparent, you will be using the soft credit amount column if not blank. (use of expression will help here).

Constituent ID	Soft Credit Recipent ID	Gift ID	Gift Amount	Soft Credit Amount
111		8000000	$5,000.00
555	111	8000001	$10,000.00	$3,000.00
555	222	8000001	$10,000.00	$4,000.00

Carol Grant

@Alex Wong- I'm slowly working through this. I do want to limit gift type now in an adhoc query but it's saying I have a json error in here. I put it through a JSON beautifier and it hit on the Gift Type field numbers, it doesn't like “id”: “1", I tried removing the quotes from the 1 but that didn't work either. Is there a special way to call these? If this looks right, it maybe something else.

{
"query_field_id:" 639,
"compare_type": "And",
"filter_values": [
"id": "1",
"description": "One-Time Gift"
}, {
"id": "2",
"description": "Pledge Payment"
}, {
"id": "9",
"description": "Stock/Property"
}, {
"id": "10",
"description": "Stock/Property (Sold)"
}, {
"id": "11",
"description": "Pay-Stock/Property"
}, {
"id": "13",
"description": "Pay-Stock/Property (Sold)"
},

Alex Wong

@Carol Grant
the issue you are running into is you trust the Get Query a little too much, though I can't blame you for that. I demo-ed how you need to modify the query definition JSON after you do a Get Query, but gift type (which is different from most other fields) is a “static integer value”. So in order to do one of those gift type, you need to do an array of these values ONLY.

{
"query_field_id": 639,
"compare_type": "And",
"filter_values": ["1","2","9","10","11","13"],
"operator": "OneOf",
"left_parenthesis": false,
"right_parenthesis": false
}

Carol Grant

@Alex Wong- I must have missed when you demo-ed this.

For anyone else following this thread:

Although this may work for others, this solution is not going to work for me. There is too much duplication in the gift query because we soft credit all gifts to couples. We also enter separate gifts for each fund. I'll be happy when there is an Export API. I don't have the knowledge to filter the arrays, avoid the duplicates, etc.

Thanks for all your help Alex, this did open my eyes to other possibilities.

Alex Wong

@Carol Grant
org's gift recording policy will affect how you bring out data. It is not hard to get rid of duplicates in flow using the union() expression:

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#union

union expression will remove all dups from the array where all properties are the same. you can use union on the gift array with itself and it will remove dup on the gift array.

Though I am not sure what you mean by “we also enter separate gifts for each fund” being a problem, if they are separate gift, then individual gift showing up as its own row should not be an issue, unless you mean you use split gift. Split gift and soft credit are both independently one-to-many relationship with a gift, so it will require a little more “calculations” first.

there is no other way to gather all direct and soft credit gift of a specific constituent using current SKY API endpoint. (Unless of cuz, there is only ONE, or very few specific, record that is doing the soft credit, so you can just gather all gifts from those and then filter). At this point, you are 90% there, a few tweaks and you will have what you want, but in the end, it's up to you

Carol Grant

@Alex Wong- maybe I'll keep plugging away and see what happens. If you still want to help. I don't understand the filter array part of this. What am I trying to do here?

Here's what you said before: To loop through all the “constituent id” that is in your original constituent query of those that gave $X and up, you can loop through the output of Select Constituent ID which gives you an array of all the constituent id to work on, then use each constituent id in the Filter Array.

The Output under the loop is the Select Constituent ID.

The json is my second ad hoc query results. Then I have item()?['cid'] in the first part and items('Applytoeach- CID') in the second part. Is that what I want?

My filtered array appears blank for some and I'm not sure why yet. It is grouping the gifts by ConsID for others.

Query 1- soft credit to both

Query 2- adhoc query- I had soft credit to recipients.

Alex Wong

@Carol Grant
Glad to hear you will continue

So the filter array is trying to get from the list of all gifts returned by the 2nd query (adhoc gift query) where cid is of the constituent (in the loop) OR scid is of the constituent. (meaning any gifts that is direct or soft credit to the constituent).

What you have now only looks at direct credit (cid) is the constituent, maybe the specific constituent you were looking at at run history only have soft credited gift so nothing is filtered.

To do this, you can:

• cid is equal to current item (you did this already
• click on Edit in advanced mode and copy the expression for this, and paste to notepad
  should look something like this @equals(item()?['cid'], items('Applytoeach-CID'))
• click to go back to basic mode
• delete the cid dynamic content and do scid: item()?['scid']
• click on Edit in advanced mode and copy the expression for this too
  should look something like this @equals(item()?['scid'], items('Applytoeach-CID'))
• then in notepad do a OR condition
  should look something like this @or(equals(item()?['cid'], items('Applytoeach-CID')), equals(item()?['scid'], items('Applytoeach-CID')))
• paste this whole thing starting with the @ symbol into the Advanced mode filter array

After the filter array, you should have an array of all gifts direct or soft credit to the constituent id in the loop (cid or scid is the constituent id).

Important: make sure your first query (constituent query that is saved in database view) is outputing the Constituent ID field, not System Recod ID, in this whole flow, we are going to use query API and only using the “lookup id” (per naming used in the SKY API).

Carol Grant

@Alex Wong- I actually did try to do the @or and it didn't work so thank you for laying out those steps. It did work!

Now I have the filtered array. Next is pulling the address information for the people and get the fund description for the gift for the acknowledgement and gift date.

I'm doing a loop for the body of the Filtered array. and then putting in item()?['gid'] for get a gift. I wonder if I should do a select outside the loop then do Get a Gift? I am getting 2-3 gifts per ID which is okay, I'll add them to a final array and do some unions. (I hope)

How would I pull in Get a Constituent using the cid piece of the filtered array. Same way?

Alex Wong

@Carol Grant
Hi, the simpest way to do this is to get all the data you want from the 2nd query (gift adhoc). So output constituent prefereed address directly. As for fund description, if you have to deal with possible split gift, then use Fund List instead (this will give you a comma or semi colon, forgot which, separated list of all fund), this will avoid duplicates based on fund description.

If you must use normal SKY API endpoint to get more data, then you will need to get system record id (constituent and gift system record id). Meaning, in first query, other than Constituent ID (cid), also output System Record ID, in 2nd query (gift adhoc query), also output gift system record id.