Ok this is a little deeper than I’ve gone before on a report data definition change so I need a little help here.
Our repairs department uses the Order Detail Reference field to enter in a specific number for each cable they repair, they want that to print on the packing slip when they ship the parts back and I’m a bit lost on how to bring that field into the report. I see OrderDtl in the data definition for pack slip, and I unchecked it as being excluded but I don’t see any of the fields associated with that table called out in the report dataset query so I’m not sure if I need to link that table in the Data Definition. Also if I do that then what T value it would be? and would I need to add the table links to that query as well.
I’ve never added a field from a table that does not appear to already be getting queried so this is why I’m a bit lost.
Thanks.
I think you have the right path…
I would add a relationship of ShipDtl to OrderDtl (ShipDtl being the parent).
Make sure that relationship is set to “Output” for a Relation Type.
Join them on Company = Company, OrderNum = OrderNum, & OrderLine = OrderLine
Save the RDD.
In your RDL:
Yes, you’ll have to add OrderDtl as a reference table in your query. The “T” number can be anything you want that hasn’t been used in the query already. This is just an alias. It doesn’t have to be “T”. For example, you can add:
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " JERRY ON T2.Company = JERRY.Company AND T2.OrderNum = JERRY.OrderNum AND T2.OrderLine = JERRY.OrderLine
Obviously, JERRY doesn’t mean anything… but neither does T1, T2, T3, etc. (Table 1, Table 2, Table 3). Its just an alias.
So… I would add the below to your query:
LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T4 ON T2.Company = T4Company AND T2.OrderNum = T4.OrderNum AND T2.OrderLine = JERRY.OrderLine
(If T4 is available… or use whatever number you want).
Other Examples, I’ve used the alias “SR” before when I joined the SalesRep table.
I’ve also seen people use C1, C2, C3… this just helps visually differentiate that the join is a Custom-added table to the query. So, stock tables have T, custom-added tables use a C. It can be whatever you want.
Two things to note… Make sure there is a space after the previous line (marked in red) whereever you paste this in. And make sure the end quotation mark is still at the end. So, if you just paste your new table join at the end (which is fine)… you need to move that quotation mark to after your new table join.
Then you can add your reference field to the upper portion of the query as T4.YourColumnName.
You’ll also need to add your custom field in the Dataset’s Fields… but you noted you’ve added fields before, so, you know the drill!
Let us know if you get hung up anywhere.
Thanks for the reply. I’m stuck on it now duplicating a line after I add the Left Outer Join to the query… I’m currently leaving out the column and the field in the dataset properties and only added the join. Does this need to be a different join to not duplicate? Similar to how a BAQ will generate multiple records if your join is the wrong one.
My report goes from this
to this
And the respective Join entries that cause this.
Original Code
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 JOIN PartLot_" + Parameters!TableGuid.Value + " T4 on T4.Company = T2.Company and T4.Calc_PackNum = T2.PackNum and T4.Calc_PackLine = T2.PackLine"
Duplicated line
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 OrderDtl_" + Parameters!TableGuid.Value + " T5
ON T2.Company = T5.Company AND T2.OrderNum = T5.OrderNum AND T2.OrderLine = T5.OrderLine
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T3
ON T1.RptLanguageID = T3.RptLanguageID LEFT JOIN PartLot_" + Parameters!TableGuid.Value + " T4 on T4.Company = T2.Company and T4.Calc_PackNum = T2.PackNum and T4.Calc_PackLine = T2.PackLine"
Okay, I just looked at the PackSlip RDD and OrderDtl (OrderLine) is already a linked table to ShipDtl.
If you look at the linked table in your RDD can you see you UD columns? (My custom OrderDtl columns show).
You may want to try:
Scrapping the added relationship to your RDD. (which may be adding unintended rows to the query temp table).
Scrapping the new join in your RDL.
Then, just try:
Adding T2.OrderLine_YouCustomColumn_c to the RDL query
Add it as a field in the dataset
Add it to your form.
See if you get the value to appear.
SUCCESS!!!
Thank you! Those table links and joins are SO convoluted it’s so hard for me to remember which one drives the other when the last time I did anything this complicated was nearly 13yrs ago… LOL
So the only thing I had to do was “Include” reference from the OrderDtl table, and then pick that field in the Linked tables Description Fields tab, for the RDD, then add T2.OrderLine_Reference to the query and add a field called Reference tied to OrderLine_Reference in the RDL add a text box, pick Reference and voila!