One of the most satisfying things for a NetSuite admin is to provide solutions that keep the users engaged and interested in the system without having to use scripts.
One way of doing that is making the environment dynamic rather than a series of static forms.
In this context, by dynamic, I mean the ability to make the interface change with the data in the system. A simple way to do that is to populate a field with a saved search result.
Reasons To Populate A Field With A Saved Search in NetSuite
There are so many use cases for this particular development but let’s take an example of analyzing Invoice data posted against a Sales Order. The following are some useful metrics we could have pulling in to a custom field on the Sales Order form.
- The most recent invoice date – Even if there are multiple invoices posted against a sales order we can pull in the most recent invoice date and populate it in a custom field.
- The total invoiced value – Similar to the above but populate with the total value of invoices posted against the Sales Order
- The total invoiced value for the customer – We could even fill a field to show the total invoiced value for that customer to date.
- Average days to pay – A slightly more advanced metric than the three above. We could analyze all the invoices against a given Sales Order and display the average number of days to pay.
There are endless possibilities but this gives you an idea of what kind of metrics are possible.
The only thing you need to remember is that the custom field can only provide one value. If you want more than one value, you will need more than one field to capture it or you can CONCATENATE your results.
How To Populate A Field With A Saved Search In NetSuite
The process to create a custom field containing a saved search value involves two important steps
- Creating an eligible saved search
- Creating a custom field
You may already know how to do both of these things, but to produce a working result there are a few things you need to remember.
Creating A Saved Search To Feed In To A Custom Field
To explain this process I will run through a working example.
We are going to be looking at Invoice data so for this example we will need a Transaction search. Navigate to Reports > New Search and then select Transaction.
Click the Create Saved Search button and give your search a name. Don’t forget to also make your search public so other uses will be able to view the results.
Navigate to the Criteria subtab. We will set the criteria to show invoices and the main line only.
Now we have set the criteria for our search we need to define the results.
This is the most important step for this kind of search as the results are what will be feeding in to our custom field.
We will use the first of my four examples and show the most recent invoice date. For this we will set one result.
Navigate to the Results subtab. The field to select will be Date and we will use the summary type Maximum. This shows only the highest value for the Date column, which will be the most recent.
The last part of configuring the search is the Available Filters tab.
We don’t want the system to show the most recent date of all invoices in the system. We need it to filter the search results for only the invoices created from the Sales Order being viewed.
We do that by adding a filter on the Created From field of the Invoice. More specifically, the unique identifier – the Internal ID.
We now have a saved search, the next step is to create a custom field for the data to feed in to.
Creating A Custom Field For A Saved Search To Populate
We will continue with the example of my Sales Order field displaying most recent invoice date.
Navigate to Customization > Lists, Records & Fields > Transaction Body Fields > New. Give the field a name and select the field type. In this example we will use a Date field type.
You can find out about the full range of NetSuite custom field types here.
A key part of the field’s configuration is unchecking the Store Value checkbox. We do not want the field to store any value. Rather we want the field to dynamically populate every time it is viewed.
Under the Applies To subtab, select the record type you want the field to display on – in this case we will select Sale.
Now the only thing to do is link the saved search to your new field. Navigate to the Validation and Defaulting tab and you will find a field called Search. Select your search from the list and save the field record.
The setup is now complete.
I can navigate to any Sales Order that has invoices posted against it and it will show me the most recent invoice date.
This is live information, since it is running off a saved search, so a user knows that every time they view the record it will give them the most recent date.
As you can imagine, this feature can be used in so many different scenarios. It allows you to present key information to users during their daily activities rather than having to open a search or report.
|If there is a specific use case for this feature that you are struggling to make a reality, please feel free to get in touch via the contact form and we may be able to provide assistance.
Important Things To Remember When Trying To Populate A Field With A Saved Search Result
Remember the following important tips for setting this type of field up effectively –
- Search results must be a single field and a summary type.
- Remember to make your search public
- Your Available Filter will be the unique identifier specific to the record your field is being viewed from.
- Ensure Store Value is not checked on your custom field
If you are looking at ways of creating more engaging forms you will also want to check out our guide to using Inline HTML fields.