Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Relation between CustI...
Finance | Project Operations, Human Resources, ...
Answered

Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

(0) ShareShare
ReportReport
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

  • Community Member Profile Picture
    Community Member on at
    RE: Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

    Thank you so much sohaib, it helped me.

  • Verified answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 46,614 User Group Leader on at
    RE: Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

    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

  • ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on at
    RE: Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

    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

  • Community Member Profile Picture
    Community Member on at
    RE: Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

    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,

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,614 User Group Leader on at
    RE: Relation between CustInvoiceJour, CustTrans and GeneralJournalEntry

    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.

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,099 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,880 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans