Are you extracting sales order data from outside of Sage and struggling to get the sales order data back into Sage 500 ERP? If you’ve tried to use Data Porter to import into the Sales Order Entry screen but have run into issues, I have a work around that you probably haven’t tried.
You may have a Sales Order Entry screen that looks something like the screen pictured below.
You click on the Data Porter icon and start building your template. Everything appears to be good in the Excel template, so you click the exclamation point “!” to import and you’re stuck with an error. Whether that error pertains to the Item ID, Warehouse, or something else, you end up with a head scratcher and a lot of data needed to be imported.
Re-Configure Entry Screen so it’s Compatible with Data Porter
Before you scrap the idea of using Data Porter, I want to show you how you can re-configure your entry screen so that it works with Data Porter. There is a forgotten setup task in Sage 500 ERP that will allow you to reconfigure how Enter Sales Orders and Quotes appears on the screen. You don’t need a Customizer license to do this. In fact, if you have ever tried using Customizer to manipulate the Enter Sales Orders and Quotes screen (sozdj101.exe), you may have run into issues. Before I tell you about this task, I want to explain why it will work once you use it to reconfigure your screen layout.
Data Porter in a nutshell
Think of Data Porter as an extremely fast typist. It does not push data in from behind the scenes, it simply uses Excel connectors with the Sage 500 user interface (UI) data screens. Data Porter relies on the available form fields shown on the screen. Drawbacks of Data Porter: it does not work with grids or buttons. That means the standard configuration shown in the image above will not allow you to get to the Item ID (pretty critical in order entry, right?). As you can see above, the only place for the Item ID is in the grid. Instead, Data Porter attempts to import data into the form fields on the right half of the screen (click More>>) where the Line / Favorites tab data lives. When Data Porter encounters that screen, there is no Item ID for it to fill, and you’re stopped in your tracks.
Re-configure the Line / Favorites tab
As I mentioned, you will need to access the task in Sage 500 ERP that allows you to easily configure the Sales Order Entry Lines / Favorites tab. This task is called Customize SO Entry. It can be found in the Sales Order module, inside the Maintenance folder. This task allows you to create a customization for the Sales Orders and Quotes entry screen by user and company, user, group, company, or site (similar to the levels in Customizer). You can customize the Favorites tab and/or the Entry Grid. For the purpose of allowing Data Porter to work, you will only need to use the Favorites Tab.
There are some limitations on the favorites tab. It allows up to 14 form fields from the available groups. But each group can have many fields. For example if you were to select Quantities, which has 7 fields, meaning you only have 7 more allowed. Line detail (5) includes five fields including: Item ID, Quantity Ordered, Unit of Measure, Unit Price, and Sales Amount. Additionally, Line Misc (3) includes three fields including: Description, warehouse ID, and Line Status. These would be the recommended choices for Data Porter to work successfully. With up to six fields left, you can leave the rest blank, or pick any one or combination from the available groups on the left totaling no more than 6. The only other thing you need to figure out is whom and where to apply this to within the customization level.
You should now have a simple format in Sales Order Entry to create a unique Data Porter template that’s equipped with the basic fields necessary to import a Sales Order without errors.
We Can Build you a Custom Template
If additional help is needed to create a template specific to your needs, please reach out to the RKL Support team.