Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

(0) Share

Report

Posted on by Community Member

Hi all,

Can you please let me know the relation between custinvoiceJour, CustTrans and GeneralJournalEntry tables.

I am able find the relation between custinvoicejour and custtrans but unable to find any direct relation between custTrans and GeneralJournalentry.

Tried to establish the relation as shown below but i am getting duplicate records

query = new Query();
qbds = query.addDataSource(TableNum(CustInvoiceJour));

qbdsCTrans = qbds.addDataSource(tableNum(CustTrans));
qbdsCTrans.joinMode(JoinMode::InnerJoin);
qbdsCTrans.relations(true);
qbdsGTrans = qbdsCTrans.addDataSource(tableNum(GeneralJournalEntry));
qbdsGTrans.joinMode(JoinMode::OuterJoin);
qbdsGTrans.relations(false);
qbdsGTrans.addLink(fieldNum(CustTrans,Voucher),fieldNum(GeneralJournalEntry,SubledgerVoucher));
qbdsGTrans.addLink(fieldNum(CustTrans,DataAreaId),fieldNum(GeneralJournalEntry,SubledgerVoucherDataAreaId));

can anyone provide the proper way of establishing the relation between these tables.

Thanks in advance

I have the same question (0)

All responses (5)

Answers (1)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

Hi,

Do you mind if I suggest you a way to find this information, instead of doing it for you?

If you will take any invoice, you would want to click the voucher button. Once done that, you will have posted voucher entries, against the invoice. on that form where you see the posted voucher, right click the form and look at datasources information.

The relationship between invoice and posted voucher is based upon three fields. and the logic is written in x++ that gets executed on loading of the form. you can see that in init method.

hope that helps you to have a look at code.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Sohaib,

Actually i am a newbie to Dynamics, i referred the mentioned form and init method but i am unable to understand the code properly. Can you let me, how can i achieve the relation or refer me any content that helps me to get basic understanding of relationship between vouchers, invoices and related transactions.

Thanks & Regards,

Was this reply helpful? Yes No
ergun sahin 8,826 Moderator on at

Like (0)

Report

If you are looking for a 'one on one' relationship, there are no guarantees for that.

Check out the discussions and answers in this topic

community.dynamics.com/.../relationship-between-generaljournalaccountentry-and-ledgerjournaltrans

Was this reply helpful? Yes No
Verified answer

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report
Hi here is some work I did for you by spending few minutes, i hope that should be okay for you to understand the relationships now between the tables.

-- sample code by sohaib cheema for community purpose only -- please change the data area id and invoice id in the query to test the voucher entries for the posted invoice --get current ledger, in x you will use Ledger::Current declare @varCurLedger as bigint set @varCurLedger = (select ledger.RecId from ledger join COMPANYINFOVIEW on ledger.PrimaryForLegalEntity = COMPANYINFOVIEW.RecId where COMPANYINFOVIEW.DATAAREA = 'TEE') --make sure to put your own data area you can get that from current company ComapnyInfo::fin() in x --# Get GL data for invoice SELECT GeneralJournalEntry.JournalNumber,GeneralJournalEntry.SubledgerVoucher,GeneralJournalEntry.AccountingDate, GeneralJournalAccountEntry.LedgerAccount,GeneralJournalAccountEntry.TransactionCurrencyCode, GeneralJournalAccountEntry.ACCOUNTINGCURRENCYAMOUNT,GeneralJournalAccountEntry.REPORTINGCURRENCYAMOUNT,GeneralJournalAccountEntry.TRANSACTIONCURRENCYAMOUNT FROM GeneralJournalEntry JOIN GeneralJournalAccountEntry ON GeneralJournalEntry.RecId = GeneralJournalAccountEntry.GeneralJournalEntry JOIN DimensionAttributeValueCombination ON DimensionAttributeValueCombination.RECID = GeneralJournalAccountEntry.LEDGERDIMENSION Join MainAccount On MainAccount.RECID = DimensionAttributeValueCombination.MAINACCOUNT Join FiscalCalendarPeriod on FiscalCalendarPeriod.recid = GeneralJournalEntry.FISCALCALENDARPERIOD join SubledgerVoucherGeneralJournalEntry on SubledgerVoucherGeneralJournalEntry.GENERALJOURNALENTRY = GeneralJournalEntry.RECID join CustInvoiceJour on CustInvoiceJour.LEDGERVOUCHER = SubledgerVoucherGeneralJournalEntry.VOUCHER and CustInvoiceJour.INVOICEDATE = SubledgerVoucherGeneralJournalEntry.ACCOUNTINGDATE where GeneralJournalEntry.LEDGER = @varCurLedger -- you can use Ledger::current in x and CustInvoiceJour.INVOICEID = 'SINV-000330' -- sample for one invoice only you can use range or not based on needs

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you so much sohaib, it helped me.

Was this reply helpful? Yes No