NetClose - Auto-Reconciliation Saved Search Setup
Summary
If you have information in NetSuite that you want to use as a subledger to reconcile your accounts in NetClose's Reconciliation module, you can use a Saved Search to get that information, and then pull it into reconciliations each period. This functionality works best with either a non-GL impacting subledger contained in NetSuite (this is best illustrated by the schedule lines in Netgain products, including the NetClose Amortization schedule lines), but can be made to work with GL impacting transactions.
Use Cases for This Feature
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, NetLoan, or NetLessor), then you have access to one of these subledgers. The same kind of subledger is used in NetClose's Amortization module; all of these can be utilized to create one of these saved-search based automatic reconciliations. This will only work if the subledger represents what the balance in a set of accounts should be; it will still be useful if there are known items outside of the subledger that aren't included in your saved search, but the account will not be able to be fully auto-reconciled by the search alone.
If the subledger from your saved search doesn't match the GL balance in your account, you'll then 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 extremely well 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. The accounts payable cycle could be a good example of this--perhaps your bills, bill credits, and bill payments all undergo a thorough review process inside of NetSuite, or are exclusively imported into the system from a separate system where they undergo such a review. It's redundant to re-reconcile these balances if there are flags on those AP-related records that have strong controls or reviews associated with them.
If you have a similar situation, you can create a saved search that looks for all bills, bill payments, and bill credits 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 (maybe someone booked a journal entry, or some other transaction that shouldn't have been booked to AP in this example), you can investigate the results of your saved search and compare that to your GL to find any differences (again, see our Prepare and Review Reconciliations article for details on how to investigate differences)
Setting Up the Saved Search
General Saved Search Information
We won't be covering the basics of how to use the saved search functionality in this article--it's standard NetSuite functionality and there are volumes and volumes written on the subject all over the internet. If needed, here are some useful articles from Oracle NetSuite's documentation 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
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 what your GL balance should be (at lease we hope it does most of the time). 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.
You may not see these fields unless you're in edit mode on your account grouping record--if the Subledger Within NetSuite; Agrees to Search checkbox isn't checked, the fields will be hidden on your record. Once checked, the fields are visible and editable. We'll go through each of them.
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.
Building Tips and Troubleshooting Tools
This is possibly the most important part of this article.
Unless you're using searches and account groupings that you've already set up before with slight (if any) variation, you're likely going to need to go through a lot of trial and error to get this working right. That's alright. This is extremely useful functionality with a lot of flexibility, and with that flexibility comes the need to be creative and resilient as you build your saved searches and configure your account groupings. We'll give you tools in this section to better build and troubleshoot your account groupings and saved searches.
Applying a Temporary Filter to Your Saved Search
The first step in building or troubleshooting is making sure your saved search is pulling in the right source material. If your source material is off, you're going to spend a lot of time wondering why you can't get your search to tie to your GL. The best way to do this is to apply the same kind of filter the script will be applying to your saved search.
If you go to your saved search, you can head to the "Available Filters" subtab to select a field that will be available to dynamically filter your saved search (as opposed to criteria, which are always applied to your search). Select the same field here that you plan on using as your filtered date field in the Search Date Internal ID field (you can use joined fields here as well, but that's beyond what we're covering here and NetSuite's documentation covers it well). Once you've done this, make sure the "Show in Filter Region" box next to the filtered field is selected, save the search, and take a look at the results.
You'll now see a filter selector that should look something like this. Use this to filter for some periods where you know what your subledger balance should be, and check what the search pulls in. You can even compare the total balance to your GL and see if it ties (if you're segregating by subsidiary or account, you can also add a subsidiary or account filter to make this easier to see what's happening on a more granular level).
Remember that if you're doing an ending balance reconciliation, only a TO filter will be applied for the last day of the accounting period you're reconciling, with no FROM filter. If you're doing an activity saved search, a TO and FROM filter will be applied to the first and last days of the accounting period you're reconciling. Apply your filters accordingly.
If your search looks the way you'd expect after filtering it, you can move forward with confidence.
The Validate Search Result Button
This button will be right at the top of your account grouping. If you click it, the same script that reads your saved search and grabs the balance will run for the current period, returning the current period amount for you to check against your saved search to make sure the balance you expect is being pulled in. You should use this to make sure your search is pulling in what you expect as a value for your saved search. It's useful for checking the saved search criteria, your Search Column Label field, and your Date Internal ID field.
Additionally, you can use this button to test and make sure your field inputs are working correctly. If everything is good, you'll see something like this:
If one of your fields is incorrect, you'll instead see an error like the following:
Which in this case indicates that your date filter is incorrect and needs to be looked at. If no errors are thrown but the wrong balance is being pulled in for the filtered period, check your saved search to make sure that it is pulling in the balance you'd expect. These scripts are only as good as the searches that underly them.
Again, this search will not validate whether your subsidiary and account filters are correct (if you're using them), but it's a good start for checking your saved search, your saved search column total, and your date internal ID field.
Generating Test Reconciliations
A final check to make sure your reconciliations are generating as expected between your saved search results, your account grouping field values, and your general ledger is just generating your reconciliations for a period (usually a period that is in the past, already closed, and you can easily check your values against) and making sure that everything populates as you expect.
If your accounts are mapped into your grouping, the saved search is set up and populated in the "Search" field on your account grouping, and you've filled in all the necessary fields for your summed column, date filter, account filter, and subsidiary filter as needed, you can head to NetClose > Close Management > Generate Reconciliations and select a period to generate your reconciliation for. Select a period to check against, select only the account grouping you're working on, and click the Submit button. You can refresh the page until the Generate Status goes to "Complete". Now click the Reconciliations link in the upper left corner of the generation page to get to your reconciliation list (or go to NetClose > Reconciliations > All Reconciliations) and filter for the period you generated your reconciliation in; you should see your reconciliation record, either summarized, segregated by account, or segregated by subsidiary according to your account grouping settings.
If you click the plus button on any of these records, the summary screen will pull up. There are several things you should check on this summary screen to make sure you've set up everything properly.
First, take a look at the GL Ending Balance (or the sum of the activity, if you're doing an activity-based reconciliation). Make sure it matches what your GL is showing for the period. If it isn't, there is likely a problem with foreign currency translation (reconciliations don't currently handle consolidation), and you'll need to break out your reconciliations by subsidiary to eliminate this difference.
Next, take a look at the Explained and Unexplained Balance fields. Notice that the reconciliation item created from your search is the source of this explained balance (unless you have carry-forward items as well). You can click on the "View" button to go to the reconciliation item record to take a look at what your saved search is actually pulling in based on the filters being applied by the reconciliation creation script. A csv version of the saved search will be saved in the Document field of the reconciliation item, and you can see exactly what's being pulled in by your saved search. If this file is pulling in something different from what you expect, your account, subsidiary, or date filters may not be using the right field. If the amount doesn't match the column you're expecting, you may have told the script to total the wrong column and should adjust that. And if you need more information in this file, you should add more results columns to your saved search to make this source documentation (essentially your subledger support) more robust for you to be able to use in the future.
Lastly, check across multiple periods. You're going to be running this search regularly, so you want to make sure that your search and filters work well across all periods, not just the one you've been testing. Keep your eyes on these reconciliations, and make adjustments as needed if you see that auto-reconciliations aren't happening. Before you throw up your hands if things don't work every time, ask yourself if there's something in your saved search you need to adjust to capture what you need to in your subledger population. You may be excluding records or transactions that you should be including.
We hope this guide has been helpful for you in learning how to set up saved searches to automatically reconcile some of your accounts in NetClose's reconciliation module. You likely won't be able to use this feature for all of your accounts, but there are many accounts that you can use this feature with, and it's flexible enough to be used in a variety of circumstances. We'll keep this guide up-to-date as new features are released that make this auto-reconciliation method even more flexible, useful, and powerful than it currently is.