Bulk editing items, Part 2: Working with data
Set yourself up for a successful import by understanding how to edit data properly in an item spreadsheet.
What you’ll learn in Bulk editing items, Part 2: Working with data
- Become familiar with key columns on the items bulk edit spreadsheet and how to edit the data in them
- Know the follow-up actions needed on the adjustment transaction that SOS Inventory will create when the data file is imported into the system
Video Transcript
Bulk editing items: topics
Welcome to Bulk Editing Items, Part 2: Working with Data. In this video, we will go through the columns of an item bulk edit spreadsheet and explain how to work with the data as you make your edits. In addition, we will review the follow-up actions needed on the adjustment transaction that SOS Inventory will create after you import your edited data into the system.
This presentation is one of several video tutorials that are relevant to bulk editing items. If you have not done so already, please be sure to view Bulk editing: Introduction, Bulk editing rules, and Bulk editing items, Part 1: Preparation.
This presentation is not intended to cover every column on the spreadsheet. The focus will be on columns that have important information of which you need to be aware. If you want a comprehensive explanation of all bulk edit columns, please consult Item Field Definitions for Data Export/Import in the SOS Inventory User Guide. Now let’s take a look at working with your spreadsheet data.
In Column A is the ItemId, which is designated for your items’ SOS-assigned numbers. As mentioned in the Bulk editing rules video, you should not make any changes to Item ID numbers.
If you add a new item, leave its ItemId cell blank, as SOS will assign a number to the item when you import the file into the system.
You can sort your data to make it easier to work with, but be sure that the ItemId stays matched to its proper row.
In addition, if you sort the columns while working with the data, make sure you perform an ascending sort by the ItemId column before you import your file into SOS Inventory.
Next to the ItemId column is the Name column. Item names must be unique. The only exception to the unique name rule is if items sharing the same name are assigned to different categories. The maximum character length for a name is 100 characters, but we recommend no more than 89. The remaining 11 characters are reserved in case you delete the item and later decide to return it to your Items list. The additional characters are used by the system, which adds (deleted) to the end of item names in the Deleted Items list.
As mentioned in the Bulk Editing Rules video, if you adjust your Quantity on Hand number, you should also change the Value on Hand figure as well. However, for an existing serial- or lot-tracked item, do not change the values in these columns. If adding a new serial- or lot-tracked item, keep the Quantity on Hand and Value on Hand values at zero.
Column F, the Category column, is used to assign an item to a category. In Part 1 of Bulk Editing Items, it stated that a category must exist in SOS Inventory before you can assign items to it. In addition, be sure that the case and spelling you use when typing the category matches the case and spelling that exists in SOS Inventory. If you are using the bulk edit to create a new category, enter the name of the category in the Name column and then enter Category in Column Q, TypeOfItem.
If you populate the Preferred Vendor column, be sure to use vendors who already exist in SOS Inventory. Again, make sure that you use the same spelling and case for the vendor name as it is found in the system.
If the item has a fixed sales price, enter the correct value in the SalesPrice column. SalesPricePercent is the markup pricing, which is used when the sales price will be a certain percentage above the Cost amount instead of a fixed price. Make sure you enter the appropriate data in these columns. Also, note that if you enter a sales price percentage for an item, its UsePricePercent field must be listed as true.
The Bin column is used only for a location that has been set up in the system for bin tracking. You may use the bulk edit process to add new bins. Simply enter the appropriate bin number for the item. If the bin does not already exist in the system, SOS Inventory will create it when you import the file.
In the IsTaxable as well as some other columns, you will notice that the words true or false are entered. We came across this earlier when we mentioned that entering true in the UsePricePercent column should be done if an item’s sales price is marked up by a percentage. In computer programming, true mean yes, while false means no. Although this example shows the words in all caps, you do not need to capitalize them.
Under the TypeOfItem column, type the appropriate item type.
The options are Category, Inventory Item, Non-inventory Item, Expense, Service, Item Group, Labor, Overhead, Assembly, and Other. Labor and Overhead are available only on the Pro Plan. The Other option is often used for something like notes. In any case, make sure that you use the correct spelling, case and spacing when designating the item type. Note that the i in Non-inventory is not capitalized.
You should not need to make any changes in the SyncWithQuickBooks column, as it will default to your Inventory Settings in SOS Inventory. If you are connected to QuickBooks Online, most items will sync, but a few will not.
Do not make any edits in the Deleted column. If you need to delete items or restore any items that were deleted, make those changes within the system using an action or batch action from the appropriate list page.
The Deleted Items list can be accessed under the Miscellaneous section of the Reports menu.
If you are adding a new serial- or lot-tracked item, type true in the appropriate column, but do not change an existing item’s tracking status unless the Quantity on Hand and Value on Hand is at zero. If nothing is entered into these columns, SOS will use false as the default.
Do not change the tracking status of an existing item unless the item’s quantity on hand is at zero. If an existing item has a quantity, please follow the procedures outlined under the Serial Inventory and Lot sections of the SOS Inventory User Guide.
On the SalesItem, PurchasingItem, and ManufacturingItem columns, enter true if you want the item to appear on sales, purchasing, and/or manufacturing forms—or false, if not. The MaxStockLevel, Weight, WeightUnit, Volume, and VolumeUnit columns are optional.
True in the Archived column means that an item is archived, while false means it is unarchived. You can archive or unarchive items as desired.
If you want to add a website where the item can be found online, include the prefix http or https, followed by :// in the WebUrl column. You do not need to include www in the web address.
In the Tags column, you can add multiple tags for an item. Be sure to use a comma between tags.
In the UpdateInventoryInShopify and UpdateInventoryInBigCommerce, enter true if you previously set up Shopify or Big Commerce.
The Shippable column is intended to indicate whether an item is always shippable. Enter true if the item is a Service, Non-inventory, or Expense item and you always want it to be available on sales and invoice transactions.
The IncomeAccountName, AssetAccountName, COGSAccountName, and ExpenseAccountName columns are extremely important. When adding a new item via a bulk edit, the account names used on that row must be unique in the list of accounts because the system will not take the account number into consideration. You must have accounts for items, but the accounts you need depend on the item type.
If the item type is Inventory, Assembly, or any item that is tracked, it needs an Income, Asset, and COGS account.
Income and Expense accounts are required for Non-inventory, Item Group, Service, and Expense item types.
No account should be assigned to a Category.
As mentioned in Part 1 of our Bulk Editing Items video, several columns of information must be pre-defined in SOS Inventory before you can use them in a bulk edit. Sales tax codes and purchasing tax codes must be pre-defined for international accounts. If you assign classes and warranties, these must be pre-defined in the system as well.
When you import a bulk edit file, SOS Inventory will automatically create an adjustment transaction if it detects any changes in item quantity or value. Be sure to check whether the system created an adjustment. Go to the Adjustments list, located under Inventory on the Operations Menu.
Then enter bulk edit in the Quick Search field.
Look for the date of the import in the Date column to see whether the system created an adjustment. The text Bulk Edit will be in the Memo field. Select Edit on the Actions Menu to open the transaction.
Review the line items on the transaction. If the adjustment is as intended and no edits are needed, close the transaction. If any changes are needed, make the edits and resave.
If no edits were made, select Add to Sync under the adjustment’s Actions Menu, as the system will not sync the transaction automatically. However, if edits were made, you do not need to manually sync the adjustment. SOS Inventory will add it to the Sync Queue after the transaction is saved. If the adjustments created by the system were unintentional, delete the transaction record from the Adjustments list.
This concludes Part 2 of Bulk Editing Items. Thank you for watching this presentation!
