How do you join a Work Order to the Sales Order Line Item for which it was created in Crystal Reports?

Question

Trying to write a report that shows Sales Order line items and any Work Orders that have been created for them. I thought I had it when I joined these tables: 
 SO_SalesOrderDetail to WO1_WorkOrderMaster on SO.SalesOrderNo = WO.MakeForOrderNumber and SO.LineKey = WO.SOLineIndex 
 But apparently a line index is not the same thing as a line key. I believe the line key is a unique number assigned at entry that never changes, while the index is just its current position. The line key field doesn't appear to be in the WO1_WorkOrderMaster table. I do see it in the WO_WorkOrder table but that seems to be an empty file. Adding Item Code and Qty would not be unique enough because sometimes we have multiple lines for same item/qty in one order. 
 Is this even possible? I really hope so and I'd greatly appreciate your help! 
 Thank you!

AgentCunningham · Accepted Answer

Hi Tyler - thank you so much for spending time on this! I looked at all the files involved and here is what I found. We originally had 3 line items in the SO, with lines 2 and 3 having associated Work Orders. We completed the WOs and shipped those 2 lines, but line 1 was back ordered. Then we added a new line to the SO, which did not need a WO, and it received line key 2 - a duplicate of the original line key 2 that was already shipped. That is why there is a WO record falsely matching to the new line 2 and appearing on my report. I would think that the line key would be kept unique even through adding, deleting, and shipping lines, but apparently not. Looks like it just takes the highest line key in the order as it sits at the time, and adds 1. This makes it impossible to be confident in the report. I can add Item Code and Order Qty to the join criteria, but it still won't be 100% - maybe 99%. Thanks again, and please let me know if you find anything that contradicts my findings. -Tony Cunningham

AgentCunningham · Answer

Hello John - Thank you so much for looking at this. I think the LineKey matches the SoLineIndex most of the time, but here is what I found: Quoting my reply to Tyler: 
 I looked at all the files involved and here is what I found. We originally had 3 line items in the SO, with lines 2 and 3 having associated Work Orders. We completed the WOs and shipped those 2 lines, but line 1 was back ordered. Then we added a new line to the SO, which did not need a WO, and it received line key 2 - a duplicate of the original line key 2 that was already shipped. That is why there is a WO record falsely matching to the new line 2 and appearing on my report. I would think that the line key would be kept unique even through adding, deleting, and shipping lines, but apparently not. Looks like it just takes the highest line key in the order as it sits at the time, and adds 1. This makes it impossible to be confident in the report. I can add Item Code and Order Qty to the join criteria, but it still won't be 100% - maybe 99%. 
 Best regards, 
 Tony Cunningham

Tyler Christensen · Answer

SELECT "SO_SalesOrderDetail"."SalesOrderNo", "SO_SalesOrderDetail"."ItemCode", "WO1_WorkOrderMaster"."WorkOrder", "WO1_WorkOrderMaster"."MakeForOrderNumber", "WO1_WorkOrderMaster"."SOLineIndex", "SO_SalesOrderDetail"."LineKey"
FROM "SO_SalesOrderDetail" "SO_SalesOrderDetail", "WO1_WorkOrderMaster" "WO1_WorkOrderMaster"
WHERE ((("SO_SalesOrderDetail"."SalesOrderNo"="WO1_WorkOrderMaster"."MakeForOrderNumber") AND ("SO_SalesOrderDetail"."ItemCode"="WO1_WorkOrderMaster"."ItemBillNumber")) AND ("SO_SalesOrderDetail"."LineKey"="WO1_WorkOrderMaster"."SOLineIndex"))

Sage 100

How do you join a Work Order to the Sales Order Line Item for which it was created in Crystal Reports?

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING