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.
For troubleshooting, please refer to the Pigment - Netsuite FAQ to help you navigate the most common errors.
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
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
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
Posted transactions to make sure you are only retrieving actuals data
Records from a specific period of time and up to the last closed month
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
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 month. Although this is technically feasible, it isn’t the recommended option as it means that ending balances compute from the beginning of time when a new import is triggered.
- Loading historical balances for all closed years and cumulating the current and future years only in Pigment.
- Creating a Balance Sheet saved search and using NetSuite Formulas to perform an ending Balances calcs for a few selected months - If feasible. See below for a sample NetSuite query for this type of calculation where we configure the results to output the last two closed months ending balances.
Not sure which option is best for use case? Discuss with your Solution Architect or ask the community !!
Tips to customise results
The advice given so far in this article points to some common standards fields used. However, every NetSuite instance is different and customisable 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!