Watch this video and/or read this article to learn how to create a bank deposit with CloudExtend Excel for NetSuite.
This article was created at the request of a customer who has payments created automatically in NetSuite and then receives a file from their bank that lists the payments that made up a deposit for the day.
We decided to create this article on how we solved this problem for one specific customer hoping other customers might benefit from the approach. Let us know if this helps you by clicking the emoji at the bottom of this article.
- Download payment file from bank
- Download undeposited payments from NetSuite
- Use Excel VLOOKUP to match payment records
- Upsert data to NetSuite from CloudExtend template
All of the payments are already recorded in NetSuite and matched up to the proper Invoices via an existing integration.
The bank file has the following fields.
- Transaction Date
- Document Number (maps to NetSuite Transaction Number, ie Invoice Number)
- Payment Amount
- Other fields not necessarily relevant to complete the deposit
The CloudExtend template only has a few required fields.
The fields highlighted in green in the screenshot below are required. All other fields should not be populated by the end user as they will be set by NetSuite automatically during the update. After the update, the user may click Refresh to see the values in these fields.
- Tran Date: Set to the date of the bank deposit. Leave empty to default to current date.
- Account: Set the G/L account of your bank the funds were deposited in. Leave empty to default to your default bank account.
- Location: If location is a mandatory field for you set it here. If it's not mandatory you can leave it empty or remove from the template.
- Payment List: Id: This is the internalID of the transaction the payment was applied to, for example the internalID of an Invoice number. You'll learn how to retrieve this in a subsequent step.
- Payment List: Deposit: Set this to TRUE to include this line in your bank deposit.
Populating the template
STEP 1 - Build a Saved Search
We need a way to find the Payment List: Id field (which is the NetSuite internalID for the record the payment was applied to). Since the bank is providing us with the Invoice Number we'll need to pull in Payments from NetSuite that have not yet been deposited to locate this internalID. Do this by creating a saved search against payment records. I used the search below and included undeposited cash sales as well. Cash sales may not be relevant and may be removed from criteria if desired.
STEP 2 - Run your search directly in Excel
Following the instructions in this article (download results as raw data, 1st option) locate and run the saved search you created above. Your results should look something like that below. Note - the payment amount is here as reference. While not part of this solution you may want to verify the payment in NetSuite matches the payment amount for the invoice provided by your bank before proceeding further.
STEP 3 - Use VLOOKUP to populate Payment List: Id
Add a column to your bank file (column D in our example) and then look for a match of Document Number (this is in the invoice paid) from the NetSuite search results and then return the internalID (2nd screenshot below).
VLOOKUP formula on bank file
NetSuite saved search results.
Step 4 (Final Step) - Populate CloudExtend Template and Upload to NetSuite
For this last step you will need to copy and paste the Payment List: Id field form the bank file into the CloudExtend template (green hilite). Next, you will enter the Tran Date, Bank Account Number, Location, and set Payment List: Deposit = TRUE (yellow hilite).
Create the NetSuite deposit by selecting Update. Once the update is successful use the Refresh action to pull in the fields auto populated by Netsuite if desired. The results will look something like this.