In today’s blog post, we’re sharing how to edit Excel Exports for Mass Updates in Epicor’s Prophet 21 (P21). If you’re using P21, you’ve likely used the Mass Update feature at some point to apply bulk edits within your system. In this post, we’ll focus on editing an exported Excel document and how to effectively apply changes before you import it back.
Understanding Mass Update in Prophet 21
You have two options for running a Mass Update in Prophet 21: the Basic screen (for simple edits to a group of items) and the Excel screen (a much better option for more complicated changes). We’ve been talking about the Mass Update feature for a few weeks now, and today, we are reviewing the Excel screen. We’re also discussing how to work with data in an Excel doc before you import it back into Mass Updates.
Quick Review: Check out our previous P21 Mass Update video tutorials. ⬇️
- How to set role/user permissions.
- How to set basic filter criteria for queries.
- Overview of the Basic Mass Update screen.
- How to build and export grids from the Excel query screen.
When you execute a Mass Update in P21, it generates an Excel file from the query.
It is a bit more advanced, but it is possible to create your own template documents for Mass Updates. Once you make a template tailored to your data needs, you can reuse it for future projects.
However, we generally recommend exporting the data, manipulating it in Excel, and reimporting it back into P21. This ensures data integrity and minimizes potential errors.
Excel Document Structure
- Document Alignment: The tabs at the bottom of the Excel document mirror the tabs in your Mass Update screen. These tabs also correspond to the various tabs within “item maintenance,” where we are running our update.
- The ID Column: The most critical element in the Excel document is the ID column. On each subsequent tab or page within the document, this column gets referenced by the “Main ID” column. It serves as a reference point, linking the data back to the General tab. It’s important that the ID data corresponds throughout the different tabs to avoid issues with replicated items.
Making Data Updates
Updating data in the Excel doc is pretty straightforward. You can modify information like prices or quantities, and save the document with a new name that will be easy to reference when you import it into P21.
Fields That Can and Cannot Be Changed
However, keep in mind that there are some fields you cannot change in Excel (for instance, Item ID). Updating certain fields can also lead to additional errors. For instance, in the Supplier by Location tab. If you were in Prophet 21 and manually changed that data, you would have to select “Apply All” to set the same Location Cost for all items. However, if you change a Location Cost in Excel, P21 will automatically apply that change to all locations when you upload the document.
Managing Data Across Different Tabs
You might notice that the number of items on different tabs within the Excel document can vary. For instance, there may be three items on the general tab, but six on the cost tab. The reason behind this discrepancy often lies in how certain data is structured within your P21 system.
For instance, the cost tab might include data for multiple locations, leading to repetitions. Either way, each data entry retains its corresponding main ID, connecting it back to the Item ID column on the general tab.
And that’s it for our overview of editing Excel Exports in the Mass Update feature of Prophet 21.
Stay tuned for our upcoming video where we’ll cover the process of importing the Excel document back into P21. In the meantime, please feel free to leave your comments and questions below.
