Crystal Reports - Sales Order Picking Sheet - Adding User Name Generates Blank Pick Sheet

Question

Hello, 
 I'm trying to modify my picking sheet so it shows which Sage user created the sales order. I'm using Sage 100 ERP 2013 and Crystal Reports 2011. 
 I have "SO_SalesOrderDefaults" added in Crystal Reports under Database Fields, but when I add "UserKey" to the template and print a picking sheet (just using Preview, not actually printing), the sheet turns up without any of the order information and only shows the template itself. I've tried using SY_User table and the SO_SalesOrderWrk tables as well. I've tried linking the fields - linking the sales order number in the picking sheet table to the userkey and I've tried reversing the link. I saw there are lots of options for configuring the links, but it's all unfamiliar to me. 
 I'm hoping to be able to show which user created the sales order using their Sage "User Logon" or the "First Name" and "Last Name" fields. 
 Does anyone have any ideas about doing this? 
 Thank you

David Speck · Accepted Answer

You have a lot of joins going on there that you shouldn't really need and they may impact performance depending on number of records in each table. You can instead add sourced UDFs to your Wrk table. The SO_PickingSheetWrk table supports sourcing data from the following sources.

So what you can do create a UDF in the Wrk table to hold the UserCreatedKey from SO_SalesOrderHeader, then you would only need to join SY_User on the user key fields from each table and you can then print the first and/or last name on the report. 
 
 You should consider creating sourced UDFs for any values that you are currently getting from the IM_Warehouse and CI_Item tables through the table joins so you can eliminate as many table joins as possible from the report.

BigLouie · Answer

I created two UDFs, First Name and Last Name in the Sales Order Header table and then created a script that populates the UDF's when the order is created. I set it up to carry to history and prints on forms as needed. 
 Here is the script 
 sFirst = "" : sLast = "" : sKey= "" : sCKey = "" 
 retVal = oBusObj.GetValue("UserCreatedKey", sCKey) 
 Set oUser = oBusObj.AsObject(oBusObj.GetChildHandle("UserCreatedKey")) 
 retVal = oUser.getvalue("UserKey$",sKey) retVal = oUser.getvalue("FirstName$",sFirst) retVal = oUser.getvalue("LastName$",sLast) 
 If sKey = sCKey then retVal = oBusObj.SetValue("UDF_FIRST$", sFirst) retVal = oBusObj.SetValue("UDF_LAST$", sLast) 
 end if

Sage 100

Crystal Reports - Sales Order Picking Sheet - Adding User Name Generates Blank Pick Sheet

Top Replies

COMMUNITY

COMPANY

PARTNERS

SUPPORT & TRAINING