web
You’re offline. This is a read only version of the page.
close
Skip to main content
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

I have the same question (0)
  • Sohaib Cheema Profile Picture
    Sohaib Cheema 48,798 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.

  • 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,

  • ergun sahin Profile Picture
    ergun sahin 8,824 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

  • Verified answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 48,798 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

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

    Thank you so much sohaib, it helped me.

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

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,157

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 883 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 674 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans