cancel
Showing results for 
Search instead for 
Did you mean: 

How to display itemcode from jdt1

Former Member
0 Kudos
417

Hi all,

I'm attempting to create a report that displays ItemCode andLineTotal from the iINV1 table grouped by the account field stored in JDT1 table. My issue is, if I total up the credits/debits from the JTD1 table, everything adds up correctly. When I add a field from the INV1 fields, all totals become inflated or just plan wrong. Is my below select startement incorrect? I understand lines will duplicate, but even running totals turn up with the wrong totals.. Thanks for your time.

SELECT DISTINCT "JDT1"."Account", "JDT1"."Credit", "JDT1"."RefDate", "JDT1"."Ref1", "OINV"."Ref1", "OINV"."DocEntry", "INV1"."DocEntry", "INV1"."LineTotal", "INV1"."ItemCode"

FROM ("Live"."dbo"."JDT1" "JDT1" INNER JOIN "Live"."dbo"."OINV" "OINV" ON "JDT1"."TransId"="OINV"."TransId") INNER JOIN "Live"."dbo"."INV1" "INV1" ON "OINV"."DocEntry"="INV1"."DocEntry"

WHERE "JDT1"."RefDate">={ts '2010-08-01 00:00:00'} AND "JDT1"."Account"=N'400014'

ORDER BY "JDT1"."Account"

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi,

Your goal is not realistic. There is no direct relationship between itemcode in INV1 and JDT1 table. The numbers can never be added up.

Thanks,

Gordon

Former Member
0 Kudos

Thanks Gordon,

So, How does one show what items have been ordered/invoiced by account? If you look at the trail balance report is shows Debit/Credit/Balance by account. (I can reproduce that) I'm just looking at adding the items which made up those totals. Can this not be done?, or am i going about it wrong.

Former Member
0 Kudos

Hi Brian....

You can built relation between OINV, INV1, OJDT and JDT1 and can fetch the item details.

You should recognize the proper keys in order to join those tables.

for OINV and INV1 DocEntry is the key and for OJDT and OINV TransId is the key.......

Regards,

Rahul

Former Member
0 Kudos

Adding the ODJT did the trick.. Thanks for your help guys..

Once I finish up the report I'll post a download link to it.. Maybe others may find it useful..

Answers (1)

Answers (1)

Former Member
0 Kudos

You can begin by focusing on one transaction to find the data relationship for those tables because each company has its own rule of G/L determination.