Is there a standard data entity that exposes Customer ID and Current Balance in D365FO?

(2) Share

Report

Posted on by HS-15061155-0

Hello everyone,

I'm working on a requirement where we need to send SMS messages to customers exposing their phone number and current balance.

To achieve this, I would like to expose the data via OData, and I’m looking for a standard data entity (if available) that includes:

Customer account number (Customer ID)
Current balance (open balance or total due amount)

I’ve checked entities like CustomerV3 to get the customer contact info, but I’m not sure which one (if any) provides a reliable real-time customer balance.

Is there a standard entity that contains this data?

Or do I need to create a custom entity?

I find a entity named Customer aging data storage but it's not public!

Any guidance or suggestions would be appreciated.

Thanks in advance!

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (10)

Answers (2)

Suggested answer

Jonas "Jones" Melgaard 4,988 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

I'm 99.99% certain there is no way to get the balance using a simple OData query against an entity. You have some BI entities, but those ones are not available using oData.

You need to make a custom solution. I'm thinking making a batch job that fills out a table with the customer balances.

Alternatively you have the CustTransOpen entity; I haven't tested it, but I wonder if it's possible to make an aggregate query against it. That way you can get the balance. https://learn.microsoft.com/en-us/power-apps/developer/data-platform/webapi/query/aggregate-data#sum-of-revenue-based-on-status

I've done something similar against a D365 CE database.

Was this reply helpful? Yes No
Suggested answer

Navneeth Nagrajan 2,438 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi HS15061155-0,

For customer contact details like phone number, email addresses etc. there is a standard data entity called Party Contacts V2 and Party Contacts V3 (DirPartyContactV2Entity and DirPartyContactV3Entity).

For customer balances the closest one as you rightly said is the CustAgingAIEntity and/or CustAgingEntity. CustAgingEntity has a public entity name called CustAgedBalance. Similarly, CustAgingAIEntity has public entity name called CustAIAgedBalance.

Suggestions:

1. You can extend CustAgingEntity and add a range to include open transactions balances(based on the requirement - if closed transaction are not considered) because the Customer balances screen displays open customer transaction balance.

In CustAgingEntity or CustAgingAIEntity you can extend and add CustTransOpen table and join it with the CustTable.

.

Note: CustAgingAIEntity is available through post 10.0.40 update.

2. Reporting Entities: From the BI Entities/Entity store based entity side you can also leverage Customer transactions (CustCollectionsBICustTrans), Aged Balances for Customers (CustCollectionsBIAgedBalances) or Customer Collections History (CustCollectionsHistoryEntity).

Used the CustCollectionsBICustTrans and its a very handy one. Alternatively, you can also use Aged Balances for Customers (CustCollectionsBIAgedBalances) too. These two entities do not have Public collection names or Public entity name but have most of the fields that you are looking for to address the requirement.

If you are looking to combine phone numbers and customer balances under one entity then you will need a custom entity. Alternatively, as Martin mentioned you can use Odata actions to populate data from multiple sources into one aggregate view or table and then retrieve the data.

Hope this helps. Happy to answer questions, if any.

Was this reply helpful? Yes No
Martin Dráb 238,030 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Note that it doesn't necessarily have to be an OData query against an entity. You could also create an OData action.

Was this reply helpful? Yes No
Verified answer

HS-15061155-0 19 on at

Like (0)

Report
Copy link

Link copied!
Thank u all for help

I'm looking for a way to expose customer balance data in JSON or XML format without creating a new data entity beacause as I can see there's no clear entity that have the a the balance for each customer.

So I move on to develop a custom service that returns a list of customers with the following fields:

Name

Phone

Balance

Only customers with a non-zero balance should be included.

The expected response format should be something like:

json

[ { "AccountNum": "US-001", "Name": "Contoso Ltd", "Phone": "+123456789", "Balance": 1200.50 }, { "AccountNum": "US-002", "Name": "Contoso Ltd1", "Phone": "+123456780", "Balance": 200 } ]

I initially thought that custom services in D365FO were mainly used to receive external requests (like POST/GET into FO). But in this case, I want to expose a custom GET API that allows external systems to retrieve this list as a full JSON array.

I also want to avoid using the CustTrans entity because of the data volume — I’m only interested in summarizing the balance per customer, not detailed transactions.

Is this approach possible using a custom service? And if so, what’s the recommended way to return multiple records in the response?

Was this reply helpful? Yes No
Verified answer

Anton Venter 20,346 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!
Hi,

If you decide to go with the custom service solution, then you should definitely do it like this for maximum performance, see the steps below. I've done this before but for general journal entries and it performs well. In any case, you should however limit the amount data returned by the service and not simply let it run for all customers.

High level steps for custom service:

Use a temporary table and populate this temp. table using set based operations/statements, not row based operations. In other words, using insert_recordset....select from.....

Using a while select statement in your custom service, select the temp. data and return the data as a collection data type.

Was this reply helpful? Yes No
HS-15061155-0 19 on at

Like (0)

Report
Copy link

Link copied!

I managed to get it working using custom services. I returned the data as a list of a custom result contract class, which was successfully converted to JSON.

However, I’ve run into a limitation: the list currently only returns 5,332 records, while I need to handle over 8,000.

Is there a recommended approach to handle larger datasets in this scenario? Would paging the results be a viable solution, and if so, how can I implement it effectively within the custom service?

Any insights or best practices would be greatly appreciated!

Was this reply helpful? Yes No
Suggested answer

Anton Venter 20,346 Super User 2025 Season 2 on at

Like (0)

Report
Copy link

Link copied!

How long does your service take to return the 5332 records? If it's taking a while, perhaps it's timing out? How much data is the service returning? Is it a lot or too much? Perhaps you can reduce the data to make it leaner?

In my opinion, your service should not return all the customers with their balances in one call. I would design the service with a mandatory argument / parameter to limit the amount of data returned by the service. E.g. customer account and perhaps you can design the service to work with ranges like D365FO does. The caller of the service can then split the 8000 customers into smaller "chunks".

If you want all the customers in one call, why not create a data entity and add the balance information to the data entity and then export the data with Data Management Framework? You can configure an endpoint and get the data as file from D365FO in one call.

Was this reply helpful? Yes No
Jonas "Jones" Melgaard 4,988 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

5,332 sounds like a odd number, are you 100% sure your are trying to send more than 8000 rows? Either using the debugger or including a counter.

A timeout error will return another error and no result, so it's not that.

Was this reply helpful? Yes No
HS-15061155-0 19 on at

Like (0)

Report
Copy link

Link copied!

I'm sorry my bad .. it's working well thank you all

Was this reply helpful? Yes No
Jonas "Jones" Melgaard 4,988 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

This is odd indeed... Collections in X++ should not have any limits, other than the resources on the server, and 8000+ items in a list is nothing if it's only simple balance information. For example, OData can return 10.000 records at a time, then you can query it so it's returning the next 10.000 records.

Is it the same if you run it in a job?

EDIT: Saw your edited reply, good to hear that it's working (-:

Was this reply helpful? Yes No