NetClose - Auto-Reconciliation Saved Search Setup
Overview
NetClose Reconciliations can auto-reconcile GL balances to subledger balances by utilizing saved searches. Saved searches for certain accounts are provided out-of-the-box. See the following articles for more information:
- Auto-Reconciliation Modules (AP/AR/NetClose Amortizations)
- Saved Searches to Reconcile NetSuite Fixed Asset Module (FAM)
- Saved Searches to Reconcile NetAsset
- Saved Searches to Reconcile NetLease
Outside of the above, users may create saved searches for other subledger information within NetSuite following the instructions below.
Use Cases for Saved Search Auto-Reconciliation
Non-GL-Impacting Subledger (Preferred)
A non-GL-impacting subledger is any set of records that tells you what your GL balance should be for a given set of accounts without actually booking any transactions to affect the general ledger. If you're using any of our primary Netgain products (NetLease, NetAsset, NetClose Amortizations), then you have access to one of these subledgers.
This works best when the subledger represents the entire expected GL balance. If there are known out-of-subledger items, the reconciliation can still be useful— but it may not auto-reconcile fully.
If the subledger from your saved search doesn't match the GL balance in your account, then you'll know that something was booked to those accounts that isn't represented by your subledger, and you can find and adjust that transaction (see our Prepare and Review Reconciliations article), or modify your subledger accordingly.
This type of saved search reconciliation works best with ending balance reconciliations.
Transaction Based
If you have transactions that are already well-reviewed or well-controlled by existing processes and therefore can be considered already reconciled, you may be able to use a transaction based saved search to automatically reconcile your accounts. Our Auto-Reconciliation Modules take care of reconciling your AP and AR with this transaction based saved search method.
If you have any other accounts you'd like to reconcile with a transaction based search, you can create a saved search that looks for all transactions affecting a chosen set of accounts that meet a particular criteria (an approver field is populated, the record was created by an integration user from your external system, etc.).
If this balance matches your GL balance, you can have confidence that every transaction in your GL for these accounts have undergone review or have been subject to strong controls. If there is a difference (someone erroneously booked a transaction to the particular account), you can investigate the results of your saved search and compare that to your GL to find any differences.
Setting Up the Saved Search
General Saved Search Information
This article assumes familiarity with NetSuite saved search functionality. If needed, here are some useful articles from Oracle NetSuite for your reference.
Advanced Search Overview (the type of search we are creating for this is called an advanced search in NetSuite)
Defining Filters to Use in Search
Determining Fields Queried in Search
Account Amount - Summed Column
Required
The only strict criteria for a saved search used in this reconciliation method is that you must have a single column of values, the sum of which represents your expected GL balance.
You can have other columns with other amounts or information, but one column must represent what you hope the balance of your GL accounts will be. Here's an example of a NetLease subledger-based saved search:
The boxed column is the column that represents the balance of our lease liability accounts; the column to the right could be used to reconcile another account if you wanted to separate the accounts, but it can also just be in the saved search for informational purposes. The accounts, IDs, and dates are also informational and aren't necessarily required, but they help make the saved search make sense to us as people, and not just to the system. This is especially important because the saved search you create will automatically be saved onto your reconciliation. You can use this as support for auditors, for help in identifying differences between your subledger and GL, or for historical record keeping.
The key with a saved search is that it needs to actually pull the balances that you're interested in and put them into a single column. Sometimes you may need to use formulas in your saved searches to get a single column that has the numbers you need.
Date Filter Requirement
Additionally, you need to make sure that the balance you're pulling can be filtered for a particular date. In the case of the subledger above, the NetLease schedule lines that we're looking at have a period date on every schedule line so you know exactly which period the balance corresponds to. The script that looks at your saved search and compares it to your GL will apply a filter of the last day of your chosen accounting period to your saved search--you just need to tell it which field to use for that filter. So make sure that you have some way of filtering your data that would make this date filter make sense (this is why it's hard to use dynamically-updating records; they reflect the current state of the object they're related to and don't necessarily have a way to identify which period that number corresponds to).
It's not necessary that this date field be included in your saved search results, but you need to have a field on the record you're searching for (or a related record) that you can use as a date filter.
(Optional) Account Segregation Fields
If you're segregating your account grouping by account (for more details on what this means, refer to the Account Grouping Basic Configuration article) you can make sure that the object you're searching for (or a related record) has some way to identify which account corresponds to the amount on that line. The lease example above is a poor example--there are 3 different accounts on each line; a single account field does not uniquely identify which account the number in your amount column belongs to. A better example using the NetClose Amortization module is below.
Notice that the "Balance Sheet Account" column is a single account value that represents which account the balance amount belongs in. If you have this information, you can easily use a single saved search on a single account grouping that's segregated by account and automatically reconcile all related accounts (all Netgain products have this functionality built into them). You don't need this account field on the saved search itself (you just need to be able to identify the field on the underlying record you're searching on), but it's helpful to have it on the saved version of this saved search that'll be attached to the created reconciliation item.
(Optional) Subsidiary Segregation Fields
If you're segregating your account grouping by subsidiary (for more details on what this means, refer to the Account Grouping Basic Configuration article) you can make sure that the object you're searching for (or a related record) has a field to uniquely identify which subsidiary the row amount belongs to. None of the previous examples we've looked at have used this, but the below NetAsset example incorporates everything we've talked about so far.
Notice that each asset balance has a subsidiary, account, and gross asset value associated with it. I can now use that subsidiary field to tell my account grouping which field should be filtered to find corresponding balances for my subsidiaries, if I'm segregating my account grouping by subsidiaries. Again, this field doesn't need to be included in your saved search (you just need to be able to identify the field on the underlying record you're searching on), but it's helpful for the downloadable version of this saved search that'll be saved to the created reconciliation item.
Setting Up the Account Grouping
With your saved search created, you're ready to set up your account grouping to use the saved search. This part involves a bit of trial and error, but it's worth it to get your auto-reconciliation working. We'll start by taking a look at an account grouping. We'll be focusing on the Auto-reconciliation Rules - Search section.
First, check the Subledger Within NetSuite; Agrees to Search checkbox. Then, the other fields will be visible and editable. Below, we'll walk through each of these fields.
Search
The Search field is where you select the saved search you created previously to use as your subledger to reconcile against. If you haven't made one yet, scroll up to the "Setting Up the Saved Search" section of this article. For the purposes of this portion of the article, I'll use the same NetAsset saved search referenced in the last screenshot of the previous section. I'll include it below for reference.
Search Column Label
The next field, Search Column Label, is where you tell the script that will be running and reading the values from this saved search which column in the search it needs to sum. This is the only field where you will use a column label to do this; all of the other fields will use the Internal ID of the underlying field value, but summing the column label allows you the flexibility to use formula or other calculated values as your summed amount rather than having to sum a particular field. In this example, the "Gross Asset Value" column is the one that we want to sum to pull in the balance of our subledger.
Record Joins on Internal ID Fields
Before we move on to the other 3 fields on the account groupings record, it's important to talk about record joins. A record join is when we reference a field that is not on the object we're searching for, but is on a related record. We can use a join to reference a field from that related record and use it in our saved searches, or in this case, in our reconciliation item creation script. Take a look at the difference between the Search Date, Subsidiary, and Account Field Internal ID field values from our Fixed Asset Capitalization account grouping, pictured below.
Notice that the date and account fields have a period in the middle of them, and what looks like 2 Internal IDs combined, while the subsidiary does not. That's because the NetClose NetAsset Asset Capitalization Reconciliation saved search we're using is a saved search on the NetAsset Asset record, and the date we're looking for is on the NetAsset Schedule Line record, while the account is on the NetAsset Type record. These two other record types are linked to the NetAsset Asset record, so we can first find the field that links them and reference the Internal ID of that field, put a period after it, and then reference the Internal ID of the field we're interested in. The linking field may be on either the record you're searching for, or on the related record. In this case, we have an "Asset" field on our NetAsset Schedule Line record, and that's the linking field to the NetAsset Asset. Alternatively, we have a "Type" field on our NetAsset Asset record that links it to the NetAsset Type record. In contrast to both of these, the subsidiary field exists on the NetAsset Asset record (the record our saved search is based on), so we can just include the Internal ID of that field in our Search Subsidiary Field Internal ID.
The main takeaway here is that if you need to use a value on a related record, you can. You'll just need to identify the field that links the records together, reference its Internal ID, put a period after it, and then reference the Internal ID of the field you're interested in. This may not be applicable in many use cases, but it is extremely helpful when needed (it's the only way to make some complicated auto-reconciliations work). This can be used only on the Internal ID fields--date, subsidiary, and account.
Search Date Internal ID
The Search Date Field Internal ID field is where you tell the reconciliation generation script which field to filter for the reconciliation period. If you're generating reconciliations for your December 2022 period, the script will filter your saved search for all entries on or before the last day of your December 2022 period (for ending balance reconciliations; activity reconciliations will have a filter applied from the first day of your accounting period to the last day, to capture only the activity in that period).
You will need to provide the Internal ID of this field so the script knows which field to filter on. The Internal ID of the field can be found by clicking on the field help for the field you're interested in filtering. If you don't see the Internal ID when you do this, hover over the house icon in the upper left corner of your NetSuite instance, go to "Set Preferences" and on the right-hand side under the "Defaults" field group, check the box next to "Show Internal IDs". Remember, you can use joins here if you need to.
(Optional) Search Subsidiary Field Internal ID
The Search Subsidiary Field Internal ID field is only used if you are using the Segregate by Subsidiary setting on your account grouping. You'll use this field to tell the reconciliation generation script which field to apply a subsidiary filter to in order to break the total amount in your saved search into the amounts corresponding to the subsidiaries defined by each line of your saved search.
You will need to provide the Internal ID of this field so the script knows which field to filter on. The Internal ID of the field can be found by clicking on the field help for the field you're interested in filtering. If you don't see the Internal ID when you do this, hover over the house icon in the upper left corner of your NetSuite instance, go to "Set Preferences" and on the right hand side under the "Defaults" field group, check the box next to "Show Internal IDs". Remember, you can use joins here if you need to.
(Optional) Search Account Field Internal ID
The Search Account Field Internal ID field is only used if you are using the Segregate by Account setting on your account grouping. You'll use this field to tell the reconciliation generation script which field to apply an account filter to in order to break the total amount in your saved search into the amounts corresponding to the accounts defined by each line of your saved search.
You will need to provide the Internal ID of this field so the script knows which field to filter on. The Internal ID of the field can be found by clicking on the field help for the field you're interested in filtering. If you don't see the Internal ID when you do this, hover over the house icon in the upper left corner of your NetSuite instance, go to "Set Preferences" and on the right hand side under the "Defaults" field group, check the box next to "Show Internal IDs". Remember, you can use joins here if you need to.
For all of these Internal ID fields, the key is making sure that your date, account, or subsidiary fields can be pulled onto each line of your saved search. This may not be possible with all records, but the script will be able to do the work of filtering all of the data so that you don't have to. You just need to identify the fields on the primary or related records you're searching for.
Validate Search Result
Once your saved search is linked on your NetClose account grouping record, you can validate your search by clicking the Validate Search Result button on the NetClose account grouping. This button triggers a script to read your saved search and pull in the balance for the current period. If the search has been linked correctly and field IDs are correct, the system will give you a value, as shown below.
Please note that this button does not validate whether your Subsidiary and Account filters are correct. It is primarily useful for checking the saved search criteria, your Search Column Label, and your Search Date Internal ID.
If one of your fields is incorrect, you'll instead see an error like the following:
In this case, the Search Date Internal ID is incorrect and needs to be looked at.
Generate Reconciliations
To check to make sure your reconciliations are generating as expected, generate reconciliations by navigating to NetClose > Close Management > Generate Reconciliations. Select the appropriate accounting period and account groupings and click Submit.
After the groupings have finished generating, go to NetClose > Reconciliations > Prepare Reconciliations and filter for the groupings you generated. You should see the "Explained Balance" is auto-filled by the saved search results. Click on a reconciliation to see the item. You may download the search results via the download icon on the item line.
If the saved search amount does not tie out to the GL Ending Balance, see our Troubleshooting article for tips.
Balance Changes
Please note that the saved search balance is synced to the reconciliation at the time the reconciliation is generated. In the event of a balance change, the user should re-submit the reconciliation on the Auto-Reconcile page to update the saved search balance.