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

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How do I connect MainA...
Finance | Project Operations, Human Resources, ...
Suggested answer

How do I connect MainAccount to PO?

(0) ShareShare
ReportReport
Posted on by Kulsum 115
I have this query I would like to add Mainaccount but I am unsure how I can do that, can someone please help?
 
SELECT DISTINCT
P.createddatetime AS ApprovalDate, P.invoiceaccount AS VendorAccountNumber, D.name as VendorName, P.purchid AS PurchaseOrderNumber, L.itemid AS ItemID, L.name AS ItemDescription, L.purchprice AS Price, L.purchqty AS Quantity, P.documentstate_$label AS State, P.documentstatus_$label AS Status, P.inventlocationid AS Warehouse, P.inventsiteid AS Site,
L.purchprice * L.purchqty AS NetPrice
FROM PURCHTABLE P
JOIN PURCHLINE L ON L.purchid = P.purchid
                AND L.dataareaid = P.dataareaid
                AND L.partition = P.partition 
JOIN VENDTABLE V ON V.accountnum = P.invoiceaccount
JOIN DIRPARTYTABLE D ON D.recid = V.party
WHERE L.dataareaid = 'xxx';
Categories:
Dynamics 365 Finance
I have the same question (0)
  • CU30101134-0 Profile Picture
    CU30101134-0 on at
    How do I connect MainAccount to PO?
    To include the MainAccount in your query, you need to join tables that hold the ledger dimension information. In Dynamics AX or D365FO, the MainAccount is linked to either the purchase line or purchase header through the ledgerdimension field. This field connects to the DIMENSIONATTRIBUTEVALUECOMBINATION table, which contains account structure data. You can extend your query by joining DIMENSIONATTRIBUTEVALUECOMBINATION,     mywisely com DIMENSIONATTRIBUTEVALUESETITEM, DIMENSIONATTRIBUTEVALUE, and MAINACCOUNT tables. The join follows this path: PURCHLINE.ledgerdimension → DIMENSIONATTRIBUTEVALUECOMBINATION → DIMENSIONATTRIBUTEVALUESETITEM → DIMENSIONATTRIBUTEVALUE → MAINACCOUNT. By filtering where DIMENSIONATTRIBUTEVALUE.ISMAINACCOUNT = 1, you can retrieve the main account linked to each purchase line. This approach ensures your results include the MainAccount alongside other purchase order details like vendor, item, quantity, and price.     
  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,972 User Group Leader on at
    How do I connect MainAccount to PO?
    Hi,
    Main account in what sense?
    PO is not a financial document. PO is never posted. Po is expression of interest only (someone wants to buy something).
    Next it goes to confirmation and later it goes to financial aspects (product receipt and invoice) 
    The financial documents have links to Main accounts, not the PO. Even the PO confirmation has nothing to do with Main account.
     
  • Kulsum Profile Picture
    Kulsum 115 on at
    How do I connect MainAccount to PO?
    Like can I get the ledger account to connect to that query using vendinvoicejour and generalaccountentry?
  • Suggested answer
    Alireza Eshaghzadeh Profile Picture
    Alireza Eshaghzadeh 14,637 Super User 2025 Season 2 on at
    How do I connect MainAccount to PO?
    Hi,
    Do you mean the Main account used in voucher transactions for a posted Purchase Order invoice? If so:
    You can find the Main account in the GeneralJournalAccountEntry table.
    Join it with GeneralJournalEntry using:
     
    GeneralJournalAccountEntry.GeneralJournalEntry == GeneralJournalEntry.RecId

    From GeneralJournalEntry, you can retrieve the Voucher ID.
    Using the Voucher ID and JournalCategory, you can then link to VendInvoiceJour, where you will find the Purchase Order number.
  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,972 User Group Leader on at
    How do I connect MainAccount to PO?
    Hi,
    Yes you can the two tables based on relationship of 4 fields. This has been discussed a few times before. take a look here, please:
     
     
    Please note that one PO(PurchTable) can have multiple invoices (VendInvoiceJour) and vice-versa (because of summary parameters). 

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

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

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

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

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 1,771

#2
André Arnaud de Calavon Profile Picture

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

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 542 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans