CSV import: Credit memo with apply to invoice
NetSuite supports being able to import a Credit Memo and apply it to a Sales Invoice in the process. This is very straight-forward, as it is exactly like importing a Credit Memo without applying it, with the exception of two additional fields to populate and include in the import mapping. The key to making Apply work is to populate two fields in the template: Apply Applied and Apply Payment. Payment is the amount being applied. Applied is the Internal ID for the Sales Invoice the Credit is being applied-to.
This can be helpful in situations where a large volume of credits are processed and it is desired to bypass default functionality and have NetSuite apply the full open amount, and instead dictate how much to apply to each invoice.
Below is a step-by-step description of how to correctly build and populate an import source file, and how to correctly build the field mapping in the CSV Import itself and execute it.
- This example assumes Department and Location are required fields for a Credit Memo.
- This example assumes one currency, US Dollars.
- Item Price Levels are used in this environment.
Build a template
Create an Excel spreadsheet with the following fields:
- External ID
- Credit number
- Posting Period
- To be Printed
- To be E-Mailed
- To be Faxed
- Price Level
- Sale Amount
Save as an Excel Workbook. Note that cells can be General format.
Populate the cells in the following manner. This example is creating two Credit Memos. One being applied to two invoices, therefore it will have two rows. This Credit Memo is for $200, we are applying $100 to each Sales Invoice. The other is for $450, and is being applied to three different Sales Invoices, so it will have three rows in the spreadsheet. We are applying $150 to each of the three invoices. The spreadsheet will be saved first as an Excel Workbook. Then it will be saved again as a .csv file. The .csv file is what the import will use as the source file.External ID: this field is needed in the template. It is not updating any field in NetSuite nor is it used as the Credit memo #. Its purpose is to define the individual credit memos to create. That is how NetSuite know which lines belong to a given transaction. This value can be numeric or alpha-numeric. Every line r group of lines per desired credit memo must have a unique external ID. A given External ID can be used again in a future import. For example, the desired result of the Import is to create two Credit Memos. One Credit Memo will be applied to two invoices, so it will have two rows. The second credit memo will have three rows. The two rows for Credit Memo A will have External ID = CR001, the three rows for Credit Memo B will have External ID = CR002. CR001 and CR002 can be used again on future imports.Credit #: NetSuite will assign the Credit Memo # based on the next number available. However this field needs to be included in the template and populated.Customer: Must match exactly how NetSuite stores the name. This rule applies to all fields.
Date: Use the desired date for the Credit Memo transaction
Posting Period: Use the desired period for GL Impact
Department and Location: Assign as desired.
Currency: Use USD for all rows
Exchange: Must = 1 for all rows
To Be Printed/E-Mailed/Faxed: Set these to TRUE (checked) or FALSE (unchecked) as desired.
Item: This is the item typically used on Credit Memos. This is usually a Discount for Sale Item.
Quantity: Can be 1 for all rows since this is a Credit Memo being created.
Price Level: In this example price levels are in Use. Custom is used to define the dollar amount ourselves.
Rate: This is the unit sale amount.
Sale Amount: This is the extended sale amount, which will equal Rate since we are dealing with a quantity of one.
Apply_Applied: This the NetSuite Internal ID for the Invoice that the credit line is being applied-to. The Internal ID can be found in a List View or the URL when viewing the transaction itself. Note: It may be necessary to turn “On” the “Show Internal ID” option in Personal Preferences.
Apply_Payment: This is the amount being applied. These can certainly be less than the Sale Amount, and that will leave the Credit Memo Open instead of Fully Applied. Apply_Payment however cannot exceed what is in the Sale Amount Field.
Save as an Excel Workbook, then save it again but this time Save As a .CSV file. Choose CSV (MS-DOS) (.csv).
End result should look like the following:
Fields External ID through To Be Printed:
Fields To Be E-Mailed Through Apply_Payment:
Build CSV Import
Note: The menu paths illustrated are visible when logged-in as an Administrator.
To create the import, navigate to Setup > Import/Export > Import CSV Records
Screen 1: Scan and Upload CSV File
Select Import Type = Transactions
Record Type = Credit Memo
Character Encoding: use default value Western (Windows 1252)
CSV Column Delimiter: use default value Comma
Select One File to Upload and select the .CSV file just created, then click Next.
Screen 2: Import Options: Select “Add”, we are adding transactions. Then click Next.
Note: Ignore Advanced Options. However a common error is using the wrong Form. The Custom Form can be changed under Advanced Options.
Screen 4: Field Mapping (NetSuite handles Screen 3 File Mapping).
This is where you actually setup the field mapping. We are mapping the fields in the .csv source file to the proper NetSuite field. To build the mapping go row by row. Start with the NetSuite field list on the right, select the NetSuite destination field. The cursor will move to the next row, place it back on the row where you just selected the NetSuite field. Then select the desired source field form the list on the lift, which is the .CSV source file. Repeat until all fields are mapped.
Field Mapping Screen:
The end-result will look like this:
Credit number: click the edit button, the pencil to the left. Set the field value as follows:
This will ensure NetSuite assigns the Credit Memo number
Click Next when finished mapping and go to the last screen in the Import Setup.
Screen 5: Import Assistant
This is where you Save and Run Import. Future use of this Import will ask you to Save and Overwrite the import. That is normal. Select OK.
Name the Import, select Save & Run.
Click the Import Job Status Link to see progress.