Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

Posted on by 1,155

Does anyone know a unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry?

We want to display the related vendor or customer (if there is one) in form “voucher transactions” (LedgerTransVoucher) as a display method column.

I found a solution to display the wanted values by linking the records in the same way the button “Customer or vendor transactions” in from LedgerTransVoucher does. But this solution only works till there is more than one CustTrans/VendTrans involved.

Any other ideas?

*This post is locked for comments

  • Arti007 Profile Picture
    Arti007 60 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Hi All,

    Please let me know if you have any way to get 1:1 relation between GENERALJOURNALACCOUNTENTRY and CUSTINVOICETRANS table. I am new to dynamics.

    I used the below query but I am getting multiple lines if I have more than one line for same sales order.

    select gae.*,CIT.ITEMID

    from GENERALJOURNALACCOUNTENTRY GAE,

    GENERALJOURNALENTRY GE,

    SUBLEDGERVOUCHERGENERALJOURNALENTRY SUB,

    CUSTINVOICEJOUR CIJ,

    CUSTINVOICETRANS CIT,

    CUSTPACKINGSLIPJOUR CPJ

    where GAE.GENERALJOURNALENTRY=GE.RECID

    AND sub.GENERALJOURNALENTRY=GE.RECID

    AND sub.VOUCHERDATAAREAID=GE.SUBLEDGERVOUCHERDATAAREAID

    and (CIJ.LEDGERVOUCHER=SUB.VOUCHER or CPJ.LEDGERVOUCHER=SUB.VOUCHER)

    AND CIJ.DATAAREAID=SUB.VOUCHERDATAAREAID

    AND CIJ.SALESID=CPJ.SALESID

    AND CIJ.DATAAREAID=CPJ.DATAAREAID

    AND CIT.INVOICEID=CIJ.INVOICEID

    AND CIT.DATAAREAID=CIJ.DATAAREAID

  • alexd Profile Picture
    alexd 457 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    This will work as long your Voucher Number rule for New Vouchers is set to "In connection with Balance".

    Check GL>Setup>Journals>Journal Names>General>Voucher Number>New Voucher

  • Suggested answer
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    ;)

    SELECT TOP 1 GJE.*, VTR.*, LJTR.*

    FROM GENERALJOURNALACCOUNTENTRY GJAE

    LEFT OUTER JOIN GENERALJOURNALENTRY GJE ON GJAE.GENERALJOURNALENTRY = GJE.RECID

    LEFT OUTER JOIN DIMENSIONATTRIBUTELEVELVALUEVIEW DALVV ON GJAE.LEDGERDIMENSION = DALVV.VALUECOMBINATIONRECID

    LEFT OUTER JOIN LEDGERENTRY LE ON GJAE.RECID = LE.GENERALJOURNALACCOUNTENTRY

    LEFT OUTER JOIN LEDGERENTRYJOURNAL LEJ ON GJE.LEDGERENTRYJOURNAL = LEJ.RECID

    LEFT OUTER JOIN LEDGERJOURNALTABLE LJT ON LEJ.JOURNALNUMBER = LJT.JOURNALNUM

    LEFT OUTER JOIN VENDTRANS VTR ON GJE.SUBLEDGERVOUCHER = VTR.VOUCHER AND GJE.DOCUMENTDATE = VTR.DOCUMENTDATE

    LEFT OUTER JOIN CUSTTRANS CTR ON GJE.SUBLEDGERVOUCHER = CTR.VOUCHER AND GJE.DOCUMENTDATE = CTR.DOCUMENTDATE

    LEFT OUTER JOIN LEDGERJOURNALTRANS LJTR ON GJE.SUBLEDGERVOUCHER = LJTR.VOUCHER AND GJE.ACCOUNTINGDATE = LJTR.TRANSDATE

    LEFT JOIN DIMENSIONATTRIBUTE DA ON DA.RECID = DALVV.DIMENSIONATTRIBUTE

  • Genuine Microsoft Profile Picture
    Genuine Microsoft 305 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    HI,  How can I update the "QUANTITY" in GENERALJOURNALACCOUNTENTRY Table ?    I need to update the QUANTITY field of GENERALJOURNALENTRY Table  with the  total of PURCHQTY of PURCHLINE  while posting the Purchase invoice.  Which function do I need to modify to achieve this. 

    Thanks Hillary 

  • Kar_Gilson Profile Picture
    Kar_Gilson 410 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Any updates on this please?

  • BartF Profile Picture
    BartF 5 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Hi Paul,

    I have the same problem , did you get this fixed finally ? If yes, how did you solve it ?

    Thanks in advance,

    Bart

  • dolee Profile Picture
    dolee 11,279 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Hi Paul,

    Yes, to my understanding it should be 1:n relationship if from CustTrans/VendTrans to GeneralJournalAccountEntry.

    But since your requirement is showing Vendor/Customer in the Voucher transaction form, Wouldn't this do (each GeneralJournalAccountEntry will find either one or no Cust/Vend account)?

    Dom

  • Paul Heisterkamp Profile Picture
    Paul Heisterkamp 1,155 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Hey Dolee,

    thx for your help, but a already know this connection and it is not what I am looking for. This connection will end in an 1:n realtion from CustTrans/VendTrans to GeneralJournalAccountEntry.

    Paul

  • dolee Profile Picture
    dolee 11,279 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Hi,

    GeneralJournalEntry tables links to SubledgerGeneralJournalEntry table by the "GeneralJournalEntry" field in SubledgerGeneralJournalEntry table.

    Then, search for Vend/CustTrans based on the "voucher", "voucherDataAreaId" and "AccountingDate" (Matches "voucher", "dataareaid" and "transdate").

    See if the result is what you expect.

  • Paul Heisterkamp Profile Picture
    Paul Heisterkamp 1,155 on at
    RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

    Version is 2012 R2 CU6

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templates⚡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,232 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,064 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans