web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Customers over credit ...
Finance | Project Operations, Human Resources, ...
Unanswered

Customers over credit limit

(0) ShareShare
ReportReport
Posted on by ChosenSilver 50

I am very new to D365 development and I am struggling a lot with forms development when the Data Sources are complex. It certainly does not help that I am not really familiar with any of the tables where information is stored so even completing simple requests has been an issue.

Currently I have been tasked with creating a custom list that shows all the Customers that have exceeded their Credit Limit (Balance + Open Orders > Credit Limit). From the poking around I have been doing I believe the info is going to come from the CustTable, CustTrans and CustTransOpen tables, but the numbers need to be summed per AccountNum.

Does anyone know if those are the correct tables, and what is the method to get the summed data and display it? I am assuming I'll need to write some x++ code to make this happen but I am not exactly sure where.

On the other hand, where this is such a seemingly simple request maybe I am overlooking something very obvious here. In all my Googling I can never seem to find any examples of anyone doing anything similar

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,069 Super User 2025 Season 2 on at

    Hi ChosenSilver,

    You found the storage in the correct tables. The balance can be calculated without the CustTransOpen table. There are some display methods on the custTable which is having the calculation for the open balance already (e.g. balanceMST). When using display methods, you can't filter on these columns. I haven't checked all methods, but a calculation with credit limit minus the balance might not exists.

    To be able to filter on the balance and the credit limit overrun, you can consider creating a temporary table and fill the details when opening your new form. You can then evaluate if the customer exceeds the credit limit and insert only those records in the temporary table. If you have a very large number of records, it might take a while to load the data.

  • ChosenSilver Profile Picture
    ChosenSilver 50 on at

    Sorry for the really basic questions, but are you able to point me at any examples where this sort of thing is being done? MS Learn does not seem to cover anything but the most very basic scenarios.

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 301,069 Super User 2025 Season 2 on at

    Hi ChosenSilver,

    There are examples where data will be put in a temporary table. E.g. have a look at the inquiry forms Trial balance and Accounting source explorer. You can find these in the General ledger menu.

  • huijij Profile Picture
    huijij 19,811 on at

    Hi ChosenSilver,

    You can  consider creating a temporary table and fill the details when opening your new form,

    and you can use a stored procedure to insert some data into a temporary table

    so that you can create custom list using the table for display.

    CREATE PROCEDURE testcustomerpro 
    @customer varchar(50),
    @balance int,   
    @OpenOrders INT,
	@CreditLimit INT 
AS  
begin 
   select ...from CustTable...where...
   ---calculation with credit limit minus the balance into field @CreditLimit---
	INSERT INTO testXXX (customer,balance, OpenOrders, CreditLimit) 
    values(@customer, @balance,@OpenOrders,@CreditLimit)
end;

EXEC testcustomerpro;

    https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/create-a-stored-procedure?view=sql-server-ver15

    regard,

    Please mark as verified if the answer is helpful. Welcome to join hot discussions in Dynamics 365 Forums.

  • ChosenSilver Profile Picture
    ChosenSilver 50 on at

    I started to build what you have suggested, but then a thought occurred to me. If this is using a "temp" table, which is really just a physical table, then won't it cause issues if multiple people are going to the form and the table keeps getting emptied and rebuilt?

    I assume it obviously works fine for the forms you suggested I look at, but I don't exactly understand why.

  • ChosenSilver Profile Picture
    ChosenSilver 50 on at

    I am still having issues with this. I experimented with creating a physical "temp" table, and aside from being very slow to load the data onto the form it has the drawback that if multiple people are trying to go to the same form the table is going to keep getting emptied and filled again in the background.

    To make matters slightly more complicated, this data needs to be populated with a Query as it is also being shown as a count on a tile in a custom workspace. I ended up making a method on the table to populate the data and call the method from within init() method of the Query. It takes over a minute to populate only about 300 accounts using this method.

    I can very easily create a view in SQL that pulls all the data I want and it runs in just a second or two. I then focused my efforts into making a View in AOT to do the same, but it seems like there is no way to do maths in the HAVING clause in a View object (that I can see anyway). I am posting my SQL query below in the hopes that you might be able to provide any insight into how I can properly build a query to show this data on a form (and tile) with acceptable performance ( < 5s to display)

    select CUSTTABLE.ACCOUNTNUM, sum(CUSTTRANS.AMOUNTMST), sum(CUSTTRANS.SETTLEAMOUNTMST), CUSTTABLE.CREDITMAX
  from CUSTTABLE
    inner join CUSTTRANS on CUSTTRANS.ACCOUNTNUM = CUSTTABLE.ACCOUNTNUM and CUSTTRANS.DATAAREAID = CUSTTABLE.DATAAREAID
group by CUSTTABLE.ACCOUNTNUM, CUSTTABLE.CREDITMAX
having sum(CUSTTRANS.AMOUNTMST) - sum(CUSTTRANS.SETTLEAMOUNTMST) > CUSTTABLE.CREDITMAX
order by CUSTTABLE.ACCOUNTNUM

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 592 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 478 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 305 Most Valuable Professional

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans