Rentals Manager:Inventory Management

Inventory Systems Normalization

Because we use 3 different piece of software to manage the business, it can be a pain to make sure that all expendable names are consistent across the systems. We use an excel spreadsheet (Master Expendables List) to act as a main hub for maintaining the vast expendables inventory we carry.

EVERYTHING related to the expendables is driven from the CurrentRMS data (except for the quantity on hand, which is in QuickBooks). That is the master data set we use that drives the other items.

When making many changes to the dataset, it is easier to do it in the excel spreadsheet and import it back in Current (and the other software). Central to tracking inventory is the Barcode (or SKU in Quickbook & Square), which is the identifier we use across all the software systems.

Exporting Current Dataset

• Go to System Setup
• Export Data
• Select Product
• Export Button

You will eventually get an email with a link to the CSV file.

• Open CSV File in Excel
• Go to Data Tab, select everything (ctrl-A) and select Filter
• In Allowed Stock Type (column D), use the pull-down and select only "Rental". This will only show rental items.
• Select all the entries (highlight first row, then <Shift-End><down arrow> to highlight all the rows
• Press <Delete> to remove all the rental entries
• Turn off Filter
• Select everything (ctrl-A) and Sort based on Barcode

Importing Dataset Updates into current

• In Excel, Open a CSV file that was previously generated
• In the Master List, select everything in the Current Product sheet and copy.
• In the CSV file, select everything and Paste
• IMPORTANT: Make sure the Rental & Sale Bulk Quantity columns (AB & AD) are EMPTY. Otherwise the import will fail.
• Save the CSV file (keep the CSV format when warning pops up)
• In Current RMS, go to System Setup, Import Data
• Select Product from the pull-down, CSV should be the format.
• Choose the CSV file from the dialog and import.

You will get an email when the import is completed. If there are errors (duplicate Barcode is common) you need to address it BOTH in the CSV file and back in the Master excel sheet. Once fixed, import in current again.

Exporting Data from QuickBooks

• In QBO, Go to Sales > Products and Services
• Select the Export Icon (next to the gear in the table)
• A xls file will be downloaded, names ProductSrviceList_...
• Open the file in Excel
• Delete rows that are not "Inventory" Type (sort by Type, then SKU)
• Select the data and paste in the QBO Export sheet, STARTING at column C.

IMPORTANT NOTE: Quickbooks uses the "Product/Service Name" as the Key for a product. That means you CAN NOT change the name of a product/service in the spreadsheet. This has to be done from within Quickbooks.

Importing Data into QuickBooks

• From the QBO Export master sheet, select all rows from columns C to P.
• In the QBO exported file, delete everything and paste the columns from the QBO master sheet (Values only)
• In QBO, go to the Sales > Products and Services screen.
• Select the New button menu and choose import
• Select the excel file you updated, click next, in Map Data, all the fields should be green.
• Select next and you should see the first few lines of the updated data with their fields.
• IMPORTANT: At the bottom, select the "overwrite all values for product with identical name"
• Select Import
• You should get a summary of how many items were imported.