on 05-20-2011 8:46 PM
8.80.236 PL 18 HOTFIX1
I thought I got this answered but it is still illuding me.
I still cannot make the connection between INV1 and the correct record in OBTN for a crystal report.
Example:
Table: INV1 Field: DocEntry Value: 234
Table: INV1 Field: LineNum Value: 0
Table: INV1 Field: ItemCode Value: Product A
Table: INV1 Field: Quantity Value: 2
Came from
Table: OBTN Field: ItemCode Value: Product A
Table: OBTN Field: DistNumber Value: Product A-0001
Table: OBTN Field: MnfSerial Value: Product B-0002
My issue is that besides the ItemCode and Date fields in both tables there is no specific link to a batch. If the Itemcode in OBTN has 2 records then there is no way of telling which batch the INV1 came from because it will link to both records in OBTN.
Simular issues arise with OBTQ OBTW OITL ITL1
Hello Jon,
To solve this problem, I did the following:
Used the standard SBO CR Lay-out
Built a custom CR subreport, in this report I used the Command function to retreive data:
SELECT DISTINCT
dbo.OITL.CardCode, dbo.OITL.CardName, dbo.OITL.DocNum, dbo.DLN1.DocEntry, dbo.OITL.DocType, dbo.OSRN.DistNumber, dbo.OSRN.MnfSerial,
dbo.OITL.ItemCode, dbo.OITL.ItemName, dbo.OSRN.MnfDate, dbo.OSRN.GrntExp
FROM dbo.ITL1 INNER JOIN
dbo.OITL ON dbo.ITL1.LogEntry = dbo.OITL.LogEntry INNER JOIN
dbo.OSRN ON dbo.ITL1.ItemCode = dbo.OSRN.ItemCode AND dbo.ITL1.SysNumber = dbo.OSRN.SysNumber RIGHT OUTER JOIN
dbo.DLN1 ON dbo.OITL.DocLine = dbo.DLN1.LineNum AND dbo.OITL.DocEntry = dbo.DLN1.DocEntry
WHERE (dbo.OITL.DocType = 15)
The subreport is embeded by the DocEntry and the (dbo.OITL.DocType = 15) in the subreport, The OITL.DocType of a sales invoices is 13
If you give your e-mail, I can send you a delivery report in CR if you like.
Grtz,
Freek
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Jon,
These two tables may not have direct link between them. Do you have delivery in process or copy sales order to invoice directly?
Thanks,
Gordon
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.