D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

Vendtrans and GAE table relation

  • 1.  Vendtrans and GAE table relation

    Posted Jun 11, 2020 10:48 AM
    hello

    any one knows how to relate vendtrans table with general journal account entry table? i have tried to relate it using general journal entry table but the output is not correct
    thanks

    ------------------------------
    Amal Sheikh Hasan
    Danish Refugee Council
    Copenhagen
    ------------------------------
    Academy - Online Interactive Learning from Experts


  • 2.  RE: Vendtrans and GAE table relation

    Posted 28 days ago
    Here is a snippet from my SQL:

    Select * FROM [dbo].[GENERALJOURNALENTRY] AS [GJ]
    INNER JOIN [GENERALJOURNALACCOUNTENTRY] AS [GJA] ON [GJ].[RECID] = [GJA].[GENERALJOURNALENTRY]
    LEFT JOIN [CUSTTRANS] CT ON [GJ].[SUBLEDGERVOUCHER] = [CT].[VOUCHER]
    LEFT JOIN [VENDTRANS] VT ON [GJ].[SUBLEDGERVOUCHER] = [VT].[VOUCHER]

    Not sure if that will help or not, but that is what we have been using for our reports, and Finance hasn't been too annoyed with the report.

    ------------------------------
    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: Vendtrans and GAE table relation

    GOLD CONTRIBUTOR
    Posted 27 days ago
    Hello,

    The query above works to a certain point. I am assuming you are in AX 2012, if you are in D365 there was a new feature that actually shows the customer and vendor name in the voucher transaction form.
    The problem with the query above is that sometimes you can have two different customer/vendor accounts use the same voucherID and in that case the query might return wrong results. in D365 there is a setting to prevent the use of the same voucherid for different customer/vendor accounts.

    regards,

    ------------------------------
    Juan Sebastian Grijalba
    Berkowitz Pollack Brant
    Miami
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: Vendtrans and GAE table relation

    Posted 27 days ago
    I am on 2012. In 365, couldn't you do add the VendTable and select where vendtable is not null? Something like:

    Select * FROM [dbo].[GENERALJOURNALENTRY] AS [GJ]
    INNER JOIN [GENERALJOURNALACCOUNTENTRY] AS [GJA] ON [GJ].[RECID] = [GJA].[GENERALJOURNALENTRY]
    LEFT JOIN [VENDTRANS] VT ON [GJ].[SUBLEDGERVOUCHER] = [VT].[VOUCHER]
    LEFT JOIN [VENDTABLE] VTB ON [VT].[ACCOUNTNUM] = [VTB].[ACCOUNTNUM]
    where VTB.Recid is not null

    You would still have the ability to get incorrect data if you had a customer and vendor with the same number, or when a customer is also a vendor, but you would have a lot less false positives.

    Again, I am on 2012, so I am just guessing if this would work on D365.

    ------------------------------
    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: Vendtrans and GAE table relation

    Posted 26 days ago
    I am using Dynamics FO 365 V.10
    i have tried to link as you mentioned below but the problem is that I get duplicated records on voucher numbers, so the results are not the same when I compare it to data in dynamics.
    not sure how to eliminate the duplicates on vouchers, do you have an idea ?

    thanks



    ------------------------------
    Amal Sheikh Hasan
    Danish Refugee Council
    Copenhagen
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: Vendtrans and GAE table relation

    SILVER CONTRIBUTOR
    Posted 25 days ago
    What if you also joined on Transdate = AccountingDate?
    Hopefully there are reasonably few cases of using the same voucher number that with the date the risk of duplicate results is very small.

    From GeneralJournalEntry As GJE  From GeneralJournalEntry As GJE
    Inner join VendTrans As VT On VT.Partition = GJE.Partition
    And VT.DataAreaId = GJE.SubledgerVoucherDataAreaId
    And VT.Voucher = GJE.SubledgerVoucher
    And VT.Transdate = GJE.AccountingDate

    ------------------------------
    Barclay Hershey
    Financial Analyst
    Sugar Creek Packing Co
    Cincinnati OH
    ------------------------------

    Academy - Online Interactive Learning from Experts


If you've found this thread useful, dive deeper into User Group community content by role