You’re offline. This is a read only version of the page.
1,155
Does anyone know a unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry?
We want to display the related vendor or customer (if there is one) in form “voucher transactions” (LedgerTransVoucher) as a display method column.
I found a solution to display the wanted values by linking the records in the same way the button “Customer or vendor transactions” in from LedgerTransVoucher does. But this solution only works till there is more than one CustTrans/VendTrans involved.
Any other ideas?
*This post is locked for comments
Hi All,
Please let me know if you have any way to get 1:1 relation between GENERALJOURNALACCOUNTENTRY and CUSTINVOICETRANS table. I am new to dynamics.
I used the below query but I am getting multiple lines if I have more than one line for same sales order.
select gae.*,CIT.ITEMID
from GENERALJOURNALACCOUNTENTRY GAE,
GENERALJOURNALENTRY GE,
SUBLEDGERVOUCHERGENERALJOURNALENTRY SUB,
CUSTINVOICEJOUR CIJ,
CUSTINVOICETRANS CIT,
CUSTPACKINGSLIPJOUR CPJ
where GAE.GENERALJOURNALENTRY=GE.RECID
AND sub.GENERALJOURNALENTRY=GE.RECID
AND sub.VOUCHERDATAAREAID=GE.SUBLEDGERVOUCHERDATAAREAID
and (CIJ.LEDGERVOUCHER=SUB.VOUCHER or CPJ.LEDGERVOUCHER=SUB.VOUCHER)
AND CIJ.DATAAREAID=SUB.VOUCHERDATAAREAID
AND CIJ.SALESID=CPJ.SALESID
AND CIJ.DATAAREAID=CPJ.DATAAREAID
AND CIT.INVOICEID=CIJ.INVOICEID
AND CIT.DATAAREAID=CIJ.DATAAREAID
;)
SELECT TOP 1 GJE.*, VTR.*, LJTR.*
FROM GENERALJOURNALACCOUNTENTRY GJAE
LEFT OUTER JOIN GENERALJOURNALENTRY GJE ON GJAE.GENERALJOURNALENTRY = GJE.RECID
LEFT OUTER JOIN DIMENSIONATTRIBUTELEVELVALUEVIEW DALVV ON GJAE.LEDGERDIMENSION = DALVV.VALUECOMBINATIONRECID
LEFT OUTER JOIN LEDGERENTRY LE ON GJAE.RECID = LE.GENERALJOURNALACCOUNTENTRY
LEFT OUTER JOIN LEDGERENTRYJOURNAL LEJ ON GJE.LEDGERENTRYJOURNAL = LEJ.RECID
LEFT OUTER JOIN LEDGERJOURNALTABLE LJT ON LEJ.JOURNALNUMBER = LJT.JOURNALNUM
LEFT OUTER JOIN VENDTRANS VTR ON GJE.SUBLEDGERVOUCHER = VTR.VOUCHER AND GJE.DOCUMENTDATE = VTR.DOCUMENTDATE
LEFT OUTER JOIN CUSTTRANS CTR ON GJE.SUBLEDGERVOUCHER = CTR.VOUCHER AND GJE.DOCUMENTDATE = CTR.DOCUMENTDATE
LEFT OUTER JOIN LEDGERJOURNALTRANS LJTR ON GJE.SUBLEDGERVOUCHER = LJTR.VOUCHER AND GJE.ACCOUNTINGDATE = LJTR.TRANSDATE
LEFT JOIN DIMENSIONATTRIBUTE DA ON DA.RECID = DALVV.DIMENSIONATTRIBUTE
HI, How can I update the "QUANTITY" in GENERALJOURNALACCOUNTENTRY Table ? I need to update the QUANTITY field of GENERALJOURNALENTRY Table with the total of PURCHQTY of PURCHLINE while posting the Purchase invoice. Which function do I need to modify to achieve this.
Thanks Hillary
Any updates on this please?
Hi Paul,
Yes, to my understanding it should be 1:n relationship if from CustTrans/VendTrans to GeneralJournalAccountEntry.
But since your requirement is showing Vendor/Customer in the Voucher transaction form, Wouldn't this do (each GeneralJournalAccountEntry will find either one or no Cust/Vend account)?
Dom
Hey Dolee,
thx for your help, but a already know this connection and it is not what I am looking for. This connection will end in an 1:n realtion from CustTrans/VendTrans to GeneralJournalAccountEntry.
Paul
Hi,
GeneralJournalEntry tables links to SubledgerGeneralJournalEntry table by the "GeneralJournalEntry" field in SubledgerGeneralJournalEntry table.
Then, search for Vend/CustTrans based on the "voucher", "voucherDataAreaId" and "AccountingDate" (Matches "voucher", "dataareaid" and "transdate").
See if the result is what you expect.
Version is 2012 R2 CU6
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,232
Super User 2024 Season 2
#2
Martin Dráb
230,064
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (