Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Joining CustTrans tabl...
Microsoft Dynamics AX (Archived)

Joining CustTrans table with LedgerJournalTrans table

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hello,

I'm trying to join these two tables, but couldnt find a proper correlation between though.Vouchers are different, custtransid in ledgerjournaltrans table is most of the time 0. What are the fields to use for a join query between these two?

Regards

*This post is locked for comments

  • LOE Profile Picture
    LOE 35 on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    Hi!. you can try this maybe

    select ledgerJournalTrans
where ledgerJournalTrans.Voucher == ledgerJournalTransVoucherParameter
join custTrans
where custTrans.Voucher == ledgerJournalTrans.Voucher;
  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    Hi Sin5k4,

    Did you solve that problem with 0´s in the field CustransId in the ledgerjournaltrans table ? because I have the same problem, I am developer and I did the payment over the invoice but I can´t match the tables, if you can answer me, will be a great helpfully.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    ok sir,thank you for your help,ill keep digging :)

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    okay gentleman, I cannot help you anymore on this. Maybe somebody else can review you data and setup and suggest you something better.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    Yeah,there is a payment for that in my custtrans table.But there isnt a relation between those two even though i see that specific payment in ledger transactions.However both are not connected through custtransid.

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    no not a business logic error!

    if you are expecting NumberOfRecordsinLedgerTrans == NumberOfRecordsInCustTrans

    you are EXPECTING WRONG!

    If I create an SO Invoice for a customer, transactions directly goes to CustTrans Table only. Nothing goes in ledgerTrans till here.

    Its has nothing to do with LedgerJournalTrans unless you receive a payment for this customer or you do some other settlement!

    So there can be difference between number of records in both tables

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    Just checked my LEDGERJOURNALTRANS  table, and in about 130sih thousand records, only 7000 of them has the custtransid field that is not zero.Could be a bussiness logic error...

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    I am not sure about your environment and data, but all I can say that I am 100% sure about relationship which I told you in my 1st reply.

    Kindly try to use following sql query instead of how you are using:

    select * from dbo.LEDGERJOURNALTRANS as L

    join CUSTTRANS C on l.CUSTTRANSID = C.RECID

    WHERE C.VOUCHER= 'SFT0001008'

    If above query is not returning results, too, try following

    select c.VOUCHER,* from dbo.LEDGERJOURNALTRANS as L

    join CUSTTRANS C on l.CUSTTRANSID = C.RECID

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    Not actually, i picked a voucher from custtrans, using that i get the custtransid value. as in:

    SELECT * FROM dbo.LEDGERJOURNALTRANS AS L WHERE L.CUSTTRANSID = (SELECT c.RECID FROM dbo.CUSTTRANS AS C WHERE C.VOUCHER= 'SFT0001008')

    This gets me 0 results because  custtransid fields in LEDGERJOURNALTRANS table are zeroes....

    (sorry for the sql :)

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: Joining CustTrans table with LedgerJournalTrans table

    you mean when you tried to join by vouchernum you got no results?? that is what you are asking about?

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!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans