Customer Payment Import
NetSuite Customer Payment transactions are records of payments. You can use the customer payment import to import historical sales data.
To maintain performance, 5000 lines per transaction is the maximum limit for transactions submitted through CSV import. The number of lines a record can contain is specific to the record.
You can import unapplied payment amounts, or apply some or all payment amounts to invoices. If you are importing both customer payments and invoices, and you want to apply imported payments to imported invoices, you need to import invoices first. Include unique IDs, preferably external IDs, for your invoice records. You then can reference invoices in your customer payment import file by including their unique IDs as Invoices sublist field values.
A payment can be associated with an invoice through the internal ID or external ID only. The transaction ID is not unique and cannot be used.
-
The customer payment import uses the following logic to apply payments to invoices, based on values for the Payment Amount field, and Invoices Payment sublist fields:
-
If the Payment Amount value is greater than the total of all Invoices Payment values, the remainder is an unapplied payment.
-
If the Payment Amount is equal to the total of all Invoices Payment values, the entire payment is applied.
-
If the Payment Amount is less than the total of all Invoices Payment values, only the Payment Amount is applied, and no error is returned.
-
-
If you import customer payment records with a value of False for the Undep. Funds field, you must supply an account for each of these records. Otherwise an error occurs. if Undep. Funds is set to True, this value overrides Payment Method, and if Undep. Funds is set to False, Account overrides Payment Method.
-
If the Use Account Numbers preference has been enabled at Setup > Accounting > Accounting Preferences, imported values for the Account and A/R Account fields should include both account number and name, for example: 11000 Accounts Receivable. If this preference is not enabled, values should be account name only, for example: Accounts Receivable.
-
If your Chart of Accounts includes more than one account of the Accounts Receivable type,you should: map the A/R Account field in the Import Assistant, and include a column with A/R account values in the CSV file. The A/R account value for each customer payment record should match the account to which related invoices were posted. If you do not import an A/R account value for an imported payment record, the default A/R account is used, and an error occurs if the payment is applied to an invoice that was posted to a different account.
Customer Payment Import Examples
The following example shows a customer payment import where the payment amounts are not applied to invoices.
|
External ID |
Customer |
Payment Amount |
Date |
|---|---|---|---|
|
Cust_Payment_3 |
John King |
6500 |
4/3/2017 |
|
Cust_Payment_4 |
Marie LaBelle |
15000 |
4/3/2017 |
|
Cust_Payment_5 |
Tim Johnson |
8000 |
4/3/2017 |
The following example shows a customer payment import where the payment amounts are applied to invoices.
|
External ID |
Customer |
Date |
Invoice ID |
Payment Amount |
|---|---|---|---|---|
|
CP-001 |
John Woodhouse |
5/18/2017 |
INV-425 |
10000 |
|
CP-002 |
Tim Johnson |
5/16/2017 |
INV-633 |
6500 |
|
CP-003 |
Ellen Guest |
5/4/2017 |
INV-732 |
1800 |
For details about fields that can be mapped in the customer payment record, see the SOAP Schema Browser’s customer payment reference page. You can use the field definitions here as a basis for creating your own CSV import template file. For information about working with this tool, see SOAP Schema Browser.
Review the following guidelines for Customer Payment imports:
Supported Customer Payment Sublist Data Imports
The Customer Payment import supports the import of Invoices sublist data. Each line in this sublist represents an invoice to which the payment is applied. The sum of Payment field values for sublist records represents the total amount of the payment.
In this sublist, the required Invoice key field holds the transaction IDs for invoices to which payments are applied. An additional, optional Line key field holds the line numbers for the invoice lines to which payments are applied, if payments need to be applied to specific lines.
If you use only the Invoice key, the following error may be returned to indicate that a unique reference was not found, “Ambiguous sublist reference; multiple matches exist for key value <>”.
An import can add new invoices or invoice lines to which a payment is applied, change the amounts applied to existing invoices or invoice lines, or remove invoices or invoice lines.
When the Overwrite Sublists advanced option is not enabled (which is the default), CSV file sublist data selectively updates existing invoices data, based on the Invoice key field. When this option is enabled, CSV file sublist data completely replaces existing invoices data. For example:
-
- If the existing Invoices sublist data is:
- Invoice A: $100
- Invoice B: $200
-
- And CSV file sublist data is:
- Invoice A: $120
- Invoice C: $180
-
- When Overwrite Sublists=F, post update sublist data is:
- Invoice A: $120
- Invoice B: $200
- Invoice C: $180
-
- When Overwrite Sublists=T, post update sublist data is:
- Invoice A: $120
- Invoice C: $180
The Customer Payment import also supports the import of Accounting Book Detail sublist data. The Accounting Book Detail sublist is available only when the Multi-Book Accounting feature is enabled, and only when more than one active accounting book exists. This sublist is selectively updateable based on the Accounting Book key field.
Complete deletion of sublist data currently is not supported. For more information, see Sublist Data Import.
Using Import Assistant for Customer Payments Mass Update
You can perform a mass update of existing Customer Payments main fields by exporting Customer Payments saved search results, changing data externally, then using the Import Assistant to import modified data, using the Update data handling option. For more information, see Creating Transaction Saved Searches for Reimport.
The Import Assistant is available at Setup > Import/Export > Import Tasks > Import CSV Records. After you select the record type for import, you choose the import character encoding. For more information, see Select a Record Type for Import and Choose Import Character Encoding.