V8 - Bulk editing items (steps)

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, it is extremely important that you do not mix up the ItemId column in relation to the other columns. (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 Company menu > Data tools > Export data.
    
2. Under the Standard section, select Items. The system will take you to the Item Export page.
   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 Columns, select Add all columns required for import.
   3. In the Actions section:
      1. In the Format dropdown, select the desired Excel file format. (Although CSV file format allows the fastest downloads, it should not be used for items.)
      2. Select Save as Excel/CSV to download the file or Send via email with the exported file as an attachment. (If you have a large amount of data to process, the system will tell you to use Send via email.)
          
3. If opening the spreadsheet in Excel and the exported format was also in Excel, you must unprotect the sheet.
    
4. Make a backup copy of the exported data file before editing.
    
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 change or delete column headings, your import will likely fail.
   3. Make sure that data in the ItemId column stays matched to the proper row.
   4. Delete a row from the spreadsheet if no changes are being made to it.
   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 figure 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 under 1,000 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 Company menu > Data tools > Import data.
      1. Under the Importing dropdown, select Items.
      2. If modifying quantity on hand and value on hand, choose the same Location and As of date as your exported file. (If uploading quantities as of the current date, do not enter an As of date.)
      3. In the File field, choose the Excel file containing your data, then select Preview.
      4. The first 10 rows of data will appear for your review. If everything looks correct, select Import. The system will begin to process the data.
          
7. SOS will send a notification that lets you know whether the import was successful. If you receive an error message while attempting to import or edit items, the cause is almost always a missing or incorrectly titled column.
    
8. If the system detects changes in quantity and value for items, it will create an adjustment with Bulk edit in the Notes 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--or on the Adjustments list (Operations menu > Inventory > Adjustments), use the Add to sync action for that transaction--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.