Dynamically Source Field Values from Saved Search Results

08/02/2023 04:03 PM - By MavenCloud

One of the unsung heroes of Netsuite is part of the core SuiteBuilder module where you can use a saved search to dynamically source the value of a custom field.  This means you can use a saved search to calculate a value, then this will be dynamically displayed on the record in your custom field.  To do so first we need to build a saved search with the following criteria:

  • It must be a summary search with only one result column
  • It must have at least one standard criteria
  • It must have a filter
Let's build this together to calculate the total sales of each sales rep so users can see their total sales and possibly use this value in a Sales Commissions workflow down the road.  First our saved search will be a transaction saved search and the criteria should include only Posting transactions that effect revenue.  We have also added criteria to filter out Freight Revenue as that does not count toward our Sales Reps goals.  (NOTE - the cool thing here is the field that will display this data will be an Entity field on the Employee record even though this search is a Transaction Search.  This is an awesome feature that allows you to link data across record types as long as there is some field on the record type used in your saved search that links to the record type you want to display the data on.)


Next in the results we can add a few columns like Date, Document Number, Type, Sales Rep and Amount


Next we need to make this a summary search so we will add the Summary Type of SUM to the Amount column


Next, we need to add a filter to this search so we will add the Sales Rep field as a filter here.  (NOTE - You do not need to check the Show in Filter Region box for this to work, but you can if you want.  It does not hurt the functionality to have it checked)


Lastly, be sure to add the correct permissions under the Audience subtab or mark the search as Public if you would like.  Now when we save the search it should only show one value which would be the total of ALL sales, but the trick is the filter we added will be dynamically sourced the the record that we add the custom field to in the next steps.



The next step is to build your custom field that will sit on the Employee record.  To do so navigate to Customization > Lists, Records and Fields > Entity Fields > New



Enter a name and ID for your field then select the Currency type. (NOTE - you can also use Decimal Number as well)



Next we need to select the Employee record under the Applies To and UNCHECK the Store Value box.  This is key to getting this to work so make sure you uncheck this box so NetSuite can dynamically source the correct value every time the record is loaded.


The last step is we need to add the saved search we made under the Validation & Defaulting subtab


Once saved you can view the Employee record to see the value automatically populated.



While this is incredibly helpful when viewing records we run into a problem in that this field is NOT reportable.  Meaning it does not display a value when added to a report or saved search.  This is because we unchecked the Store Value box on the custom field configuration.  This is because the value cannot be dynamically sourced if it is stored, BUT if it is not stored then it cannot be reported on.  To overcome this "catch 22" we can leverage workflows.  Then nice thing about workflows is any non-stored field is still dynamically sourced during the workflow execution and then can be used as a condition or even used to set the value of another field.

To do this we need to first create a saved search that will trigger our workflow.  The criteria of this workflow would be any Employee that is a Sales Rep. (NOTE - The results and filters to do not matter in this case, but you can customize those as you feel comfortable.)


The make another Custom Entity Field that will be used to store the value being calculated by the saved search.  Be sure to keep the store value box checked.  (Make sure the field type matches the field type you created in your non-stored field).



Lastly, create a scheduled workflow based on the Employee record and use your saved search as the criteria.  Also setup the frequency you would prefer.  We will use Daily at 11:00pm, so every night the total sales will be calculated and stored.



In the first State add a Set Field Value and use the Entry trigger.  (NOTE - We do not need to use any extra Conditions, Contexts or Event Types as this is already controlled by the Saved Search and Schedule we setup in the previous steps)



In the parameters find your stored field and then use the From Field parameters to set the value from your non-stored field


Then save your workflow.  Now every night at 11:00pm the workflow will run on ALL employee records where Is Sales Rep = T.  The Employee Total Sales field will be calculated from the first saved search we built, then the calculated value will be stored in the Employee Total Sales (stored) field.  This way you can report on the stored field in other saved searches or reports.  You can also set the frequency of your workflow to be more often if you need more "real-time" information.  This same concept also works in Event Based workflows but requires a user to create or edit the record to trigger the workflow to store the new value.


One other thing to note is you can set the field display type of the non-stored field to "hidden" in the field configuration and that field will not be shown to users as they view employee records but still can be accessible to workflows as normal.



This little trick is an amazing leap forward in automating and optimizing data throughout your system, as you can now automatically calculate and store data between many different record types with your NetSuite environment.  Be sure to follow us and check back often for more great tips and tricks to help you maximize your NetSuite investment!

MavenCloud

Categories :
Tags :