NetClose - Saved Searches to Reconcile FAM
Overview
This article explains how to use a NetSuite FAM (Fixed Asset Module) saved search as a subledger source for auto-reconciling your fixed asset accounts in NetClose. These steps are based on the standard NetSuite FAM setup.
Prerequisites
- FAM must be installed, configured, and actively used.
- Identify GL accounts for Fixed Asset and Accumulated Depreciation.
- Confirm whether the mid-life Depreciation History was imported into FAM.
Creating the Saved Search
Both options below use the same saved search. Build it once, then choose how to set up your account groupings.
Step 1 - Build the saved search
1. Navigate to: Reports > Saved Searches > New and select FAM Depreciation History.
2. Check the Public box. If the search is not public, it will not be available to select on the account grouping.
3. On the Criteria tab, add the following filters. Paste the formula into the formula field of the Formula (Numeric) filter.
| Filter | Description | Formula |
| Asset : ID | is not empty | |
| Alternate Method | is none | |
| Asset : Include in Reports | is true | |
| Inactive | is false | |
| Asset : Inactive | is false | |
| Subsidiary | is not none | |
| Asset Type | is not none | |
| Formula (Numeric) | is 1 | CASE WHEN {custrecord_deprhistassettype}={custrecord_deprhistasset.custrecord_assettype} THEN 1 ELSE 0 END |
| Schedule | is false |
4. On the Results tab, add the group columns first.
| Field | Summary type | Custom label |
| Asset Type | Group | Type |
| Asset : ID | Group | ID |
| Asset : Name | Group | Name |
| Asset : Asset Status | Group | Status |
| Asset : Depreciation Start Date | Group | In Service |
| Asset : Asset Lifetime | Group | Asset Lifetime |
5. Then add one Formula (Currency) column for each formula below. Set the summary type to Sum and enter the custom label shown. Only the column you pick as the Search Column Label is required for reconciling. The other columns give you an activity breakdown for review.
| Custom label | Formula |
| Original Cost | MAX({custrecord_deprhistasset.custrecord_assetcost}) |
| Acquisition | CASE WHEN {custrecord_deprhisttype.ID} = 1 THEN {custrecord_deprhistamount} WHEN {custrecord_deprhisttype.ID} = 8 THEN {custrecord_deprhistamount} * -1 ELSE 0 END |
| Depreciation | CASE WHEN {custrecord_deprhisttype.ID} = 2 THEN {custrecord_deprhistamount} ELSE 0 END |
| Revaluation | CASE WHEN {custrecord_deprhisttype.ID} = 5 THEN {custrecord_deprhistamount} ELSE 0 END |
| Lease Modification | CASE WHEN {custrecord_deprhisttype.ID} = 9 THEN {custrecord_deprhistamount} ELSE 0 END |
| Write Down | CASE WHEN {custrecord_deprhisttype.ID} = 4 THEN {custrecord_deprhistamount} ELSE 0 END |
| Sale | CASE WHEN ({custrecord_deprhistasset.custrecord_assetdisposaltype.ID} = 1 and {custrecord_deprhisttype.ID} = 3) or {custrecord_deprhisttype.ID} = 6 THEN {custrecord_deprhistamount} ELSE 0 END |
| Disposal | CASE WHEN ({custrecord_deprhistasset.custrecord_assetdisposaltype.ID} = 2 and {custrecord_deprhisttype.ID} = 3) or {custrecord_deprhisttype.ID} = 7 THEN {custrecord_deprhistamount} ELSE 0 END |
| NBV | CASE WHEN {custrecord_deprhisttype.ID} = 1 THEN {custrecord_deprhistamount} ELSE {custrecord_deprhistamount}*-1 END |
6. Save the search. Preview it to make sure all your assets are pulling into the search correctly.
Option 1 - Reconcile net book value in one grouping
Use this option if you reconcile fixed assets and accumulated depreciation together, and you do not segregate your account grouping by account.
1. Create a NetClose account grouping if you have not already by going to NetClose > Setup > Reconciliation Groupings & Rules. Make sure the mapped accounts include all fixed asset accounts and all accumulated depreciation accounts from the GL that are used in FAM.
2. On the Account Grouping record under the Reconciliation Rules tab, check the box that says "Subledger within NetSuite; Agrees to Search". This will allow you to fill in the other fields.
3. In the Search field, select your saved search. Fill in the other fields as follows:
| Field | Value |
| Search Column Label | NBV |
| Search Date Field Internal ID | custrecord_deprhistdate |
| Search Subsidiary Field Internal ID | If segregating your account grouping by subsidiary. Example: custrecord_deprhistsubsidiary |
| Search Account Field Internal ID | Leave blank. NBV combines the asset and accumulated depreciation accounts, so it cannot be filtered to a single account. |
| Search Segment Field Internal ID | If segregating your account grouping by segment |
| Search Accounting Book Field ID | If segregating your account grouping by accounting book |
Reconciliation Method
If FAM mid-life asset data was imported, select the Reconciliation Method to be Ending Balance; if not, select Activity. This field is called "Method" on the Account Grouping record.
Option 2 - Reconcile cost and accumulated depreciation separately by account
Use this option if you segregate your account groupings by account. NetClose filters the saved search to one GL account per grouping, so you need one grouping for your fixed asset accounts and a second for your accumulated depreciation accounts. Both groupings use the same saved search from Step 1.
1. Create two NetClose account groupings by going to NetClose > Setup > Reconciliation Groupings & Rules: one mapped to your fixed asset accounts and one mapped to your accumulated depreciation accounts.
2. On each Account Grouping record under the Reconciliation Rules tab, check "Subledger within NetSuite; Agrees to Search", then select your saved search.
3. Fill in the search fields on each grouping as follows. The Search Account Field Internal ID is the field that points to the GL account on the asset type.
| Field | Fixed asset grouping | Accumulated depreciation grouping |
| Search Column Label | Acquisition | Depreciation |
| Search Date Field Internal ID | custrecord_deprhistdate | custrecord_deprhistdate |
| Search Account Field Internal ID | custrecord_deprhistassettype.custrecord_assettypeassetacc | custrecord_deprhistassettype.custrecord_assettypedepracc |
Reconciliation Method
If FAM mid-life asset data was imported, select the Reconciliation Method to be Ending Balance on both groupings; if not, select Activity.
Testing the Saved Search
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
Next, generate the account grouping(s) tied to the saved search by going to NetClose > Close Management > Generate Reconciliations. Select the appropriate accounting period and account grouping(s) and click Submit.
After the groupings have finished generating, go to NetClose > Reconciliations > Prepare Reconciliations and filter for the grouping 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
The saved search balance is synced 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.
