Nellie Builds a Style Grid (The Nellie Series)
Please note this is the sixth and final post in a series of posts on QuickBooks Software Point of Sale by our staff Point of Sale expert, Leah Swain.
Here is an example of how the import file looked from our last post.
NOTE: This is for an item name example only and is not how an excel file should look for import. Many fields needed for proper import are missing. See Ways to Add Inventory above for information on how to access the default template.
When entered the items will appear as such in the item list:
See the items in the Center Named Pocket Tee. The import file did not separate the color or the size as needed so each item was listed as defined. No Attribute or Size is showing in the correct field. They are only listed in the Item Name. Putting Items in a style grid properly will allow you to edit each item in one swoop as a style or each individual item on its own. Perhaps a color has been discontinued. The Import File should have been modified before import to show as this:
The items will then appear correctly at import. To correct the items already entered you can do 1 of two things. Export the incorrect items and re-import. Or delete the items and build them new manually.
Importing and Exporting Items for a Style Grid
Highlight the items you want to correct in the item list.
From the green “I Want To..” drop-down click export to Excel.
When Excel Opens make the necessary changes. For Jonas, we used text to columns under the Data tab.
An import file must have at least the item name, department, and UPC to import correctly.
Save the excel file when complete, making note of the file name.
In Point of Sale to File, Utilities, Import
Select Next, Inventory files, Next.
Select “Custom File” then Next
This will open your Data Import Wizard
Use the Browse button on the upper right to find the file you created.
Select the Sheet name the data you want to import is on.
Now click select Mapping. This is where you define which excel column will populate on the new inventory item. See below:
Click Save, name your mappings, and select OK.
Select Next on the data import wizard window.
Because we are updating items that are already in the system but will be changing item names, use the UPC option to find matching items for updates.
I always set it to prompt me and let me decide if it finds a duplicate item. We know these UPCs are already in the system so it will return a duplicate error and ask us how to proceed.
Leave System to Default department.
Select Next
Here is the duplicate error message we expected. We want to replace existing Data with import data, ignoring blank fields.
If we selected the first one nothing would change.
If we select the third option, any fields we did not map and are empty in our file would erase the information already in the system.
You can select remember my answer for this to avoid a prompt on every item for this import. It will not save you an option for the next import.
Select OK
After the system verified your file it will give you an overview of what changes are expected within this import.
We see here the 9 items we want to update were found and will be updated. No items will be added meaning none of these items we edited where unfound or altered in a way that will create a second item. Which is what we want to see. You should have a general idea from working the excel file of how many items you will be updating and how many you will be adding on every inventory update.
Nothing has been imported yet for this file. You can cancel at this time and make changes if needed.
Always backup a company file before import.
Item limit imports I have seen are typically no more than 3000 items at a time. And I recommend restarting your computer after every 3rd import in a row of 3000 items. Just keeps it running smoothly.
Select Import.
Save file Backup, and import will begin
Depending on the number of items imported this could take some time. Be patient and only cancel if absolutely necessary. Sometimes it looks like it is not progressing, but it actually is.
Once the import is complete it will advise you of the changes made.
Check your item list and ensure the items came through correctly and the changes you expected were done. If not revert to the backup you saved and try again after making changes on the excel file. Feel free to reach out to us at KHB Office to review your file before import or to troubleshoot issues that arise during import. We are always glad to help!
See the corrected items now:
Note how the items now are one item name and show an attribute and size. If I select one of them I can now view my inventory in the style grid.
Double click on an item
Click Style
Your item style grid will now show you the attributes and sizes in a grid form so you can check inventory for the item style across the board in relation to the attribute and sizing.
Manually adding Items using a Style Grid
Enter the Item as you normally would excluding the attribute and size.
Save
Select the new item and click the style button next to the size field
Select edit style in the lower right. See above
On the top of the grid Define your Attributes. Example: Color, Wide, Long/Short, Cotton
On the left side define your sizes. Example: Small, ¾’, 6.5 Shoe size
Enter zeros in the fields where the item is available. For example, every size is available in every color above. If the Orange Pocket Tee was not available in Small you would leave that corresponding square (top line 3rd from the left) empty.
Save
Locate each individual item using the style and attribute. Manually enter the UPC as described above “Manually Adding Items”. This is a bit tedious for large styles, and that is why it is recommended to enter style grid items as an import from an excel item.
Now when you change an items pricing or cost, if it is in a style grid it will prompt you to ask if you only want to edit that single item or if you want to change all the items in that style.
Once Jonas completed entering his styles in grids he found much more free time to fish and enjoy Nellie’s cookies. He was so glad that he contacted Certum Solutions to help him put his inventory file to work for him!
Please reach out to us and let us help you with your project! Or simply call and let us know if you enjoyed this post. We look forward to hearing from you!
If you like this article or need help with business management, QuickBooks, or other tips, you can find more info on how we can help you by contact us. You can also check out our Twitter, LinkedIn, and YouTube Channel for more content.
