Creating AR/AP Searches in NetSuite

Although NetSuite has AR and AP Aging reports built-in, it can be helpful to create these reports in a search format that allows for more flexibility and specificity for filtering and grouping.

First, let’s look at the final result, which is a transaction based saved search filtered to show invoices for AR Aging report. You can filter for Vendor Bills for an AP Aging report.

(Click for larger image)

This AR Aging report is grouped by Project ID, and shows the Sum of Current, Sum of 30/60/90/+, as well as the total outstanding. Using a search, we can easily group by Project, Customer, Department or any other value that can be reached on the relevant records.

Next, let’s look at how we are getting these values behind the scenes in the search’s results section:

The grouping is currently set to Project ID (We use Project (Main Line) : ID to get to the Project on the header-level of the invoice). The brackets for the AR report are created using a simple CASE WHEN formula:

CASE WHEN TRUNC ({today})-{duedate} BETWEEN 0 AND 30 THEN {amountremaining} END

Let’s dissect the above.

The shell of a CASE WHEN formula is CASE WHEN Condition A THEN Output B ELSE Output C END.

Using CASE WHEN tells NetSuite that a condition has to be met before it can give us a specific output. Using the above format, if Condition A is met, then NetSuite is to provide Output B. If Condition A is not met, then NetSuite is to give us Output C; END simply tells NetSuite that this is the end of our formula.

Using the above structure, we query NetSuite for invoices where the difference between today and the due date is between 0 and 30 days. If it is, then give us the amount remaining on that invoice. Here we don’t use the ELSE logic as we don’t need an alternative output if the criteria is not met. If we had to, we could have used ELSE 0 to return $0 if the invoice does not meet the criteria.

Creating the buckets is as simple as using the above formula for 0-30, 31-60, 61-90 and 90+ days. The Total outstanding is pulled directly from a field on the Invoice record.

Finally, because we would like to group by Project, we have to select a Summary Type for each row we want visible on the report. Any row that does not have a Summary Type selected will only be visible if you drill-down into a specific project.

We hope this helps you create a much more useful AR/AP report. If you have any questions or comments, please contact us.