Sage 100

I have a customer that is needing to print picking sheets in a specific order each day.  Currently, they created different form types with Crystal Reports parameters but using the date range the orders that are not printed as they do not meet the CR parameters the Pick Sheet print field is marked as "Y" even though the form was not printed.  The customer has several different type of criteria for the printing order.  Any suggestion would be welcomed.

------------------------------
Venessa Griffin
RKL eSolutions, LLC
------------------------------

With other forms I've done this in a couple of hours by converting them into a custom report thereby avoiding the reset of the Printed flag. I further avoid having to rebuild the report by emulating the "SO_PIckingSheetWrk" table using a Crystal Set Location command table to re-direct source table's fields to the pseudo work table. I use a blank Crystal report to link the necessary tables to create a SQL statement with the table structure necessary to allow access to most of the needed SO_PIckingSheetWrk fields. After piling all of the fields required for the picking sheet onto the blank report, the Show SQL Query command provides the basis for converting the work table into a similar command table that will look and function just like the work table. (the last image below shows a sample of the converted database fields under the command table named SO_PickingSheetWrk)

1. Link all the necessary tables including the SO header, detail, CI_Item warehouse description, terms description, etc.
2. The idea is not to re-create the form, but to drop all the fields onto a blank report without regard to where they land.
3. Once you have all of the fields, use the Database menu Show SQL Query command to display and copy the resulting query.
4. Open a copy of the PickSheet report and select the Database menu Database Expert command
5. Select the SO_PickingSheetWrk table in the "Current Data Source" section
6. Expand the "My Connections" entry in the "Replace with:" section to expose and select the  "Add Command" feature under the same connection used in the "Current Data Source" section above. (see the 1st graphic below)
7. Click the "Update" button
8. Paste SQL query into the "Add command to Report" dialog and Click OK (see the 2nd graphic below)
9. Any missed unmapped fields can be added to the command query in the Database Expert by selecting the SO_PickingSheetWrk table, right-clicking and selecting "Edit Command".
10. There are a few formulas that should be set as if their values were passed by Sage (Divisions, QuantityDecimals, IncludeBackorderedLines, etc.) and other formulas to be cleaned up for fields that are not necessary for the purpose.
11. You may need to piece together a "GroupField" in the SQL to serve your sorting purposes as it is the basis for the 3rd report grouping.
12. Since this is a custom report, you can add criteria that checks the PickingSheetPrinted flag for current orders.

------------------------------
Dan Burleson
Software Consultant
Connex Software
Corvallis OR
541-224-6642
------------------------------