In today’s guide, we will continue reviewing the Mass Update function in Epicor’s Prophet 21 by focusing on building grids and query exports in the Excel Query screen. We’ll walk through running queries, creating templates, and effectively exporting query templates into Excel documents for future use.
Starting a Mass Update:
Start off by navigating to the item maintenance screen within Prophet 21. Here, we’ll explore how to bring saved filter criteria into a Mass Update, set up a grid (including tabs and columns), execute queries, and export the data into an Excel document.
Using a pre-saved template:
To save time when making mass updates in Prophet 21, we can make use of saved templates. In the video, we’re pulling up a pre-established template named ‘BND Washer Items.’ Note: You can also construct your criteria from scratch as needed.
When the template opens, it will upload all your previously saved criteria.
Reviewing the Excel Query screen:
Before we dive into the actual Mass Update query, we’ll briefly skim over some important parts of the Excel Query screen.
Columns and Tabs – The Building Blocks
The main part of the Mass Update Excel Query function is the columns and tabs section. Consider these as your building blocks. They represent the various tabs within item maintenance. In the “Tabs/Columns” section on the left side of the screen, you’ll see a search feature to pull in specific columns for your query.
An important feature here is the ‘eyeball‘ tool, which allows you to toggle search results visibility. When you select an item and toggle the visibility on, the screen will generate another tab at the bottom, effectively creating additional pages within your Excel document.
Creating a query:
With the tabs and columns configured, we can finish constructing our query. You can select multiple data points to include depending on what you are looking for in the report.
Once you’ve set up your query, it’s time to run it. Depending on how many records your query finds, it may take some time to process fully.
Query Record Limits:
Also, remember that there is a 10,000-record limit for an Excel screen query. Note that this limitation doesn’t just apply to individual items but also to items with shared records across multiple locations and tabs, which can significantly impact your record count.
So keep in mind that if you run a query for five thousand items that are at three different locations, you will get an error saying that you are over ten thousand records when you try to open up that tab.
Now if you are only focusing on the top tabs, they are not location-specific, and you can edit ten thousand items. But once you get into items with multiple locations, your record counts will immediately be higher.
Data Verification Tip:
Remember to always open all tabs before proceeding with the export. Each click on a tab triggers the system to retrieve the data specific to that tab. If you rush to export without opening all the tabs, Prophet 21 may not grab all the data you need for your query.
The Final Step – Exporting Data
Once you have confirmed all the correct data is included in your query, click Export. You may be prompted to save the file. In our system, we have it set to automatically save to the Downloads folder.
And that concludes our tutorial on the Mass Update Excel Query function in Prophet 21. We’ll continue to explore more advanced topics, such as creating full Excel templates, in future videos. If you have any questions or specific areas you’d like us to cover, don’t hesitate to leave a comment below.
