Adding Material SubPart to Job Traveler

MelissaC · February 13, 2026, 6:34pm

Our Job travelers worked in E10 but broke when we went to Kinetic. We need to add the Substitute Parts for the material on the BOM to the traveler. I’m really not good with RDDs and SSRS

RDD With SubPart Unexcluded:

I think I need to update the RawMaterial subreport in the traveler but I’m not sure how, current query:

=“SELECT T1.JobNum,T1.Calc_MultiJobParts, T2.AssemblySeq,T2.BackFlush,T2.Company,T2.[Description],T2.IssuedComplete,T2.IssuedQty,T2.IUM,T2.JobNum as JobMtl_JobNum,T2.MfgComment,T2.MtlSeq,T2.Ordered,T2.PartNum,T2.RelatedOperation,T2.RequiredQty,T2.WarehouseCode,T2.Calc_BCMtlSeq,T2.Calc_BCPartNum,T2.JobNum_PartDescription, T3.PartNum as TTJobPartMtl_PartNum, T2.Calc_InventoryQty, T2.Calc_InventoryUOM, T2.Calc_AttributeSetShortDescription, T2.RevisionNum
FROM JobHead_” + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JobMtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.JobNum = T2.JobNum
LEFT OUTER JOIN TTJobPartMtl_" + Parameters!TableGuid.Value + " T3
ON T2.Company = T3.Company AND T2.JobNum = T3.JobNum AND T2.MtlSeq = T3.MtlSeq"

And then add SubPart as a calculated field?

utaylor · February 13, 2026, 6:38pm

So, in order to add substitute parts for the job material/raw material you have to figure out what table those live in and then add that table to the RDD and then add a “report relationship” to the RDD where you join that table to the table in the RDD that holds the raw material you want alternates for.

Once you do that, then you update the query you just pasted in the RDL with the same thing by adding a left outer join clause to it and pretty much write the SQL version of what you did in the ERP screen.

Does that make sense?

utaylor · February 13, 2026, 6:39pm

We can work through it if you start by telling me the table that the alternate parts live in, do you know that table?

tgwinn · February 13, 2026, 8:22pm

PartSubs looks like the table you want.

MelissaC · February 13, 2026, 8:49pm

Hi,
It’s in the Part table. I need to link the part number from jobmtl to the part table then look up the Part.SubPart field.

utaylor · February 13, 2026, 10:17pm

Okay, I can help with that next week.

MelissaC · February 14, 2026, 3:58pm

I can’t tell if it’s the RDD or the query on the report that’s wrong. Once I add the join I get the error that the subreport can’t be shown.

Updated RDD to add Part Table to JobMtl: Should it be Output or Definition? I’ve tried both.

Do I have to pick anything in this Linked Tables section? I thought the link came from the report relationship.

Here is my updated query on the subreport (my changes in yellow) - I get the error subreport can’t be shown:

MelissaC · February 14, 2026, 4:33pm

I’ve also tried the same link but using the PartSubs table (which I didn’t think existed because I didn’t see it in the data dictionary - LOL) and I get the same error that the sub report can’t be shown

MelissaC · February 17, 2026, 3:55pm

I’ve gotten a bit farther towards solving this I think. I added the PartSubs table to the RDD and linked it to the TTJobPartMtl table

Then I updated the query on my report:

        T1.Calc_MultiJobParts,
        T2.AssemblySeq,
        T2.BackFlush,
        T2.Company,
        T2.[Description],
        T2.IssuedComplete,
        T2.IssuedQty,
        T2.IUM,
        T2.JobNum as JobMtl_JobNum,
        T2.MfgComment,
        T2.MtlSeq,
        T2.Ordered,
        T2.PartNum,
        T2.RelatedOperation,
        T2.RequiredQty,
        T2.WarehouseCode,
        T2.Calc_BCMtlSeq,
        T2.Calc_BCPartNum,
        T2.JobNum_PartDescription,
        T3.PartNum as TTJobPartMtl_PartNum,
        T2.Calc_InventoryQty,
        T2.Calc_InventoryUOM,
        T2.Calc_AttributeSetShortDescription,
        T2.RevisionNum, 
        T4.SubPart AS SubPartValue
 FROM JobHead_" & Parameters!TableGuid.Value & " T1
 LEFT OUTER JOIN JobMtl_" & Parameters!TableGuid.Value & " T2
    ON T1.Company = T2.Company
   AND T1.JobNum = T2.JobNum
 LEFT OUTER JOIN TTJobPartMtl_" & Parameters!TableGuid.Value & " T3
    ON T2.Company = T3.Company
   AND T2.JobNum = T3.JobNum
   AND T2.MtlSeq = T3.MtlSeq
 FT OUTER JOIN PartSubs_" & Parameters!TableGuid.Value & " T4
    ON T3.Company = T4.Company
AND T3.PartNum = T4.PartNum"

When I refreshed the fields nothing changed so I added the field

But when I add it to my report and run it it prints SubPartValue instead of the substitute part

Randy · February 17, 2026, 4:15pm

You need to have the relationship fields at minimum in the unchecked in the Exclusions tab. So Company and MtlPartNum will be in the dataset plus whatever other fields you want to show actually on the report.

utaylor · February 17, 2026, 4:23pm

@MelissaC, I never went into the fact that when you add a table to your RDD, like @Randy said, you have to go in and go to that table in the RDD and make sure that hte columns you are joining on from that new table are not “excluded” and whatever other column you want to show in the results is also not excluded so that they can be used in the query expression in your .rdl. Does that makes sense?

MelissaC · February 17, 2026, 4:36pm

They are not excluded

dcamlin · February 17, 2026, 4:41pm

Can I ask a question and potentially propose an easy (UNTESTED) solution?

Are the Sub Parts you want to show marked as the “default” sub part on the part records?

Here are my thoughts. When you’re one a Part record, and you add a substitute part… the system writes that to the PartSub table. You can have multiple sub parts for a given Part record.

HOWEVER, the Sub Part that you mark as the “default” (and you can only pick one per Part Record) is then recorded directly onto the Part Table.

Example: as long as I check that default box…

If I then query the Part Table… that value lives on the Part Table in the SubPart column.

So… I guess my point is… IF you’re showing “default” Sub Parts… Then you should be able to simply add the SubPart column to the ALREADY LINKED PartNum table in the RDD:

Click on JobMtl table > Linked Tables > Description Fields > Choose PartNum in the dropdown > Move SubPart to the “Picked” columns.

Then in your RDL… all you should need to do is add T2.PartNum_SubPart to your query and as a query field.

Again, as mentioned above, this is not TESTED… but it should work. The only draw back is that IF, on a given part record you have multiple Subs… and want to display them ALL, this approach won’t work.

MelissaC · February 17, 2026, 5:08pm

Ok - I started from the beginning again

Added the SubPart

Added it to my report query:

Added it as a field:

Added it to my report:

And it prints the words instead of the value

utaylor · February 17, 2026, 5:45pm

Don’t use an expression, just “add a query field” when you’re adding a field to your dataset.

dcamlin · February 17, 2026, 6:08pm

Based on this… you’re adding a “Calculated” Field instead of a Query Field.

Make sure you pick Query Field when you add it.

MelissaC · February 17, 2026, 6:29pm

Well they sure hide that option well … thank you so much - I’m not going to cry myself to sleep tonight