Netsuite Series - Create Transaction Saved Searches

  • 15 February 2022
  • 0 replies
  • 230 views

Userlevel 1
Badge

 

 

The Pigment native connector for NetSuite supports the direct import of business through a specific report type known as a Saved Search object. If you’re not familiar with creating this type of report to generate Financial data, we got the basics covered here! This article is a step-by-step guide to generating Transactional data using saved searches, along with some tips and tricks.

 

 


Querying a Search in seconds from Pigment

 

A Saved Search is a table that queries information in your NetSuite Instance based on specific criteria and displays results that can be customised with relevant columns. To be able to create or update a transaction list in Pigment, you must first configure the one time connection

 

Saved Search Results In NetSuite 

 

Our Native connector then consumes the resulting tables users create and automate mappings to any transaction list in Pigment. Here is how a Saved Search

 

Sample output of a Financial Saved Search

 

Creating or Retrieving a Saved Search Report 

 

 

Before you you dive in, one common mistake people make when using the Native Connector is trying to use a NetSuite Saved Report Instead of a Saved Search. Here is the difference:

 

📂Saved Searches: Real-time data tables that can be retrieved in NetSuite and pushed to other systems via APIs using the search Unique ID. NetSuite gives Functional users the ability to specify criteria and display results in an easy to use interface. 

 

📈Saved reports: Designed to be used within NetSuite primarily for visualisation. They are saved formatted results, visually appalling for the end user but not easy to transform in an acceptable format for API consumption. 

 

Tips to create or retrieve a Search:

When defining your criteria for the data you wish to extract, you have the option to either create a new Search, or retrieve an existing one.   Both options can be found under the Reports tab in the main menu bar.

Option 1: Reports > New Search to create a brand new list
Option 2: Reports > Saved Searches > All Saved Searches to retrieve an existing search 

 

Create or retrieve a Search through the Home Page

 

 

The next step would be to select the type of record you would need. This can be anything within the modules set up in your NetSuite instance. For GL Financial data, select Transaction:

 

 

 Click on ‘Create Saved Search’:

 

 

For each saved search you will need to :

  •  Filters using the ‘Criteria’ tab
  •  Desired columns using the ‘Results’ tab
  •  Retrieve the ID of the search

 

 

In the following sections, we will illustrate an example using the most common use case for creating a Saved Search to integrate Balance Sheet and Income Statement data

 

Criteria Requirements for Financial Data 

 

NetSuite's filtering capabilities can be used to narrow down the list of records to desired outputs. For Financial transactions in Pigment, the most common use case is to use the following filtering criteria 

 

        :heavy_check_mark: Posted transactions to make sure you are only retrieving actuals data

        :heavy_check_mark: Records from a specific period of time and up to the last closed month

        :heavy_check_mark: Non Blank values to optimize performance

 

Income statement Criteria

To select Income Statement actual records for the current fiscal year, the filters used would be:

  • Account Type :  Select all Income Statement account (See screenshot below)
  • Accounting Period: End date
  • Posting (Yes) 
  • Amount is not equal to 0

 

 

 

You can also use  Formula Expressions in Search Criteria to perform some advanced filtering!

 

Balance sheet Criteria

 

Similarly to Income statement, to create a Balance Sheet Saved Search, the filter requirements would need to specify both account type to filter on Balance Sheet accounts and period load of transactions data.

 

 

Should I use one Saved Search for all Financial data or separate P&L from Balance sheet?

 

Typically, the best practice is to use two separate Searches. Here is why 

 

  • Set a different upload schedule in Pigment for P&L transactions Refresh  versus Balance Sheet based on how it’s used in the business.

 

  • Configure a different set of results such as different columns, drill down requirements or grouping logic in the result tabs  

 

 

 

 

 

Results Requirements for Financial Data 

 

In the results Sub-tab, you will notice defaults fields for the Transaction list type. You can customise your results in many ways. Here are the common customisations for Pigment Integrations

  • Remove Irrelevant fields that you do not need by clicking on the field and then Remove.
  • Add fields at the bottom of the menu bar by selecting them from the list. 
  • Move the fields to follow the order you want them placed by clicking on Move Up or Move Down.
  • You can add aggregation methods to pull different levels of granularity

 

Sample Results for NetSuite Financial Data

 

 

Considerations for Income Statement and Balance Sheet Data

 

 

Income Statement Monthly Balances

 

A simple transaction list would produce all transaction records for the periods selected. Once in Pigment, data can be aggregated to output Monthly activity. 

 

Balance Sheet Ending Balances

 

Balance Sheet results do not cumulate automatically in a Saved search at the transactional level. as a result, an additional step needs to be performed to output Ending Balances! 

 

There are many ways of dealing with this constraint depending on the overall architecture of your Pigment Application. Here are a few ideas: 

 

  • Loading transactions for all historical data  in Pigment and using the Cumulate  or Show value As functionality to calculate Ending Balances for each year

 

  • Creating a Balance Sheet saved search and using NetSuite Formulas to perform an ending Balances Calcs for a few selected Months - If feasible. 

 

  • Loading historical balances for all closed years and cumulating the current and future years only in Pigment

 

Not sure which option is best for use case? Discuss with your Solution Architect or ask the community !!

 

Tips to customize results
 

The advice given so far in this article points to some common standards fields used. However, every NetSuite instance is different and customizable so If you are not finding a field you are looking for, you can try using the tips below before contacting your NetSuite Administrator for more support

 

 

Can’t find fields you are looking for? If you scroll down to the bottom of available results, you can use the ‘Item fields’ options to provide more available properties to use 

 

 

 

Save your Saved Search and It’s ready to use in Pigment!


0 replies

Be the first to reply!

Reply