Skip to main content
Post a question

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id : zL5726fU2q7gus5j3AaDa9
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / Relationship/join betw...
Finance | Project Operations, Human Resources, ...
Suggested answer

Relationship/join between GeneralJournalAccountEntry and Trans tables

Like (0) ShareShare
ReportReport
Posted on 24 May 2021 23:59:52 by javastuff.sam@gmail... 5

I am new to Dynamics 365, with my limited understanding, here is my problem description -

GeneralJournalAccountEntry is the table that store posted transaction, it only store limited information that can be used for trial balance. It does not store any details related to the original transaction. To find it's original transaction, we have to find the relation between GeneralJournalAccountEntry table with another table. We found few tables with postfix "Trans" which can provide that details (VendTrans, BankAccountTrans, CustTrans, LedgerJournalTrans, AssetTrans, TaxTrans), however we did not able to find direct relationship between GeneralJournalAccountEntry and listed Trans tables.

As an example, GeneralJournalAccountEntry and VendTrans are related, VendTrans Table store transactions about Vendor related transactions and GeneralJournalAccountEntry stores it's nominal value. To join those two tables, we have to join them using field GeneralJournalEntry.SubLedgerVoucher with VendTrans.Voucher. Similar for other Trans tables. However this join is not sufficient, one voucher can have multiple transactions for each GeneralJournalEntry table entry and for each GeneralJournalEntry table entry there will be multiple entries in GeneralJournalAccountEntry. This result into cartesian join and incorrect number of entries returned.

Is there any other join or way we can get posted transactions (from GeneralJournalAccountEntry) along with transaction details (from Trans tables)?

We tried to lookup other old posts, but seems joins discussed there are not sufficient so that each transaction in Trans table can be joined to GeneralJournalAccountEntry entry, in other words tied to Main Account or Ledger Account. LedgerDimention field in Trans table is either empty or value zero or some entries point to module specific account not main account, so it can not be used to join back with  GeneralJournalAccountEntry entry effectively.

We are trying to export posted transactions along with its details using custom data entity and hence we are looking for a better join. We are using Dynamics 365 Finance and Operation 2020 release wave 2. 

Thanks for your help.
Best Regards,
-Sam

  • VickyD Profile Picture
    VickyD on 09 Mar 2023 at 12:56:48
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    javastuff.sam@gmail.com Were you able to find a solution to this? It seems like there is no 1:1 relationship between GeneralJounalAccountEntry record and a VendTrans record so we might be looking for something that does not really exist :).

    When I just visually look at the 2 tables and at 2 records that I know are related then the only thing I can see that could be used to uniquely tie the 2 together is the text description along with of course the voucher, date and data area id.

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 05 Jun 2021 at 00:16:15
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Thanks Hossein for your response. I looked at table custPaymModeTable you mentioned, it is only for CustTrans, what about other trans tables. I do not think this table is helping much resolving the link between GeneralJournalAccountEntry and Trans tables (VendTrans, BankAccountTrans, CustTrans, LedgerJournalTrans, AssetTrans, TaxTrans).  

    Thanks,

    -Sam

  • Suggested answer
    Hossein.K Profile Picture
    Hossein.K 6,646 on 02 Jun 2021 at 11:28:15
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Hi,

    Please try below code:

    GeneralJournalAccountEntry generalJournalAccountEntry;
GeneralJournalEntry        generalJournalEntry;
LedgerJournalTrans         ledgerJournalTrans;

select firstonly forUpdate generalJournalAccountEntry
join generalJournalEntry
order by RecId desc
where generalJournalEntry.SubledgerVoucher == _ledgerJournalTrans.Voucher
&& ledgerJournalTrans.Voucher == "Your voucher Num"
&& generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId
&&  generalJournalAccountEntry.LedgerDimension == custPaymModeTable.InterCompanyLedgerDimension;

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 02 Jun 2021 at 00:57:59
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Joins between GeneralJournalAccountEntry and Trans tables (VendTrans, BankAccountTrans, CustTrans, LedgerJournalTrans, AssetTrans, TaxTrans) discussed here or found in other posts seems to be not sufficient to solve my usecase get posted transactions (from GeneralJournalAccountEntry) along with transaction details (from Trans tables).    

    Let me rephrase my question -

    Is there a way, we can find MainAccount from Trans table entry directly without joining with GeneralJournalAccountEntry? Is there a way we can find fully qualified MainAccount (including all dimensions) from Trans table entry directly without joining with GeneralJournalAccountEntry?

    Thanks,

    -Sam

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 27 May 2021 at 18:05:45
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Thank you CraigW42 for your response. I have not looked into DefaultDimensionView yet, I will take a look. However working with defaultdimension field seems not directly matching with GL entries in GeneralJournalAccountEntry.

    I am really looking for logic, how transactions entries from Trans table flow into GeneralJournalAccountEntry and linked with MainAccount/LedgerAccount since Trans table there is no information about MainAccount/LedgerAccount.

    Thanks,

    --Sam  

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 27 May 2021 at 17:51:04
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Removing duplicate response.

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 27 May 2021 at 17:50:13
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Thank you Ergun Sahin for you response.

    Link you mentioned is joining only with generalJournalEntry and its fine, however problem starts as soon as we try to add generalJournalAccountEntry table to the same join because of Cardinality between these tables -

    generalJournalEntry to generalJournalAccountEntry is 1:N and generalJournalEntry to Trans table is 1:N, so out come is N:N without any good direct join between generalJournalAccountEntry and Trans table. This result into more entries and wrong details.

    Hope I am clarifying the issue here.

    Thanks,

    --Sam

  • CraigW42 Profile Picture
    CraigW42 85 on 26 May 2021 at 01:54:27
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Hi Sam,

    Have you considered turning the query on it's head and using the Voucher table as the primary.

    I always found that approach effective in AX2012 and sure it still works for D365.

    The join is from the Voucher table to a view called DefaultDimensionView (which joins three of the dimension tables together), the link is using the defaultdimension value. That would then give you the all voucher values regardless of the number of corresponding GL entries.

    Regards,

    Craig  

  • Suggested answer
    ergun sahin Profile Picture
    ergun sahin 8,816 Moderator on 25 May 2021 at 18:27:55
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Add date and dataareaId(look kranthi's code)

    dynamicsuser.net/.../general-journal-to-ledgerjournaltrans

  • javastuff.sam@gmail.com Profile Picture
    javastuff.sam@gmail... 5 on 25 May 2021 at 16:52:12
    RE: Relationship/join between GeneralJournalAccountEntry and Trans tables

    Thanks Will, for your response.

    We are already using Voucher field as join, however it is not sufficient. One voucher has multiple entries, so when those are joined with generalJournalAccountEntry then we get more than expected rows exported. Basically wrong details are joined to posted transactions.

    We feel, we need an additional join, probably LedgerDimension so that Trans table entries can be joined to generalJournalAccountEntry correctly. However issue is how to get correct LedgerDimension for Trans table entries, there is a field in Trans tables, but does not have value or it is zero or not ,matching with generalJournalAccountEntry.LedgerDimension.

    Based on initial finding, it seems it involved posting profile setup. So question is - how to get correct LedgerDimension for Trans table entries which can be used in join with generalJournalAccountEntry?

    Thanks,

    -Sam

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

Announcing Our 2025 Season 1 Super Users!

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

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,489 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,305 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans