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.

FilterDescriptionFormula
Asset : IDis not empty
Alternate Methodis none
Asset : Include in Reportsis true
Inactiveis false
Asset : Inactiveis false
Subsidiaryis not none
Asset Typeis not none
Formula (Numeric)is 1CASE WHEN {custrecord_deprhistassettype}={custrecord_deprhistasset.custrecord_assettype} THEN 1 ELSE 0 END
Scheduleis false


4. On the Results tab, add the group columns first.

FieldSummary typeCustom label
Asset TypeGroupType
Asset : IDGroupID
Asset : NameGroupName
Asset : Asset StatusGroupStatus
Asset : Depreciation Start DateGroupIn Service
Asset : Asset LifetimeGroupAsset 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 labelFormula
Original CostMAX({custrecord_deprhistasset.custrecord_assetcost})
AcquisitionCASE WHEN {custrecord_deprhisttype.ID} = 1 THEN {custrecord_deprhistamount} WHEN {custrecord_deprhisttype.ID} = 8 THEN {custrecord_deprhistamount} * -1 ELSE 0 END
DepreciationCASE WHEN {custrecord_deprhisttype.ID} = 2 THEN {custrecord_deprhistamount} ELSE 0 END
RevaluationCASE WHEN {custrecord_deprhisttype.ID} = 5 THEN {custrecord_deprhistamount} ELSE 0 END
Lease ModificationCASE WHEN {custrecord_deprhisttype.ID} = 9 THEN {custrecord_deprhistamount} ELSE 0 END
Write DownCASE WHEN {custrecord_deprhisttype.ID} = 4 THEN {custrecord_deprhistamount} ELSE 0 END
SaleCASE WHEN ({custrecord_deprhistasset.custrecord_assetdisposaltype.ID} = 1 and {custrecord_deprhisttype.ID} = 3) or {custrecord_deprhisttype.ID} = 6 THEN {custrecord_deprhistamount} ELSE 0 END
DisposalCASE WHEN ({custrecord_deprhistasset.custrecord_assetdisposaltype.ID} = 2 and {custrecord_deprhisttype.ID} = 3) or {custrecord_deprhisttype.ID} = 7 THEN {custrecord_deprhistamount} ELSE 0 END
NBVCASE 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:

FieldValue
Search Column LabelNBV
Search Date Field Internal IDcustrecord_deprhistdate
Search Subsidiary Field Internal IDIf segregating your account grouping by subsidiary. Example: custrecord_deprhistsubsidiary
Search Account Field Internal IDLeave blank. NBV combines the asset and accumulated depreciation accounts, so it cannot be filtered to a single account.
Search Segment Field Internal IDIf segregating your account grouping by segment
Search Accounting Book Field IDIf segregating your account grouping by accounting book

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.

FieldFixed asset groupingAccumulated depreciation grouping
Search Column LabelAcquisitionDepreciation
Search Date Field Internal IDcustrecord_deprhistdatecustrecord_deprhistdate
Search Account Field Internal IDcustrecord_deprhistassettype.custrecord_assettypeassetacccustrecord_deprhistassettype.custrecord_assettypedepracc


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.

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


Was this article helpful?