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 / Microsoft Dynamics AX (Archived) / Displaying SalesOrderB...
Microsoft Dynamics AX (Archived)

Displaying SalesOrderBalance field from VendPurchOrderJour

(0) ShareShare
ReportReport
Posted on by oldman.logan 565

Hi. I am trying to display the SalesOrderBalance field from table VendPurchOrderJour in a report. 

The report gives a list of all purchase orders with the Amount before discount and tax (SalesOrderBalance) and Amount. 

For some reason, the SalesOrderBalance does not appear correctly in the report. The SalesOrderBalance for a particular PO shows the amount from the previous PO instead of the current one on the list. 

For Ex: PO 2322 -Actual SalesOrderBalance = USD 2000

     PO 2323 - Actual SalesOrderBalance = USD 1723

     PO 2324 - Actual SalesOrderBalance = USD 1989


The report displays the salesorderbalance as below

PO 2323 - USD 2000

PO 2324 - USD 1723 


Currently the SalesOrderBalance field is being read directly from the table VendPurchOrderJour. Checking the table VendPurchOrderJour shows the correct SalesOrderBalance value against the corresponding PO number. Any support is appreciated. 

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ravikumar Profile Picture
    Ravikumar 325 on at

    Hi Logan,  In Vendpurchorderjour PurchaseOrderId field is unique for a PO, so you can sort by desending (dsc) to get latest confirmed details.

    select vendPurchOrderJour

               order by PurchaseOrderId dsc

               where vendPurchOrderJour.PurchId == _purchId;

  • oldman.logan Profile Picture
    oldman.logan 565 on at

    Thank you Ravikumar. But I only need the balance from the last PO revision on this report. Right now, I am getting the report with all revisions of the PO in descending order.

  • Verified answer
    Ravikumar Profile Picture
    Ravikumar 325 on at

    can you share your code.  

    if you use the below select statement you will get latest PO confirmation details.

    select vendPurchOrderJour

              order by PurchaseOrderId desc

              where vendPurchOrderJour.PurchId == _purchId;

  • Verified answer
    oldman.logan Profile Picture
    oldman.logan 565 on at

    Hi.

    This is my code.

    display real SalesOrderBalance1()

    {

    select VendPurchOrderJour

    order by PurchaseOrderId desc

    where VendPurchOrderJour.PurchId==PurchTable.PurchId;

    return VendPurchOrderJour.SalesOrderbalance;

    }

    I'm using PurchTable.PurchId since I'm displaying the PurchId from PurchTable.

  • Suggested answer
    Ravikumar Profile Picture
    Ravikumar 325 on at

    the above one is correct, kindly check your report logic or share your code in ProcessReport method.

  • oldman.logan Profile Picture
    oldman.logan 565 on at

    I just restarted the AX service and it started working now...... :D Thanks!

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 > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans