How to retrieve data from LedgerJournalTrans table (based on relations and joins) for voucher transaction form in D365FinOp?

Question

Basically I have a client request to implement: Need to show data from the following fields : PaymMode, BankChequeNum, LedgerDimensionName, JournalNum from ledgerJournalTrans table on the LedgerTransVoucher form but have been unable to do so have tried nearly all of the possible queries that I could think of but none of them are working the way I expect them to: either the query is doing a cartesian product and duplicating the records or it is displaying no data in those fields. Below is the query that I have recently tried:

public display MH_AccountTitle displayBeneficiaryName(GeneralJournalAccountEntry _accountEntry) 

{ 
    select SubledgerVoucher, AccountingDate from journalEntry 
        where journalEntry.RecId == _accountEntry.GeneralJournalEntry 
            join Voucher, MH_AccountTitle, RecId, AmountCurDebit, AmountCurCredit, TransDate from LedgerTrans  
                where LedgerTrans.Voucher == journalEntry.SubledgerVoucher 
                && LedgerTrans.TransDate  == journalEntry.AccountingDate 
                && LedgerTrans.PaymReference == _accountEntry.PaymentReference 
                && (abs(_accountEntry.TransactionCurrencyAmount) == LedgerTrans.AmountCurDebit 
                || abs(_accountEntry.TransactionCurrencyAmount) == LedgerTrans.AmountCurCredit); 

    return ledgerTrans.MH_AccountTitle; 

}

I know this query is logically incorrect because joins can't be applied on the basis of date and amount but this was suggested by a senior of mine after all else failed, and it did work, records were returned correctly but it failed where there were multiple transactions with same TransactionCurrencyAmount,TransDate and voucher join with PaymentReference also failed where the method of payment was not Check and hence there was no BankChequeNum/Payment reference resulting in the same problem

Anyone who has any idea of what could be work around for this?

Note: work has been done on a custom form of LedgerTransVoucher display method approach was used because simply applying joins on the form's data source didn't work Also code has been written on form's datasource GeneralJournalAccountEntry

Does this answer your question? [Relationship between GeneralJournalAccountEntry and LedgerJournalTrans\_Project](https://stackoverflow.com/questions/28965170/relationship-between-generaljournalaccountentry-and-ledgerjournaltrans-project) — FH-Inway, Jul 19 '22 at 15:11
Also take a look at https://community.dynamics.com/365/financeandoperations/f/dynamics-365-for-finance-and-operations-forum/425467/relationship-join-between-generaljournalaccountentry-and-trans-tables — FH-Inway, Jul 19 '22 at 15:11

score 0 · Answer 1 · edited Aug 08 '22 at 10:01

What I usually do to debug these kinds of issues is get the query string that X++ creates when using inline SQL. Often there is something that X++ translates into SQL that is not expected.

Secondly, I always write my queries in SSMS first to avoid having unwanted results because of the X++ translation to SQL query.

You can get the query string by either making a query object and using the .ToString() method or you can take a trace and use Traceparser to view the query that was sent to the SQL Server.

I am thinking the abs() functions will be the issue here.

How to retrieve data from LedgerJournalTrans table (based on relations and joins) for voucher transaction form in D365FinOp?

1 Answers1