You’re offline. This is a read only version of the page.
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
Hi,
GeneralJournalAccountEntry and LedgerJournalTrans tables relate together with below code:
select firstonly forUpdate generalJournalAccountEntry join generalJournalEntry join ledgerJournalTrans order by RecId desc where generalJournalEntry.SubledgerVoucher == ledgerJournalTrans.Voucher && generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
Hello Mr. Rustem,
Thank you for your kind contribution, It's really worked for me.
Hi Rick!
Try to join via Voucher and SublegerVoucher field:
Share
Report
All responses (
Answers (
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));
}
}
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
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.
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.
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.
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.
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.
#1
Mohamed Amine Mahmoudi
100
Super User 2025 Season 1
#2
Community Member
48
#3
shanawaz davood basha
6
