V9 - Bulk editing items

Items are the most common data type to be edited in bulk.

For assistance with completing the data fields, refer to Item column descriptions for bulk edits, and be sure to observe the Guidelines for editing data. As mentioned in the guidelines, do not touch ItemId column in your spreadsheet. (By default, the file is protected to prevent that from happening. If you override this protection, be extremely careful. To override the protection in Excel, go to the Review ribbon and click Unprotect Sheet.)

Steps for bulk editing items

1. On the Task bar, go to Tools & settings > Export data.
    
2. Under the Inventory section, select Items. 
   1. Specify your desired Filters and Options.
      1. If you intend to change the quantity on hand and value on hand of items, you must select the Location affected by the changes.
      2. If the changes do not impact the current date, select the As of date required. (If changing quantities as of the current date, do not enter an As of date.)
   2. Under Retrieve your report, select Email or Download as the method of delivery for the exported data. In either case, a pop-up window will open.
       
      edit_noteNOTE: If you know that you have a large amount of data to process, use the Email option. The resulting file will be sent to you after it is generated.
      1. Email. Select this method if you have a lot of data. Sends the exported file as an attachment to the email address(es) you specify. The report file type options are Excel (.xlsx), Legacy Excel (.xls), and Comma-separated values (.csv).  Multiple email addresses must be separated by a comma.
      2. Download. Choose the desired report file type—Excel (.xlsx), Legacy Excel (.xls), or Comma-separated values (.csv).
          
3. Make a backup copy of the exported data file before editing.
    
4. If opening the spreadsheet in Excel and the exported format was also in Excel, you must unprotect the sheet (located under the Review ribbon).
    
5. Edit the data as needed, observing the following rules:
   1. Do not change the numbers in the ItemId column for existing data. Leave the ItemId field blank for a new item, as the system will assign an ID when the data is imported.
   2. Do not change any of the column headings. The format of the template is fixed, and it is very important. If you add, change, or delete column headings, your import will likely fail.
   3. Make sure that the number in the ItemId column stays assigned to that item.
   4. Delete a row from the spreadsheet if no changes are being made to it. Deleting a row does not delete the item from the database.
   5. The names of items must be unique. Names have a 100-character limit.
   6. Additional rules for items:
      1. Use the format a@b.c for email addresses.
      2. Use the prefix http:// or https:// for websites.
      3. If the QuantityOnHand value is changed, change the ValueOnHand also.
      4. Enter all applicable accounts (income, asset, COGS, and expense) for items.
   7. Break long files into multiple sheets. The upload will be faster if you limit the number of rows to 200 per sheet. The system will prompt you to indicate which workbook sheet should be imported. This is also important if an adjustment is created by the import.
   8. Do not leave any special features in the file (filters, formulas, freeze panes, hidden columns or rows, etc.). You may use these while editing the file, but remove or turn them off before saving.
   9. If you sorted the sheet by anything other than the ItemId (Column A), make sure that you perform an ascending re-sort by Column A and move all new rows without an ID to the end of the spreadsheet before saving.
       
6. Import the edited Excel file into SOS Inventory.
   1. Go to Tools & settings > Import data.
      1. Under the Importing dropdown, select Items. The Data import page will appear as shown below.
         
         
          
      2. The location must match the one selected for the export.
         1. Set the As of date, ensuring it matches the date that you chose when you performed the data export.
            1. Leave the date blank if making changes for today. The timestamp on the resulting adjustment will be for the moment the import is completed.
            2. If using any other date, the resulting adjustment will be 23:59.
                
         2. Total across all is the default option and will affect the default location.
            1. With this setting, the system will compare the quantity and value in total (across all locations) to the uploaded quantity and value. Where there is a difference, SOS Inventory will create an adjustment that will affect the location you have set as the default.
            2. This upload will also update any changes in the item definition.
                
         3. If you select a specific location: 
            1. The system will compare the quantity and value at that location to the uploaded quantity and value. Where there is a difference, SOS Inventory will create an adjustment that will affect the selected location. 
            2. This upload will also update item record definition and location-specific reorder points, max stock levels, and bins (when applicable).
                
      3. In the File field, drag and drop the Excel or CSV file containing your data—or use the File selector button to browse for the file on your device.
          
      4. Select Preview.
         1. If importing items from an Excel workbook with multiple sheets, you will need to select the appropriate sheet from a dropdown list in the Worksheet field before the preview data will display.
         2. Up to ten rows of data will appear for your review to ensure that you are importing the correct file.
             
      5. If everything in the Preview looks correct, select Import. The system will begin to process the data.
          
7. SOS will send a notification to your Notifications list that lets you know whether the import was successful. If there is an error while importing, the notification will state "Import failed" and provide the reason for the failure. One of the common reasons for failure is column headings that are incorrect. You must keep the columns in the template and leave the headings the same, or the upload will not complete properly.
    
   edit_noteNOTE: If you are importing multiple sheets, please wait to receive the notification, "Item import complete," before importing another sheet from the workbook.
8. As previously mentioned, if the system detects changes in quantity and value for items, it will create an adjustment with Bulk edit in the Comment column on the list. It will have an eight-character, randomly generated number in the Ref # column. If the changes were not intentional, delete the adjustment. Otherwise, be sure to follow the important instructions below:
    
   warningIMPORTANT: The adjustment transaction needs to be resaved to send the adjusting journal entries to QuickBooks Online. In certain circumstances (such as if you are editing during a transaction period), you may want to backdate this adjustment to show starting values on a certain date and time. To do this, change the transaction date and time, then click the Compute button next to the Adjust cost basis by column header to recalculate the values.