Overview: Just like the other Premier Health hospital gift shops, we assist them with their physical inventory. With Upper Valley, they bring in a third party to do the scanning, and when they are done scanning, UVMC is given a file to upload with the counts. Here is the basic process:
Phase 1: Preparation:
· Step 1 – Scheduling - UVMC will reach out months ahead of time to put it on our calendar when they have their Physical Inventory scheduled. At that time, we schedule 3 things on our calendar
o Export of test file to send to UVMC a week or two before their inventory
o Final export file to send to UVMC a few days before their inventory
o The date of the actual inventory so we can assist in starting the PI, running reports, importing the PI file given to us by the third part, and then running post-inventory reports for them.
· Step 2 – Test Export File - About a week before the third party comes in, we need to export a test PI file and send it to UVMC for them to send to the third party. This file must be in a specific format. Here are the steps to follow:
o Log into UVMC’s server and log into Retail Pro
o Go to Merchandise – Inventory
o Click on the PI Export tab at the bottom of the screen:
o Right click anywhere on the list of inventory, then click “Select All”. Then right click again and click “Export – Send to Notepad”
o Let the export run fully (do not click on anything because that may disrupt the export)
o Save the file on the server, and then copy the file to your PC. The export can take 10 minutes or more.
o Name the file something that makes sense, like “UVMC Export 2026” and save it in C:\RTI.
o Copy that file to your desktop and close all windows on the server and close your connection to the server.
o Format the export file:
§ Open up the text file in Excel (it is tab delimited) and make sure that the ALU and UPC fields are set to TEXT fields so Excel doesn’t mess with the format!
§ This export file is a inventory lookup file that will be loaded onto the third party’s inventory scanners to verify each scan. As such, you will need to put all the ALUs and all of the UPCs into one column, keeping the Desc1 and Price fields as well. It’s OK that items can show up twice in here (once with an ALU and once again with a UPC) because its just being used to look up barcodes that are scanned to find and verify the item(s).
§ You can accomplish this however you want, but one way to format the file is to
· Copy the sheet to another sheet in Excel
· Get Sheet 1 to show only ALUs in Column A, Desc1 in Column B, and Price in Column C by simply deleting the UPC column)
· Get Sheet 2 to show only UPCs in Column A, Desc1 in Column B, and Price in Column C by sorting by UPC code and deleting any rows that DO NOT have a UPC field. Then delete the ALU column
· Copy the contents from Sheet 2 into Sheet 1, so Column A now contains all ALUs and UPCs, Column B has the Desc1, and Column C has the unit price.
· Use Find/Replace to find any commas and replace them with a blank space
· Format the Price column to be a number (so the dollar signs, $, go away)
· Label Column A: Lookup
· Label Column B: Description
· Lavel Column C: Price
· Save the file as a CSV file.
§ Make sure the format looks correct:
§ Email to the correct person at UVMC and they will forward it to the third party company.
· Step 3 – Final Export File – according to the schedule given, repeat the steps in Step 2 to create an updated export file which will have all the current inventory right before they start their inventory process.
Phase 2 – Physical Inventory Day: On the day of the inventory, we assist them with starting the inventory, running an inventory report before we upload the counts, and then updating the inventory and running the same inventory report to show the updated inventory counts.
· Step 1 – Start the PI in RP9 run a command in TechToolKit, and export inventory
o Log into the UVMC server and start the PI in RP9 – they do a simple PI in RP9 (no zones, no filters for anything, etc).
o Export the inventory right from Merchandise – Inventory to create the “Pre Inventory” report.
§ Open up Excel
§ Right click on the grid of items in the “Item Details tab” (not the “PI Export” tab). Here’s what it should look like:
§ Click on Select All
§ Right click again and select Export – Send to Notepad
§ Wait several minutes for it to export to Notepad (making sure you do not click on anything on the server that might disrupt the export)
§ Save the file on their server, and then copy the file to your computer
· Format the export file:
o Open up the text file in Excel (it is tab delimited) and make sure that the ALU and UPC fields are set to TEXT fields so Excel doesn’t mess with the format!
o Make the spreadsheet more visually appealing (turn into a table, summarize the St OH Qty, Ext Cost, and Ext Price columns at the bottom
o Label the Excel sheet (Sheet 1) as “Pre Inventory”
o Save your spreadsheet.
o Resolve any potential count issues using TechToolKit
§ Go into TTK and go to SQL Shell and run this (this helps avoid update issues where a Null value doesn’t get counted as a 0). Hit the thunderbolt after each entry:
UPDATE pi_start SET start_qty = 0 WHERE start_qty is null
COMMIT
· Step 2 – Upload the Counts, verify they just want you to update the inventory without them reviewing the counts first, then create a PI report
o They will send you the file you need to upload into Retail Pro, and there is only one map to use when uploading.
o Once the counts are imported, click on the Discrepancy button.
§ Right click on Select All
§ Right click again and select Export – Copy to clipboard
§ Wait for it to copy
§ Open up your Excel spreadsheet with the “Pre inventory” report, create a second Sheet, and paste the information into there
§ Make the spreadsheet more visually appealing (turn into a table, summarize the Discrepancy Qty, Discrepancy Ext Cost, and Discrepancy Ext Price columns at the bottom.
§ Label this second sheet “PI Discrepancies”
o If they confirm that they just want you to update the PI as it is, update the PI using the “All Qty” option
o When finished go back to Merchandise – Inventory and make sure you’re on the Item Details Tab and repeat the process you followed for the “Pre Inventory” report, by exporting data from RP9 but calling it “Post Inventory”
§ Add this data as a third Sheet in Excel and call the Sheet “Post Inventory”.
o Your finished Excel file will have 3 tabs
§ Pre Inventory (which shows the inventory BEFORE the PI was taken showing the value of the inventory before the by showing the total quantity, total ext cost, and total ext price)
§ Discrepancies (which shows just the discrepancies and the total discrepancies in quantity, ext cost, and ext price)
§ Post Inventory (which shows the inventory AFTER the PI was taken showing the value of the inventory before the by showing the total quantity, total ext cost, and total ext price)
o Email the final Excel file to the contact person at UVMC and let them know everything is finished.