How can I get to GeneralJournalEntry from VendInvoiceJour in Electronic Reporting?I need JournalNmber filed in GeneralJournalEntry table.

(0) Share

Report

Posted on by Zahra Mirzazadeh

156

Dear All,

I wanna get Journulnumber which in (GENERALJOURNALENTRY)table. my Table Record is VendInvoiceJour,

I tested another way in previous Comments.

1- one solution was in last topic:

https://community.dynamics.com/ax/f/microsoft-dynamics-ax-forum/207784/relationship-between-generaljournalaccountentry-and-ledgerjournaltrans

I made this script in Sql and it works well:

select e.JOURNALNUMBER, t.LEDGERVOUCHER from VENDINVOICEJOUR as T
inner join LEDGERJOURNALTRANS as g on T.LEDGERVOUCHER=g.VOUCHER
inner join GENERALJOURNALENTRY as e on e.SUBLEDGERVOUCHER=g.VOUCHER

where t.LEDGERVOUCHER='API-00000008'

But in Formula designer in Relation tables of LEDGERJOURNALTRANS ,there is n't GENERALJOURNALENTRY table,so i found Ledger journal table(JournalNum) instead it and used this script:

select j.JOURNALNUMBER, t.LEDGERVOUCHER from VENDINVOICEJOUR as T
inner join LEDGERJOURNALTRANS as g on T.LEDGERVOUCHER=g.VOUCHER
inner join LEDGERJOURNALTABLE as h on h.JOURNALNUM=g.JOURNALNUM
inner join LEDGERENTRYJOURNAL as i on i.JOURNALNUMBER=h.JOURNALNUM
inner join GENERALJOURNALENTRY as j on j.LEDGERENTRYJOURNAL=i.RECID

where t.LEDGERVOUCHER='API-00000008'

This above script works well also I can find this above path in formula designer, But when I run my report, it shows wrong "journalNumber" !

GIG_VendInvoiceJour_TR.'vendTrans()'.'<Relations'.LedgerJournalTrans.'>Relations'.LedgerJournalTable.'<Relations'.LedgerEntryJournal.'<Relations'.GeneralJournalEntry.JournalNumber

thanks in advance for any assistance

Best Regards,

Zahra

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Sergei Minozhenko 23,097 on at

Like (1)

Report
Copy link

Link copied!

Hi Zahra,

VendTrans relation in LedgerJournalTrans is done by VendTransId field, not by Voucher and that's probably the reason why you see different journal number.

I would recommend to create relation on VendInvoiceJour to LedgerJournalTrans to get direct link between invoice and ledger journal trans.

Was this reply helpful? Yes No
Zahra Mirzazadeh 156 on at

Like (0)

Report
Copy link

Link copied!

Hello Sergei,

Thank you for the reply, Ok I will use your guidance in my script in SQL server. I should say that I can get to correct data in SQL,But in electronic Reporting i Can't get my correct "JournalNumber" which exists in "GENERALJOURNALENTRY" table.

Would you please guide me?

Best regards,

Zahra

Was this reply helpful? Yes No
Verified answer

Sergei Minozhenko 23,097 on at

Like (1)

Report
Copy link

Link copied!

Hi Zahra,

Your SQL script is almost correct except you need to add an additional link between VendInvoiceJour and LedgerJournalTrans by T.InvoiceDate = g.TransDate (but if you don't use duplicated vouchers it's not an issue) and parition and dataArea links as well.

select j.JOURNALNUMBER, t.LEDGERVOUCHER from VENDINVOICEJOUR as T

inner join VendTrans as t1 on t1.AccountNum = T.InvoiceAccount AND t1.TransDate = T.InvoiceDate AND t1.Invoice = T.InvoiceId AND t1.Voucher = T.LedgerVoucher

inner join LEDGERJOURNALTRANS as g on T1.VOUCHER=g.VOUCHER

inner join LEDGERJOURNALTABLE as h on h.JOURNALNUM=g.JOURNALNUM

inner join LEDGERENTRYJOURNAL as i on i.JOURNALNUMBER=h.JOURNALNUM

inner join GENERALJOURNALENTRY as j on j.LEDGERENTRYJOURNAL=i.RECID

This is a query produced by ER is using different link between between VendTrans and LedgerJournalTrans

select j.JOURNALNUMBER, t.LEDGERVOUCHER from VENDINVOICEJOUR as T

inner join VendTrans as t1 on t1.AccountNum = T.InvoiceAccount AND t1.TransDate = T.InvoiceDate AND t1.Invoice = T.InvoiceId AND t1.Voucher = T.LedgerVoucher

inner join LEDGERJOURNALTRANS as g on T1.RecId=g.VendTransId

inner join LEDGERJOURNALTABLE as h on h.JOURNALNUM=g.JOURNALNUM

inner join LEDGERENTRYJOURNAL as i on i.JOURNALNUMBER=h.JOURNALNUM

inner join GENERALJOURNALENTRY as j on j.LEDGERENTRYJOURNAL=i.RECID

I don't see any possibility to fix it without development (add new relation to VendInvoiceJour to LedgerJournalTrans and use it instead vendTrans() method)

Was this reply helpful? Yes No
Zahra Mirzazadeh 156 on at

Like (0)

Report
Copy link

Link copied!

Hi Sergi,

I wanted to find it in electronic Reporting without any Development, But if it is not possible So I have to make a method and use it.

Thank you.

Best Regards,

Zahra

Was this reply helpful? Yes No