cancel
Showing results for 
Search instead for 
Did you mean: 

how to join OCPR table with SAP B1 default Crystal Report for Purchase Order

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (3)

Answers (3)

mike_taylor3
Contributor
0 Kudos

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

Former Member
0 Kudos

I have tried exactly as you described here by on a Sales A/R - Delivery Note where I joined CntctCode and CardCode from the ODLN Table to the OCPR table (using Left Outer Joins). and the Item Lines do not print at all.

Any Ideas?

Thanks,

Neil.

mike_taylor3
Contributor
0 Kudos

Does the layout work without OCPR?  If you are properly LEFT OUTER JOINing it to the layout then it should work.  Try to remove the OCPR table and then try the same DocKey and ObjType and see if it still works properly.

Check my invoice example too, does that work properly for you?

Former Member
0 Kudos

After adding the OCPR Table, setting up the Joins and it not working I removed the table and ran the Verify Database again and reran the preview and the lines are still missing.

It looks like adding the table and removing it again somehow breaks the design.

julie_jamieson2
Active Contributor
0 Kudos

Can you post the SQL query from your report? in Crystal Designer> View SQL Query

Former Member
0 Kudos

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.

mike_taylor3
Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Mike,

Your Hypothesis was correct, this did indeed resolve the problem and now everything works as it should.

Many, many thanks for your help.

Regards,

Neil.

mike_taylor3
Contributor
0 Kudos

Glad to hear it!  If my answer was correct, plz give me props and mark my answer as correct.  I'm only five points from the next forum level.  Feel free to message me directly for other questions, I'll do my best to answer them.  Mike

Former Member
0 Kudos

Hi Mike,

Not sure if I can mark this thread as the correct answer as I didn't start off this thread.

Regards,

Neil.

mike_taylor3
Contributor
0 Kudos

Duh, sorry about that.  You are right.  Regardless, let me know if you have any more issues.  If I see you post anything I'll try to follow-up on it or message me. Mike

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Are you getting any result, if you run above query in SAP B1?

Thanks & Regards,

Nagarajan

Former Member
0 Kudos

Hi Nagarajan,

Yes I am getting the desired results in SQL when I run the query.

Is there a possibility to add this query in the default "AP Purchase Order (Item) - CR (GB) (System)" Crystal report layout in SAP Business One.

Kind Regards,

Ravi

Former Member
0 Kudos

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.

Former Member
0 Kudos

Dear Thomas,

I tried the Left Outer Join also but it did not helped.The full layout became empty while generating.

Please advise.

Kind Regards,

Ravi

julie_jamieson2
Active Contributor
0 Kudos

Try reversing the link - right click on the link and choose reverse?

Does the layout produce results where there is a contact person defined?

Check that you don't have Automatic Smart Linking turned on - this will generate a whole lot of links when adding a table that are invalid.