Line Item Updates on Transactions Imports

The Import Assistant lets you 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 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.

• You should 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.

• You can remove a line item from an existing transaction by exporting your data, removing the unnecessary line in your CSV file, and then importing the file again. In this case, set the Overwrite Sublists option to True.

• 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.

CSV Data Example of 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.

CSV Data Example of 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.

The following example illustrates how you might structure your CSV import file. Because the order line ID is not specified, this example adds another line item to the specified sales order.

CSV Data Example of 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.

The following example illustrates how you might structure your CSV import file. Because the order line ID is specified, this example updates the matching line item in the specified sales order.

General Notices