Credit Limit Calculation

[Former Member]

Dear Experts,

I have setup formatted search based on query in sales order document to calculate remaining credit limit for particular customer based on outstanding transaction as per creation date.  This function is working well when user creates document however it's not working (display wrong result) when user updating sales order document.

Question: how to set the value based on mode (Add/Update)?

Please find attach query for your reference.  Thank you.

Best Regards,

Lailus

Query.txt.zip

[Johan_H]

Hi Lailus,

Please take a look at how you set the FMS up. How and when is it triggered?

If you have set it to refresh regularly, then you can set for example the DocTotal field in Auto Refresh When Field Changes.

Please note that this would still not update the query, when for example the user updates only the comment field in the sales order.

In other words, as the trigger you should choose a field that will most likely change when a user updates the sales order.

Regards,

Johan

[Former Member]

Hi Johan,

Thank you for your reply. I have configured it exactly as mentioned by you.  But the problem that I am having now is when user update existing sales order document, the calculation also consider working document (sales order that currently being updated by user).  Please find below example:

a. Customer A credit limit is $1,000

b. Outstanding Invoice is $100

c. Outstanding DO is $0

d. Outstanding SO (SO#2016) is $750

The result that I am expecting when user updates SO#2016 is:

Credit Limit ($1,000) / Balance After Invoice ($900) / Balance After DO ($900) / Balance After SO ($900) / Credit Limit Balance ($150)

NOT

Credit Limit ($1,000) / Balance After Invoice ($900) / Balance After DO ($900) / Balance After SO ($150) / Credit Limit Balance (-$600)

Is that possible?

Regards,

Lailus

[Johan_H]

Hi Lailus,

That should be possible. You can adapt the query to not consider the working document (SO#2016).

just add this to the WHERE clause:

AND NOT DocNum = $[ORDR.DocNum]

Regards,

Johan

P.S. I have not looked at your query, so you may have to tweak the line above a little.