Troubleshooting error - Commercial Invoice - Added Country to RDD

I’m creating a commercial invoice (copied the packing slip for the base). I’m getting an error after adding the Country table to the RDD when printing a test with the new report. Added new relationship with Country table and Part. See below details. If anyone has any advice on where to troubleshoot this from here, it would be greatly appreciated!

Error in system monitor: The column Part.ISOrigCountryNum used by the ‘PartCountry’ relationship is missing. Include the column or remove the relationship.

Relationships on RDD:

Relationship Part to Country:

image

Column is included on Part:

Column is included on Country:

1 Like

First off, never select a Key, always leave it blank. Remove that and see what happens.

Sadly, same error. Good to know for the future though on the key part!

The column Part.ISOrigCountryNum used by the ‘PartCountry’ relationship is missing. Include the column or remove the relationship.

Relogged into Epicor to ensure the new relationship saved.

Also, doublechecked ISOrigCountryNum on Part is included

Have you added the table and fields into the RDL query?

Yes, added this to the bottom of the query:

LEFT OUTER JOIN Country_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4.Company AND T2.IsOrigCountryNum = T4.CountryNum"


Complete:

="SELECT T4.Country_Description,T2.OrderLine_DocUnitPrice,T2.PartNum_CommodityCode,T1.Company,T1.LegalNumber,T1.PackNum,CAST( T1.CustNum as nvarchar ) as CustNum,T1.ShipComment,T1.ShipDate,T1.Calc_BilContct,T1.Calc_BillToAddress,T1.Calc_SoldToAddress,T1.Calc_Contct,T1.Calc_CustPartOpts,T1.Calc_FOBDescription,T1.Calc_LegalNum,T1.Calc_NumRecordPerPage,T1.Calc_NumTotalParts,T1.Calc_SalesRepName,T1.Calc_ShipToAddress,T1.Calc_ShipViaDescription,T1.Calc_stPhone,T1.Calc_CusPhone,T1.Calc_stFax,T1.Calc_EMailAddress,T1.Calc_FaxNum,T1.Calc_SEmailAddr,T1.Calc_CarrierDesc,T1.Calc_ShipViaSCAC,T1.Calc_CarrierSCAC,T1.Calc_CompanyEORINumber,T1.Calc_CustShipToEORINumber, T2.PackLine,T2.Discount,T2.ExtPrice,T2.HeaderShipComment,T2.LineDesc,T2.OrderLine,T2.OrderNum,T2.OrderRelNum,T2.PartNum,T2.RevisionNum,T2.ShipComment as ShipDtl_ShipComment,T2.UnitPrice,T2.XPartNum,T2.XRevisionNum,T2.Calc_DspBackOrdQty,T2.Calc_DspBackOrdQtyUom,T2.Calc_DspLabDur,T2.Calc_DspLabMod,T2.Calc_DspLineDesc,T2.Calc_DspLineShpQty,T2.Calc_DspLineShpQtyUom,T2.Calc_DspMatDur,T2.Calc_DspMatMod,T2.Calc_DspMiscDur,T2.Calc_DspMiscMod,T2.Calc_DspPlannedQty,T2.Calc_DspPlannedQtyUom,T2.Calc_DspSerialNumber,T2.Calc_DspShipRouting,T2.Calc_DspSubShipTo,T2.Calc_GetNextLegalNum,T2.Calc_LegalText,T2.Calc_LinChangd,T2.Calc_NextLegalNumID,T2.Calc_NumLineByInv,T2.Calc_OrdRelRef,T2.Calc_POLine,T2.Calc_SalesRepName as ShipDtl_Calc_SalesRepName,T2.Calc_SerialNumLabl,T2.Calc_TotalLineCost,T2.Calc_TotalTax,T2.Calc_HidePackLine,T2.OrderLine_KitFlag,T2.OrderLine_KitParentLine,T2.OrderLine_KitPrintCompsPS,T2.OrderLine_DisplaySeq,T2.OrderLine_KitShipComplete,T2.Calc_MarkForAddress,T2.OrderNum_PONum,T2.PartNum_PartDescription,T2.PCID,T2.Calc_CommodityCode,T2.Calc_AttributeSetShortDesc,
          T3.RptLiteralsLblDescription,T3.RptLiteralsLblFax, T3.RptLiteralsLblEmail,T3.RptLiteralsLblRel,T3.RptLiteralsLblPh,T3.RptLiteralsLblPO,T3.RptLiteralsLblRev,T3.RptLiteralsHComponents,T3.RptLiteralsLblBckOrQty,T3.RptLiteralsLblCustPart,T3.RptLiteralsLblDiscAmt,T3.RptLiteralsLblExtendedPrice,T3.RptLiteralsLblFOB,T3.RptLiteralsLblHdngPckSlip,T3.RptLiteralsLblLbDu,T3.RptLiteralsLblLine,T3.RptLiteralsLblMiDu,T3.RptLiteralsLblMtDu,T3.RptLiteralsLblOurPrt,T3.RptLiteralsLblPackSlip,T3.RptLiteralsLblPage,T3.RptLiteralsLblPlndQty,T3.RptLiteralsLblPoLine,T3.RptLiteralsLblPrtNum,T3.RptLiteralsLblReference,T3.RptLiteralsLblSalesOrder,T3.RptLiteralsLblSalesperson,T3.RptLiteralsLblShipDt,T3.RptLiteralsLblShipRouting,T3.RptLiteralsLblShipTo,T3.RptLiteralsLblShipVia,T3.RptLiteralsLblShpdQty,T3.RptLiteralsLblSoldTo,T3.RptLiteralsLblSrlNum,T3.RptLiteralsLblSrlNums,T3.RptLiteralsLblSubShipTo,T3.RptLiteralsLblTotalLineCost,T3.RptLiteralsLblTotalTax,T3.RptLiteralsLblUnitPrice,T3.RptLiteralsLblYourPO,T3.RptLiteralsLLegalText,T3.RptLiteralsLLegNum,T3.RptLiteralsLSalesKit,T3.RptLiteralsLCarrierSCAC,T3.RptLiteralsLCarrier,T3.RptLiteralsLblShipViaSCAC,T3.RptLiteralslblInvNumber,T3.RptLiteralslblInvDueDate,T3.RptLiteralsLblOf, T3.RptLiteralsLblContinueOnNext, T3.RptLiteralsLblComeFrom, T3.RptLiteralsLblPCID, T3.RptLiteralsLCommodityCode, T3.RptLiteralsLEORINumber, T3.RptLiteralsLblAttributeSet 
          FROM ShipHead_" + Parameters!TableGuid.Value + " T1
          LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
          ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
          LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T3
          ON T1.RptLanguageID = T3.RptLanguageID LEFT OUTER JOIN Country_" + Parameters!TableGuid.Value + " T4	ON T2.Company = T4.Company AND T2.IsOrigCountryNum = T4.CountryNum"

Here’s the query field I added as well. Just need the country name description on this report.

image

You need to have every field you are using in a join as one of the fields. I don’t see T4.Company, T4.CountryNum, and T2.ISOrigCountryNum.

And the field name is incorrect. Instead of T4.Country_Description it should be T4.Description AS Country_Description.

image

Should this be T4.Description AS Country_Description,

DaveO

Just got the same error with the PackSlip. It was complaining that the ShipHead.OrderNum wasn’t active. It was in the standard data definition that I copied from! This is on the latest version (cloud customer).

Did you ever get this resolved?

@Bob_Naylor

Were you able to get past this ?
I am having the same problem, save report style, same column.

I copied the data definition so I could include OrderHed.OrderNum and ShipHead.OrderNum, added the columns and the join in the SQL for the dataset and tried both with and without the relationship, and both with/without the 'Key" in the relationship in the data definition.

Still getting the error.

I also tried selecting both fields in the SQL for the dataset, although I’ve never had to do that in the past if I only needed them for the join…

Scott

Why would that be ? I’ve always used a Key and never had a problem.
(until now, but I’ve tried both with/without the Key to no avail)

Well, you know what it was…I was linking to the wrong table…It should have been ShipDtl. The very, very odd thing is that the OrderNum column doesn’t even exist in the ShipHead table….but shows up in the Exclusion list! That’s why I was getting the error. Maybe it’s the same thing with yours. Weird.

Bob

1 Like

Epicor can ship multiple orders on the same packing slip, so you’ll find the OrderNum in the ShipDtl.

Right. It is funny that the column ShipHead.OrderNum does show up in the exclusions when it is non-existent.

1 Like

I think it might be a holdover from days gone by. :person_shrugging: