Relationship between GeneralJournalAccountEntry and LedgerJournalTrans

(0) Share

Report

Posted on by Community Member

I'm designing a report that requires data from both the GeneralJournalAccountEntry and LedgerJournalTrans tables, but I can't seem to find any way to create a one-to-one relationship between the two tables. I've looked at potential relationships directly between the two tables and through the GeneralJournalEntry and LedgerJournalTable tables, but I always wind up with a Cartesian Join at the voucher level between the GeneralJournalAccountEntry and LedgerJournalTrans. Does anyone know if there is anyway to define a relationship between these two tables? Seems like there should be, but I can't seem to find it.

Thanks,

*This post is locked for comments

I have the same question (4)

All responses (12)

Answers (0)

DavidGunawan 1,381 on at

Like (0)

Report

Hi RickC,

There is a middleman between these two tables. You need to join through table GeneralJournalEntry when you make query join between these two tables. Hope this can help you.

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

There is not always a 'one-to-one' relationship. The closest you can get is to join

LedgerJournalTrans to GeneralJournalEntry on Voucher = SubLedgerVoucher and TransDate = AccountingDate then Join GeneralJournalAccountEntry on GeneralJournalEntry.RecId = GeneralJournalAccountEntry.GeneralJournalEntry

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi David,

Thanks for the reply. I was able to join to the tables through the GeneralJournalEntry table, but still couldn't establish a one-to-one relationship (or one-to-two if offset accounts are used) between the LedgerJournalTrans and GeneralJournalAccountEntry tables. I'm not sure it can even be done.

Was this reply helpful? Yes No
Suggested answer

DavidGunawan 1,381 on at

Like (0)

Report

Hi RickC,

As I know, It's not possible to get one to one relationship between these tables. The business reason is GeneralJournalAccountEntry stores accounting transactions (debit and credit). So it will always at least one couple transaction for one journal (LedgerJournalTable).

I suggest changes your report to header and line layout.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Osama,
Thanks for the reply. The issue that I run into is that my client doesn't use Offset Accounts and actually generates vouchers with multiple lines, sometimes 12-15 debits to a single credit, for example. When I try to join based on the Voucher Number and Data Area ID, I wind up with a Cartesian join at the voucher level. Basically, joining a 7 line voucher through the GeneralJournalEntry table based on DataAreaID and Voucher creates a 49 record result set.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi David,

I may have been over-simplifying my question with the 'one-to-one' relationship. Basically, I'm trying to trace a GeneralJournalAccountEntry record back to the original LedgerJournalTrans record used to create it. Whether a LedgerJournalTrans record created one GeneralJournalAccountEntry record or 5, I just want to be able to join the two tables so that the result set is the GeneralJournalAccountEntry records with the additional data fields from the LedgerJournalTrans table. At this point, I'm not sure it can even be done.

Was this reply helpful? Yes No
giss68 105 on at

Like (0)

Report

Hi Rick,

what will be the correct approach or join to the Vendtrans table to LEDGERJOURNALTRANS because it looks like the invoice number and the voucher cannot be used. in this case

Was this reply helpful? Yes No
Suggested answer

DavidGunawan 1,381 on at

Like (0)

Report

Hi Rick,

Based on your requirement, you can't just run one query to build the relation.

I suggest you breakdown the query to two parts. The first part you can query join between GeneralJournalEntry and LedgerJournalTable. For the first query, I believe you can get one to one relation. Then in the loop of the first query, you can create another query to get another related information of LedgerJournalTrans. For the second query, you can use other related fields that can give you the most specific record of LedgerJournalTrans such as Invoice (DocumentNum), DocumentDate, Voucher, LedgerDimension, etc.

Hope this can give you more idea.

Was this reply helpful? Yes No
D365 beginner 634 on at

Like (0)

Report

Hi Rick,

Did you create a job for the above ? I have the same requirement now. Can you share please, if you have done it.

Thanks

Was this reply helpful? Yes No
Suggested answer

Rustem Galiamov 8,072 on at

Like (1)

Report
Hi Rick!

Try to join via Voucher and SublegerVoucher field:

static void Job191(Args _args) { LedgerJournalTrans ledgerJournalTrans; GeneralJournalEntry generalJournalEntry; GeneralJournalAccountEntry generalJournalAccountEntry; while select ledgerJournalTrans where ledgerJournalTrans.Voucher == "voucherNum" join generalJournalEntry where generalJournalEntry.SubledgerVoucher == ledgerJournalTrans.Voucher join generalJournalAccountEntry where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId { info(strFmt("generalJournalAccountEntry %1", generalJournalAccountEntry.TransactionCurrencyAmount)); } }

Was this reply helpful? Yes No