Hi all,
I have done a ton of research on this one and looked at a bunch of other threads with similar issue with no clear fix. I need my records to show in the exact same order in crystal reports as they do in the SAP b1 document and include text lines.
Some solutions involve the TmSP_DocLineTypeLayout;1 stored procedure and i had a look at a system crystal report but haven't been able to put 2 and 2 together and figure out how its done.
As you can see from the crystal report view, the text line displays after the first record however;
The order and text row comes last here.
Ideally this will work with text rows anywhere in this order of the document.
Here is my query in Crystal Reports;
SELECT DISTINCT T0.[CardName], T0.[DocNum], T0.[DocDate], T0.[Address], T0.[Address2], T2.[SlpName], T2.[Mobil], T2.[Email], T0.DocEntry, T1.ItemCode, T1.Dscription, T1.Quantity, '\\LSA-DB01\Pictures\'+convert(varchar,T3.Picturname), T1.Price, T4.Tel1, T4.Cellolar, T4.E_MailL, T4.FirstName, T4.LastName, T5.PymntGroup, CAST (T0.[U_LSA_Deposit] AS DECIMAL) AS 'Deposit', CAST(T6."LineText" AS varchar(MAX)) AS LineText, T0.[U_LSA_Period], T7.ItmsGrpNam, CAST(T3.UserText AS varchar(MAX)) AS ItemInfo, T0.Series, T1.FreeTxt FROM OQUT T0 INNER JOIN QUT1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode INNER JOIN OITM T3 ON T1.[ItemCode] = T3.[ItemCode] INNER JOIN OCPR T4 ON T0.[CntctCode] = T4.[CntctCode] INNER JOIN OCTG T5 ON T0.[GroupNum] = T5.[GroupNum] INNER JOIN QUT10 T6 ON T0.[DocEntry] = T6.[DocEntry] INNER JOIN OITB T7 ON T3.ItmsGrpCod = T7.ItmsGrpCod
I tried having QUT10 as a LEFT JOIN with AND T6.AftLineNum = T1.LineNum included and T1.DocEntry instead of T0 however that just produced no results.
I understand why this isnt a straight forward solution like it is in the PLD, but i still cant figure out how to fix it, have been at it for a while now.
As always, any help is greatly appreciated.