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,188 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,253 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,188 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

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Leaderboard

Featured topics

Product updates

Code to fetch customer balance on basis of asOfdate parameter

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Top 10 leaders for November!

Tips for Writing Effective Suggested Answers

Subscribe to

Leaderboard

Featured topics

Product updates