Query funds¶
Walkthroughs of the queries partners most often run against the Fund table. Every scenario is shown four ways — Web API, curl, Python, C# — so you can follow whichever fits your stack.
This page assumes you've worked through the Dataverse quickstart. For the complete column and relationship catalog, see the Fund reference.
Conventions
Examples below assume ORG (env URL), access_token (valid bearer), headers (standard Dataverse headers dict), and service (a ServiceClient instance). See Authentication.
Scenario 1: Export all active endowed funds¶
Finance wants a periodic snapshot of every endowed fund with current balances. Single query plus pagination.
GET {org}/api/data/v9.2/akoya_funds?$select=akoya_fundname,akoya_fundcode,akoya_fundtype,akoya_netassets,akoya_spendable,akoya_earnings&$filter=statecode eq 0 and akoya_endowed eq true&$orderby=akoya_fundname asc
Accept: application/json
OData-Version: 4.0
OData-MaxVersion: 4.0
Prefer: odata.maxpagesize=500
On large result sets, the response includes an @odata.nextLink URL — keep following it until absent.
NEXT="https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds?\$select=akoya_fundname,akoya_fundcode,akoya_fundtype,akoya_netassets,akoya_spendable,akoya_earnings&\$filter=statecode%20eq%200%20and%20akoya_endowed%20eq%20true&\$orderby=akoya_fundname%20asc"
while [ -n "$NEXT" ]; do
RESPONSE=$(curl -s "$NEXT" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
-H "OData-Version: 4.0" \
-H "OData-MaxVersion: 4.0" \
-H "Prefer: odata.maxpagesize=500")
echo "$RESPONSE" | jq -c '.value[]'
NEXT=$(echo "$RESPONSE" | jq -r '."@odata.nextLink" // empty')
done
url = f"{ORG}/api/data/v9.2/akoya_funds"
params = {
"$select": "akoya_fundname,akoya_fundcode,akoya_fundtype,akoya_netassets,akoya_spendable,akoya_earnings",
"$filter": "statecode eq 0 and akoya_endowed eq true",
"$orderby": "akoya_fundname asc",
}
page_headers = {**headers, "Prefer": "odata.maxpagesize=500"}
all_funds = []
while url:
r = requests.get(url, params=params if not all_funds else None, headers=page_headers)
r.raise_for_status()
body = r.json()
all_funds.extend(body["value"])
url = body.get("@odata.nextLink") # absolute URL with params baked in
params = None
var query = new QueryExpression("akoya_fund") {
ColumnSet = new ColumnSet(
"akoya_fundname", "akoya_fundcode", "akoya_fundtype",
"akoya_netassets", "akoya_spendable", "akoya_earnings"),
Criteria = {
Conditions = {
new ConditionExpression("statecode", ConditionOperator.Equal, 0),
new ConditionExpression("akoya_endowed", ConditionOperator.Equal, true)
}
},
Orders = { new OrderExpression("akoya_fundname", OrderType.Ascending) },
PageInfo = new PagingInfo { Count = 500, PageNumber = 1 }
};
var all = new List<Entity>();
while (true) {
var page = service.RetrieveMultiple(query);
all.AddRange(page.Entities);
if (!page.MoreRecords) break;
query.PageInfo.PageNumber++;
query.PageInfo.PagingCookie = page.PagingCookie;
}
Scenario 2: A donor's fund affiliations¶
"Show me the funds this donor is primary on, with their current balances." No $expand needed — we already have the donor.
DONOR_ID="00000000-0000-0000-0000-000000000000"
curl "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds?\$select=akoya_fundname,akoya_fundcode,akoya_fundtype,akoya_netassets,akoya_spendable&\$filter=_akoya_primarydonor_value%20eq%20$DONOR_ID%20and%20statecode%20eq%200&\$orderby=akoya_netassets%20desc" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
-H "OData-Version: 4.0" \
-H "OData-MaxVersion: 4.0"
donor_id = "00000000-0000-0000-0000-000000000000"
r = requests.get(
f"{ORG}/api/data/v9.2/akoya_funds",
params={
"$select": "akoya_fundname,akoya_fundcode,akoya_fundtype,akoya_netassets,akoya_spendable",
"$filter": f"_akoya_primarydonor_value eq {donor_id} and statecode eq 0",
"$orderby": "akoya_netassets desc",
},
headers=headers,
)
funds_for_donor = r.json()["value"]
var donorId = new Guid("00000000-0000-0000-0000-000000000000");
var query = new QueryExpression("akoya_fund") {
ColumnSet = new ColumnSet("akoya_fundname", "akoya_fundcode", "akoya_fundtype", "akoya_netassets", "akoya_spendable"),
Criteria = {
Conditions = {
new ConditionExpression("akoya_primarydonor", ConditionOperator.Equal, donorId),
new ConditionExpression("statecode", ConditionOperator.Equal, 0)
}
},
Orders = { new OrderExpression("akoya_netassets", OrderType.Descending) }
};
var fundsForDonor = service.RetrieveMultiple(query);
Filter on _xxx_value, not the navigation property
In $filter, use the lookup's GUID column (_akoya_primarydonor_value), not the navigation property (akoya_PrimaryDonor). The navigation property is only used in $expand and @odata.bind. In C# QueryExpression, use the logical name of the lookup attribute (akoya_primarydonor).
Scenario 3: Fund plus recent gift activity¶
"For a fund statement, show the fund record plus its 20 most-recent gift payments." Two queries (recommended) — simpler and avoids relationship-name hunting.
FUND_ID="..."
# 1. Fetch the fund
curl "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds($FUND_ID)?\$select=akoya_fundname,akoya_fundcode,akoya_netassets,akoya_spendable" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
-H "OData-Version: 4.0" \
-H "OData-MaxVersion: 4.0"
# 2. Fetch recent payments
curl "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_giftpayments?\$select=akoya_amount,akoya_check,akoya_checkdate,akoya_cleardate&\$filter=_akoya_fund_value%20eq%20$FUND_ID&\$orderby=akoya_cleardate%20desc&\$top=20" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
-H "OData-Version: 4.0" \
-H "OData-MaxVersion: 4.0"
fund_id = "..."
# 1. Fetch the fund
r = requests.get(
f"{ORG}/api/data/v9.2/akoya_funds({fund_id})",
params={"$select": "akoya_fundname,akoya_fundcode,akoya_netassets,akoya_spendable"},
headers=headers,
)
fund = r.json()
# 2. Fetch recent payments
r = requests.get(
f"{ORG}/api/data/v9.2/akoya_giftpayments",
params={
"$select": "akoya_amount,akoya_check,akoya_checkdate,akoya_cleardate",
"$filter": f"_akoya_fund_value eq {fund_id}",
"$orderby": "akoya_cleardate desc",
"$top": 20,
},
headers=headers,
)
payments = r.json()["value"]
var fundId = new Guid("...");
// 1. Fetch the fund
var fund = service.Retrieve("akoya_fund", fundId,
new ColumnSet("akoya_fundname", "akoya_fundcode", "akoya_netassets", "akoya_spendable"));
// 2. Fetch recent payments
var paymentsQuery = new QueryExpression("akoya_giftpayment") {
ColumnSet = new ColumnSet("akoya_amount", "akoya_check", "akoya_checkdate", "akoya_cleardate"),
Criteria = {
Conditions = {
new ConditionExpression("akoya_fund", ConditionOperator.Equal, fundId)
}
},
Orders = { new OrderExpression("akoya_cleardate", OrderType.Descending) },
TopCount = 20
};
var payments = service.RetrieveMultiple(paymentsQuery);
When $expand is preferable
A single round-trip with $expand works too — but you need the exact 1:N relationship name (look it up in the Fund reference's Relationships section). Two queries are more robust against relationship-name churn and easier to paginate independently.
Scenario 4: Navigate the fund hierarchy¶
akoya_fund has a self-referential akoya_parent lookup. "Show me all child funds of this parent."
PARENT_ID="..."
curl "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds?\$select=akoya_fundname,akoya_fundcode&\$filter=_akoya_parent_value%20eq%20$PARENT_ID%20and%20statecode%20eq%200&\$orderby=akoya_fundname%20asc" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
-H "OData-Version: 4.0" \
-H "OData-MaxVersion: 4.0"
var parentId = new Guid("...");
var query = new QueryExpression("akoya_fund") {
ColumnSet = new ColumnSet("akoya_fundname", "akoya_fundcode"),
Criteria = {
Conditions = {
new ConditionExpression("akoya_parent", ConditionOperator.Equal, parentId),
new ConditionExpression("statecode", ConditionOperator.Equal, 0)
}
},
Orders = { new OrderExpression("akoya_fundname", OrderType.Ascending) }
};
var children = service.RetrieveMultiple(query);
To traverse upward — walk the parent chain — repeat the pattern for each parent's akoya_parent value, or expand akoya_Parent once per level. Hierarchy depth is arbitrary; deep traversal needs recursive round-trips or a client-side tree-build.
Scenario 5: Find a fund by business code¶
The find-then-do-something pattern — a prerequisite for any update against a fund, since akoyaGO has no alternate keys.
GET {org}/api/data/v9.2/akoya_funds?$select=akoya_fundid&$filter=akoya_fundcode eq 'F-001234'&$top=1
Accept: application/json
OData-Version: 4.0
OData-MaxVersion: 4.0
Then PATCH using the returned GUID:
FUND_ID=$(curl -s "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds?\$select=akoya_fundid&\$filter=akoya_fundcode%20eq%20'F-001234'&\$top=1" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Accept: application/json" \
| jq -r '.value[0].akoya_fundid // empty')
if [ -z "$FUND_ID" ]; then
echo "Fund not found"; exit 1
fi
curl -X PATCH "https://{org}.crm.dynamics.com/api/data/v9.2/akoya_funds($FUND_ID)" \
-H "Authorization: Bearer $ACCESS_TOKEN" \
-H "Content-Type: application/json" \
-H "If-Match: *" \
-d '{"akoya_spendpercent": 4.5}'
r = requests.get(
f"{ORG}/api/data/v9.2/akoya_funds",
params={"$select": "akoya_fundid", "$filter": "akoya_fundcode eq 'F-001234'", "$top": 1},
headers=headers,
)
hits = r.json()["value"]
if not hits:
raise LookupError("Fund not found")
fund_id = hits[0]["akoya_fundid"]
requests.patch(
f"{ORG}/api/data/v9.2/akoya_funds({fund_id})",
json={"akoya_spendpercent": 4.5},
headers={**headers, "Content-Type": "application/json", "If-Match": "*"},
).raise_for_status()
var lookup = new QueryExpression("akoya_fund") {
ColumnSet = new ColumnSet("akoya_fundid"),
Criteria = {
Conditions = {
new ConditionExpression("akoya_fundcode", ConditionOperator.Equal, "F-001234")
}
},
TopCount = 1
};
var hits = service.RetrieveMultiple(lookup);
if (hits.Entities.Count == 0)
throw new InvalidOperationException("Fund not found");
var fund = new Entity("akoya_fund", hits.Entities[0].Id);
fund["akoya_spendpercent"] = 4.5;
service.Update(fund);
Performance tips¶
- Always use
$select. Default is "return every column" — extra bytes, extra serialization, extra columns you might accidentally depend on. - Page in 500–1000-row chunks via
Prefer: odata.maxpagesize=N. Larger pages stress serialization; smaller pages are HTTP overhead. - Push predicates to Dataverse with
$filter— never fetch-then-client-filter. - Cache stable references. GL account GUIDs, fund IDs, and option-set values rarely change; caching them locally cuts round-trips.
- Use change tracking for delta sync. See the Dataverse quickstart FAQ on incremental sync.
Related¶
- Fund reference — full attribute and relationship catalog.
- Dataverse quickstart & FAQ — query-option mechanics and FAQ.
- Query data using the Web API — Microsoft Learn.