Joining CustTrans table with LedgerJournalTrans table

(0) Share

Report

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

All responses (12)

Answers (0)

LOE 35 on at

Like (0)

Report
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 Microsoft Employee on at

Like (0)

Report

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

Like (0)

Report

RE: Joining CustTrans table with LedgerJournalTrans table

ok sir,thank you for your help,ill keep digging :)
Sohaib Cheema 46,610 User Group Leader on at

Like (2)

Report

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

Like (0)

Report

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 46,610 User Group Leader on at

Like (1)

Report

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

Like (0)

Report

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 46,610 User Group Leader on at

Like (0)

Report

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

Like (0)

Report

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 46,610 User Group Leader on at

Like (0)

Report

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?