Extend NetSuite’s Additional Filters on Batch Record Generators

This article is relevant if you are seeking to extend NetSuite’s bulk/batch record generator. In particular, this article solves a specific challenge with NetSuite’s “Invoice Billable Customers” or “Invoice Customers” routine but is an applicable pattern on NetSuite’s bulk generation tools.

Background

This is a challenge not for a client but for the work we do at our NetSuite Systems Integration firm. However, this kind of challenge comes up often for NetSuite customers that are trying to use NetSuite provided tools working in batch mode. The reality is that the NetSuite provided batch systems are not exposed at the platform API level. So you have to work with the user-interface customizations with the intent to get the job done.  Generally, you have to get creative because you can’t just “throw a script at it”.

Customize Sublist Additional Filter Challenge

Here is the challenge that my firm faced:

1. Weekly Billing: We bill all of our customers weekly for our previous week’s timesheet driven work. Some customers seek to be on a different billing cycle so as to not overwhelm their accounts payable staff.
2. Batch Invoicing: We seek to use NetSuite’s built-in Invoice Billable Customers feature because it exists; like any business person, we want to use the built-in software where we can.
3. Project Billing: We use NetSuite’s Services Resouces Planning (SRP aka Advanced Projects). Thus, the billable element is on the project, not the customer record. So it presents information a bit distant from the customer record.
4. Filter on Billing Cycle Preference: We need to get the built-in filter mechanism to simply show all customers/projects that are ready to bill. A simply “Yes/No” flag is desired. Meaning, show me all customers that are ready to bill. Don’t show me the ones that are not due to a billing cycle preference.

While the solution can be achieved using our Custom Bulk Generator pattern as discussed in my 2020 article, See How to Craft Fast and Flexible NetSuite Bulk Record Generators, the built-in generator works very well except that we can’t easily get a filter that reaches the data we care about.  So close — yet so far.

The Solution to Extend NetSuite’s Additional Filter Reach

The solution demands a number of innovations to get NetSuite to reach the data we care about. Here is what we produced:

Custom Entity Field: Billing Cycle in Weeks [Integer]

Create a custom entity field for the customer of type integer. Defaulting to each week, supply the integer value of 1. If you want every month, use the value of 4 to approximate a month. If you want every other week, enter the value of 2.

Custom Entity Field: Billing Cycle Due [Yes/No]

Create a custom entity field for the customer of type checkbox. This is a calculated AND stored value field that will hold Yes/No (really, it’s T/F in the database).  The key now is to determine how to get this field to be updated automatically any time a customer record is edited/saved.

Customer Record Update for Storing a Calculated Field

Subject to another article, we supply our clients a license-free utility that makes it very easy to have saved-search driven calculated information stored in a custom field when the target record is edit/saved (we call that a record “touch”). This capacity is in contrast to NetSuite’s “Creating Custom Fields with Values Derived from Summary Search Results” which will NOT store the value for you in saved searches.

Saved Search which returns T/F (Yes/No) Value

Create a custom search that will evaluate when the last sale took place and compare that to the Billing Cycle in Weeks custom field to see if the value of the Billing Cycle Due custom field should be Yes or No [T/F].  Think of this as very similar to NetSuite’s approach which requires you to define a Summary Search to dynamically calculate a value: except, in our case, we WILL store this value in the database.

Here is the text formula in the saved search that leverages NetSuite’s {lastsaledate} field to easily know the date when the last invoice was generated:

CASE 
	WHEN (NVL({lastsaledate}, SYSDATE) + NVL({custentity_pri_billing_cycle_weeks},1) * 7 - SYSDATE) > 0 
	THEN 'F' 
	ELSE 'T' 
END 	

Scheduled Edit/Save on Customer Record

Since the customer record calculates the correct value when it is edit/saved (“touched”), we need a routine that will regularly touch all candidate customer records. We need this because the Billing Cycle Due’s True/False value changes day-by-day depending on the frequency of billing and time passing.  Hence, touch customer records nightly to get the new value.  This can be done with a Scheduled Workflow. In our license-free utilities library, we have a “Touch” tool that makes it easy to bulk edit/save records by supplying a target saved search.

Reach through to Expose the Customer Billing Cycle Due Field

Since the built-in NetSuite Invoice Billable Customers feature effectively sees transactions that are project-based, it can’t see the related customer information easily.  It’s a quirk of the Additional Filter subsystem of the Invoice Billable Customers tool.  Thus, the trick is to create a custom field that sources the custom Billing Cycle Due field from the Customer record using NetSuite’s native Sourcing feature. Meaning, the angle of  database attack is to expose another Yes/No field that looks up the “Company : Billing Cycle Due” field from the perspective of the transaction.

See the related image for how the field is now exposed on the filter section.

Push the NetSuite Platform to Meet Business Requirements

The NetSuite Platform is a great environment to innovate. Yet, there are times that the provided tools require workarounds and indirect methods to achieve the desired objective. Hence, the next time you think the built-in batch tools “almost” work, think again. You may need to go on a database journey to get there.

If this article was interesting to you, feel free to receive notifications as I post new ones. If you have a built-in NetSuite program challenge, let’s have a conversation.

Be Sociable, Share!

• 
• 

Marty Zigman

Holding all three official certifications, Marty is regarded as the top NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 30 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to set up a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - YouTube