on 08-27-2014 12:55 PM
Hello,
I am trying to use the default "AP Purchase Order (Item) - CR (GB) (System)" Crystal report layout to make the Purchase Order in SAP Business One.
The problem is that this template does not has the table of OCPR ( which is the contact person details like First Name, last Name, Telephone 1 etc ).
I tried from my end by going to database expert and then selecting the OCPR table but after i connect and regenerate the report there is no data coming and the whole template is becoming blank.
In SQL I am able to link OPOR and OCPR table with the below query but I am not able to link the same in "AP Purchase Order (Item) - CR (GB) (System)" layout.
SELECT T0.[DocNum], T1.[CardCode], T1.[Name], T1.[Position], T1.[Tel1], T1.[FirstName], T1.[MiddleName], T1.[LastName] FROM OPOR T0 INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
Please advise how can i connect OCPR table to this template.
Kind Regards,
Ravi
Here is an example of joining the OCPR table in an SAP Business One layout.
Download: http://www.battleshipcobra.com/example_invoice.rpt
I have joined OCPR and done some formatting, just copy what I did to your PO. Just join CntctCode and CardCode to the OPOR table.
Thanks,
Mike
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Julie,
Below is the SQL Query;
LN-042690\SAPB1V91 SELECT "ODLN"."DocNum", "ODLN"."Comments", "ODLN"."NumAtCard", "ODLN"."CurSource", "ODLN"."VatSum", "ODLN"."VatSumFC", "ODLN"."VatSumSy", "ODLN"."DocTotal", "ODLN"."DocTotalFC", "ODLN"."DocTotalSy", "ODLN"."DocType", "ODLN"."DiscSum", "ODLN"."DiscSumSy", "ODLN"."DiscSumFC", "ODLN"."DocCur", "ODLN"."DocEntry", "ODLN"."DocDate", "ODLN"."TotalExpns", "ODLN"."TotalExpSC", "ODLN"."TotalExpFC", "ODLN"."WTSum", "ODLN"."WTSumSC", "ODLN"."WTSumFC", "ODLN"."RoundDif", "ODLN"."RoundDifSy", "ODLN"."RoundDifFC", "ODLN"."DiscPrcnt", "ADP1"."PrintSums", "ADP1"."PrnDscnt", "ADP1"."VndrNum", "ODLN"."Address", "ODLN"."CardName", "ODLN"."Address2", "ODLN"."ShipToCode", "ODLN"."Weight", "ODLN"."TrnspCode", "ODLN"."CardCode", "ODLN"."Series", "ODLN"."CntctCode", "ODLN"."Footer", "ADP1"."Remark" FROM "ADP1" "ADP1" RIGHT OUTER JOIN "ODLN" "ODLN" ON "ADP1"."ObjType"="ODLN"."ObjType" WHERE "ODLN"."DocEntry"=29 ORDER BY "ODLN"."DocNum" EXTERNAL JOIN ODLN.DocEntry={?LN-042690\SAPB1V91: TmSp_DocLineTypeLayout;1.DocEntry} AND ODLN.TrnspCode={?LN-042690\SAPB1V91: OSHP.TrnspCode} AND ODLN.CardCode={?LN-042690\SAPB1V91: OCRD.CardCode} AND ODLN.Series={?LN-042690\SAPB1V91: NNM1.Series} AND ODLN.CntctCode={?LN-042690\SAPB1V91: OCPR.CntctCode}
LN-042690\SAPB1V91 "TmSp_DocLineTypeLayout";1 29, 15 EXTERNAL JOIN TmSp_DocLineTypeLayout;1.DocEntry={?LN-042690\SAPB1V91: ODLN.DocEntry} AND TmSp_DocLineTypeLayout;1.DocEntry={?LN-042690\SAPB1V91: DLN1.DocEntry} AND TmSp_DocLineTypeLayout;1.LineNum={?LN-042690\SAPB1V91: DLN1.VisOrder}
LN-042690\SAPB1V91 SELECT "DLN1"."LineTotal", "DLN1"."TotalFrgn", "DLN1"."TotalSumSy", "DLN1"."Dscription", "OADM"."SysCurrncy", "OADM"."MainCurncy", "OADM"."DecSep", "OADM"."ThousSep", "OADM"."CurOnRight", "OADM"."SumDec", "DLN1"."TaxOnly", "OADM"."DateFormat", "OADM"."CharMonth", "OADM"."DateSep", "OADM"."PercentDec", "DLN1"."ItemCode", "DLN1"."TreeType", "DLN1"."OrderedQty", "DLN1"."Quantity", "OITM"."SalUnitMsr", "DLN1"."VisOrder", "DLN1"."DocEntry" FROM ("DLN1" "DLN1" INNER JOIN "OITM" "OITM" ON "DLN1"."ItemCode"="OITM"."ItemCode") INNER JOIN "OADM" "OADM" ON (((("DLN1"."VatPrcnt"="OADM"."VatPrcnt") AND ("DLN1"."LogInstanc"="OADM"."LogInstanc")) AND ("DLN1"."WtLiable"="OADM"."WTLiable")) AND ("DLN1"."DeferrTax"="OADM"."DeferrTax")) AND ("DLN1"."ConsumeFCT"="OADM"."ConsumeFCT") WHERE "DLN1"."DocEntry"={?LN-042690\SAPB1V91: TmSp_DocLineTypeLayout;1.DocEntry} AND "DLN1"."VisOrder"={?LN-042690\SAPB1V91: TmSp_DocLineTypeLayout;1.LineNum}
LN-042690\SAPB1V91 SELECT "OSHP"."TrnspName", "OSHP"."TrnspCode" FROM "OSHP" "OSHP" WHERE "OSHP"."TrnspCode"={?LN-042690\SAPB1V91: ODLN.TrnspCode}
LN-042690\SAPB1V91 SELECT "OCRD"."GroupCode", "OCRD"."CardCode" FROM "OCRD" "OCRD" WHERE "OCRD"."CardCode"={?LN-042690\SAPB1V91: ODLN.CardCode}
LN-042690\SAPB1V91 SELECT "NNM1"."BeginStr", "NNM1"."EndStr", "NNM1"."Series" FROM "NNM1" "NNM1" WHERE "NNM1"."Series"={?LN-042690\SAPB1V91: ODLN.Series}
LN-042690\SAPB1V91 SELECT "OCPR"."Name", "OCPR"."Tel1", "OCPR"."E_MailL", "OCPR"."Cellolar", "OCPR"."CardCode", "OCPR"."CntctCode" FROM "OCPR" "OCPR" WHERE "OCPR"."CntctCode"={?LN-042690\SAPB1V91: ODLN.CntctCode} AND "OCPR"."CardCode"={?LN-042690\SAPB1V91: ODLN.CardCode}
Regards,
Neil.
Hi Neil,
I have a hypothesis. Could you go into your Crystal Reports and go to File >> Options >> Database tab. At the very bottom there is a box called "Automatic Smart Linking."
Is this checked?
The reason I ask is that any time you make a change in the Database Expert this "smart" linking will just blindly match ALL primary keys based on column name alone. This will make an unusable spider-web of linking. You should see simple linking between the tables, if you see a rats nest of links then you will have to start the layout again.
"Automatic Smart Linking" needs to be disabled once per Crystal Reports Designer install and under no circumstances should it be enabled for SAP Business One purposes.
Thanks,
Mike
Hi,
Are you getting any result, if you run above query in SAP B1?
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Crystal defaults to an Inner Join. In the event that there isn't a valid contact person this could cause the behavior your seeing. In the Database Expert Link tab, right click on the OPOR to OCPR join and change it to a Left Outer Join.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
86 | |
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.