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

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) / Joining CustTrans tabl...
Microsoft Dynamics AX (Archived)

Joining CustTrans table with LedgerJournalTrans table

(0) ShareShare
ReportReport
Posted on by Community Member

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

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 2 on at

    if you will try to macth it using VoucherId that can lead you towards a disaster, as there is possibility of one voucher for whole journal.

    you have to use following relationship to get correct results

    ledgerjournaltrans.CustTransId  == CustTrans.RecId

  • Community Member Profile Picture
    Community Member on at

    I see,however when joined custtransid, i can not get proper joins as custtransid in ledgerjournaltrans tables are mostly zeroes.Why would that be?

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 2 on at

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

  • Community Member Profile Picture
    Community Member on at

    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 :)

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 2 on at

    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 on at

    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 49,668 Super User 2026 Season 2 on at

    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 on at

    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.

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 49,668 Super User 2026 Season 2 on at

    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 on at

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

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

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Women in Power Builds Momentum

Expanding mentorship, skilling, and AI innovation

Congratulations to the July Top 10 Community Leaders

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans