Adding JobProd table to Job Pick List RDD

Kinetic ERP
1

I need the JobProd table as I need the OrderNum for the Job Pick List RDD. I added the JobProd table then Linked Tables, found OrderNum, click the Drop Down for OrderNum and then selected OrderNum again. I went into SSRS and added the table
LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " JP
ON T2.Company = JP.Company AND T2.JobNum = JP.JobNum AND T2.Calc_AsmSeq = JP.AsmSeq AND T2.Calc_MtlSeq = JP.MtlSeq AND T2.PartNum = JP.PartNum"

I run the report and keep getting NO RECORDS SELECTED.

2

Try

LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " JP
ON T2.Company = JP.Company AND T2.JobNum = JP.JobNum"
3

That did not work, still same error. I included a screenshot of the RDD as well

2023-02-06 10_46_07-Report Data Definition
2023-02-06 10_46_07-Report Data Definition758Ă—378 14.7 KB

4

=“SELECT JP.OrderNum_OrderNum,T1.PartNum_Durometer,T1.AssemblySeq,T1.[Description],T1.DueDate,T1.IssuedQty,T1.IUM,T1.JobNum,T1.PartNum,T1.PullQty,T1.RequiredQty,T1.RevisionNum,T1.Calc_BCAsmSeq,T1.Calc_BCJobNum,T1.Calc_BCPartNum,T1.Calc_BCRevNum, T2.[Description] as JobMtl_Description,T2.MfgComment,T2.MtlSeq,T2.PartNum as JobMtl_PartNum,T2.ReqDate,T2.Calc_BCMtlSeq,T2.Calc_BCPartNum as JobMtl_Calc_BCPartNum,T2.Calc_FromSource,T2.Calc_MtlSeq,T2.Calc_ReqdQty,T2.Calc_UOM, T3.BinNum,T3.LotNum,T3.MtlSeq as JobBin_MtlSeq,T3.OnhandQty,T3.UOM,T3.WarehouseCode,T3.AllocatedQty,T3.PCID, T3.Calc_BCPCID, T2.Calc_SubAsmSeq, T2.Calc_AttributeSetShortDescription
FROM JobAsmbl_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum AND T1.AssemblySeq = T2.Calc_AsmSeq LEFT OUTER JOIN JobBin_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.Calc_AsmSeq = T3.AsmSeq AND T2.Calc_MtlSeq = T3.MtlSeq AND T2.PartNum = T3.PartNum LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " JP ON T2.Company = JP.Company AND T2.JobNum = JP.JobNum"

5

Maybe, instead of adding to Linked Tables, create a Report Relationship to bring in JobProd for Output.

Looking at JobProd in BAQ, it appears that PartNum is now JobHed.PartNum. So you’d likely have to keep this in mind in the design.

Unfortunately, we have not added this data so I have not run into the gotchas on this one before.

6

I would guess you would want to add a relationship to make this work.

Also, Joining with JobProd could create multiple records. It would be much safer to use a Dataset in SSRS to get the Order Information you are looking for.

image
image884Ă—660 115 KB

1 Like
7

Right on.

This thread talks a bit about doing a similar thing, using JobAsmbl relationship.

Best route to add ShipTo Address and ShipDate to Job Pick List RDD? - ERP 10 - Epicor User Help Forum (epiusers.help)

8

Still getting NO RECORDS SELECTED. I added the JobHead table and deleted the JobProd table as well.
For the relationship, wouldn’t I need to add OrderNum for the 3rd sequence?

9

Mr. Gregory: you will not be able to add the order number as the third - because the order number does not exist in JobAsmbl Table.

DaveO