Skip to main content

Filters

The Filters area controls how records are selected when you download into a sheet. There are two kinds of filter.

KindWhere it is used
Optional filtersBecome the Filter By dropdown on the sheet. The person using the sheet picks one and enters a value each time they download.
Default filterA condition built into the template that every download must meet, on top of whatever is picked in Filter By. The person using the sheet cannot change or remove it. Including a default filter is optional.

Note: Filters use body fields, the fields that filtering through the REST API supports.

Optional Filters

A new template starts with recommended optional filters already listed for its record type, such as tranDate, tranId, id, entity, postingPeriod, and lastModifiedDate, with tranDate preselected.

  1. Remove any filter you do not want with its trash icon. To add another, select Add an optional filter and pick a field; search to find it.
  2. In Filter By, choose which optional filter new sheets open with, or select None (no pre-selected filter). It can be changed on the sheet at any time: the sheet's Filter By dropdown lists every optional filter on the template.
  3. Select Test filters. NetSuite's REST API does not accept every field as a filter on every record type; for example, it rejects subsidiary on invoices. The test checks each field with NetSuite and marks it Works or Failed. Remove any filter that fails with its trash icon.

Optional filters with tested fields and tranDate preselected

Test filters result with subsidiary marked Failed

Remarks

  • Every optional filter must pass its test before the template can be saved. If you skip Test filters, the editor runs the same test when you leave the Filters area and again when you save. Filters that fail are removed when you move on to Dropdowns or save, and the editor asks you to save again.
  • Each optional filter can have its own dropdown, set on the Dropdowns step of template creation. On a new sheet, the Start cell offers the list of valid values, such as customer names, for the filter it opens with. If you switch Filter By to another filter, the list does not change with it; select the Start cell and apply that filter's list from the ribbon's Dropdowns menu: under Bulk Records for a list fetched from NetSuite or typed by hand, or at its own entry for a Finsyte list such as Customer.

Default Filter

A default filter brings in a subset of records based on conditions you choose, such as a date range or invoices in one currency, and applies to every download alongside the Filter By choice on the sheet. It is optional when you build a template. Each sheet keeps the default filter it was created with, so to change it, edit the template in the Template Portal and create a new sheet.

  1. Select + Add default filter. The editor adds an empty condition for you to fill in. Removing the last condition removes the default filter.
  2. Choose whether the group matches ALL of (AND) or ANY of (OR) its conditions.
  3. In the condition, pick a body field, an operator, and a value. Select + condition to add another. Operators depend on the field type and include options such as is, is not, contains, starts with, is empty, equals, greater than, between, any of, on, before, and on or after.
    • For fields that reference another record, select Choose… to pick records from a list. Picked records appear as chips.
    • Choosing values for an any of condition
    • Date values use your NetSuite date format.
  4. Select + AND / OR group to nest another group when a condition needs both kinds of logic.
  5. Select Test filter. The result line reads ✓ Passed - N matching records, 0 matching records., or ✗ followed by NetSuite's message.

Default filter with a passed test

Remarks

  • The default filter must pass its test before the template can be saved.
  • A test that finds 0 matching records. still passes, so the template can be saved, and Review and Save warns The default filter currently returns 0 results. Downloads from its sheets return nothing until records in NetSuite match the filter. If the conditions are wrong instead, change them, save the template, and create a new sheet.
  • Fields that pass as optional filters work in the default filter too. Not every body field can be filtered on. For example, on invoices subsidiary fails with The field 'subsidiary' is not available for filtering on this record type.
  • To match double quote characters in a value, use the is or is not operator. For every other operator, such as contains, starts with, and ranges, NetSuite has no way to escape a quote, so quotes are removed from the value before searching. The editor shows the value it will search with.
  • A default filter applies to every download from the sheet.
  • The sheet shows a summary of the default filter beside its Filter By cell so the person downloading knows it is in effect.
  • If a template has no optional filters, the sheet's Filter By list offers only None, and downloads use the default filter alone, or return every record of the type if there is no default filter either.

Example

A template for invoices billed in US dollars might use the default filter currency any of US Dollar, with optional filters for Date and Customer. Every download is limited to US dollar invoices, and the person downloading narrows further by date range or customer.

Next step: Dropdowns