The Import Assistant allows you to update items data for the following types of transactions: Cash Sale, Estimate, Invoice, Opportunity, Purchase Order, Sales Order, Vendor Bill, Vendor Credit, and Vendor Return Authorization. You can modify data for existing line items and/or add line items to existing transactions.
The Items sublist for transactions uses either Line/Order Line or Item as a key field to determine how to update or add line items.
- If a CSV file row includes values for both Line/Order Line and Item fields, Line/Order Line takes precedence over Item.
- CSV file rows with key field values that match existing line item key field values are used to update existing line items. CSV file rows with non-matching key field values are added as new line items.
- An error is returned if the import cannot determine which lines to update. This error occurs if there are two existing lines with duplicate Item values and if there are two lines in the CSV file that match a single existing line.
- It is recommended that you use Line/Order Line as a key field. Because transactions may include multiple lines with the same Item value, using Item as the key may cause errors resulting from duplicate Item values.
- If you import line items to update a transaction and these new line items have the same Line/Order Line values as existing transaction line items, the import results in the new line items overwriting the existing line items. To instead add the line items, map a column with null values to the Line/Order Line field for the import.
- For updates of existing transaction line items (based on matching key field values), by default only mapped field values overwrite existing line item field values; fields that are not mapped for import do not change. If you want all CSV file values to overwrite existing values, set the Overwrite Sublists option to True. Generally you do not want to overwrite sublists, as there are many values set at the time of transactions that you would not want changed after the fact, such as Rate.
Review the following examples to get an understanding of how to format a CSV file to update transaction item lines.
Example CSV Data - Unsuccessful Transaction Line Item Update
- Existing Sales Order Transaction Lines
- Item Y, Quantity 1
- Item Y, Quantity 2
- CSV File for Import
- Item Y, Quantity 5
- Item Y, Quantity 5
- Item Y, Quantity 15
This data results in an error because the import is trying to use the Item value as the key, and it is not clear which transaction lines should be updated.
Example CSV Data - Successful Transaction Line Item Addition
- Existing Sales Order Transaction Lines
- Item Y, Quantity 1
- Item Y, Quantity 2
- CSV File for Import
- Item Y, Quantity 5, Order Line (no value)
- Item Y, Quantity 5, Order Line (no value)
- Item Y, Quantity 15, Order Line (no value)
- Updated Sales Order Transaction Lines
- Item Y, Quantity 1
- Item Y, Quantity 2
- Item Y, Quantity 5
- Item Y, Quantity 5
- Item Y, Quantity 15
The inclusion of the Order Line column in the CSV file prevents the error. Because the Order Line column does not have any values, CSV file lines are not matched with any existing line items, and are simply added.
Example CSV Data - Successful Transaction Line Item Update
- Existing Sales Order Transaction Lines
- Item Y, Quantity 1, Order Line 22
- Item Y, Quantity 2, Order Line 25
- CSV File for Import
- Item Y, Quantity 5, Order Line 22
- Item Y, Quantity 5, Order Line 25
- Item Y, Quantity 15, Order Line (no value)
- Updated Sales Order Transaction Lines
- Item Y, Quantity 6, Order Line 22
- Item Y, Quantity 7, Order Line 25
- Item Y, Quantity 15
The first two CSV file lines are matched to existing line items so the quantity amounts are added. The third CSV file line is not matched, so it is added as a new line item.
