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.

All responses (8)

Answers (1)

Verified answer

Martin Dráb 230,414 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);
Suggested answer

Nikita Mhamane 23 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);
Hana Xue Microsoft Employee on at

Like (0)

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
André Arnaud de Cal... 291,684 Super User 2024 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.
Nikita Mhamane 23 on at

Like (0)

Report

Code to fetch customer balance on basis of asOfdate parameter

Hello hana,

The link is not working for me.
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
Nikita Mhamane 23 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.
Martin Dráb 230,414 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?

Code to fetch customer balance on basis of asOfdate parameter

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Meet the Top 10 leaders for December!

Get Started Blogging in the Community

Leaderboard

Featured topics

Product updates

Code to fetch customer balance on basis of asOfdate parameter

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Meet the Top 10 leaders for December!

Get Started Blogging in the Community

Subscribe to this forum!

Select categories

Leaderboard

Featured topics

Product updates