Code to fetch customer balance on basis of asOfdate parameter

(0) Share

Report

Posted on by Nikita Mhamane

Hello,

Can anyone help me, how can we fetch the Customer Balance amount on basis of date like asOfdate.

I have the same question (0)

All responses (8)

Answers (1)

Martin Dráb 236,962 Most Valuable Professional on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

I see that code in your other thread is actually from D365 F&O, not Dynamics AX. Isn't this question about F&O too?

Was this reply helpful? Yes No
Nikita Mhamane 39 on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Thank you for the reply.

Yes ,both the threads are from D365 F&O.

I will move this to finance forum.

Was this reply helpful? Yes No
Hana Xue Microsoft Employee on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hi,
You can check out this link, hope it helps:
https://www.bing.com/ck/a?!&&p=e547ba76030c5f75JmltdHM9MTY5NzU4NzIwMCZpZ3VpZD0yYjA0OWRhMS02YTdjLTYxNzYtMDIzYy04ZWE2NmI3ZDYwZGUmaW5zaWQ9NTI2Ng&ptn=3&hsh=3&fclid=2b049da1-6a7c-6176-023c-8ea66b7d60de&psq=D365+fetch+customer+balance+on+basis+of+asOfdate+parameter&u=a1aHR0cHM6Ly93d3cubGlua2VkaW4uY29tL3B1bHNlL2FkZGluZy1hZ2luZy1kYXRlLWRlLWN1c3RvbWVyLWFnZWQtYmFsYW5jZXMtZDM2NWZvLWh5bGtlLWJyaXRzdHJh&ntb=1
Best Regards,
Hana

Was this reply helpful? Yes No
Nikita Mhamane 39 on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hello hana,

The link is not working for me.

Was this reply helpful? Yes No
André Arnaud de Cal... 300,160 Super User 2025 Season 2 on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hi Nikita,

You can sum all CustTrans records per customer setting a range on the transaction date.

Was this reply helpful? Yes No
Hana Xue Microsoft Employee on at

Like (1)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hi Nikita,
You can refer to the code shown in the screenshot, it may not be accurate, but you can refer to it to get inspiration.
In this code, first specify the "asOfDate" and then select the sum of the field from records for the specified customer () where the transaction date is less than or equal to the "asOfDate."

Best Regards,
Hana

Was this reply helpful? Yes No
Suggested answer

Nikita Mhamane 39 on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hello Everyone,

Thank you for the response!!

I got the solution as -

while select sum(AmountMST) from custTrans
where custTrans.AccountNum == custTable.AccountNum
&& custTrans.TransDate <= balDate
{
BalanceTmp.BalAmount = custTrans.AmountMST; // Total Balance
}
BalanceTmp.OpeningBalance = custTable.openBalanceMST(balDate);

Was this reply helpful? Yes No
Verified answer

Martin Dráb 236,962 Most Valuable Professional on at

Like (0)

Report
Code to fetch customer balance on basis of asOfdate parameter

By the way, there is no need for a while select, because your query won't ever return more than a single record. It summarizes all records together (without any grouping). Therefore your code can be refactored to this:

select sum(AmountMST) from custTrans where custTrans.AccountNum == custTable.AccountNum && custTrans.TransDate <= balDate; balanceTmp.BalAmount = custTrans.AmountMST; // Total Balance balanceTmp.OpeningBalance = custTable.openBalanceMST(balDate);

Was this reply helpful? Yes No