Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

(0) Share

Report

Posted on by Paul Heisterkamp

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

All responses (10)

Answers (0)

Arti007 60 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

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
alexd 457 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

This will work as long your Voucher Number rule for New Vouchers is set to "In connection with Balance".

Check GL>Setup>Journals>Journal Names>General>Voucher Number>New Voucher
Suggested answer

daniellqueiroz on at

Like (2)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

;)

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
Genuine Microsoft 305 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

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
Kar_Gilson 410 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

Any updates on this please?
BartF 5 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

Hi Paul,

I have the same problem , did you get this fixed finally ? If yes, how did you solve it ?

Thanks in advance,

Bart
dolee 11,279 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

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
Paul Heisterkamp 1,155 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

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
dolee 11,279 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

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.
Paul Heisterkamp 1,155 on at

Like (0)

Report

RE: Unique connection between CustTrans/VendTrans and GeneralJournalAccountEntry

Version is 2012 R2 CU6