Agenda

Saved Searches:

Advanced Features

• Filtering search results

• Highlighting specific results

• Using standard search functions

• Formulas (including the powerful CASE statement)

• Email schedules and alerts

• Dynamic, custom field from saved search

Filtering Search Results

• Let’s you filter within a saved search

• Set in the “Available Filters” tab

• Use to add search boxes to the top of saved searches

• You can use any field on the record to filter by, including linked record fields

• This does not have to be a visible field in Results

• Use the “Default Text Field Filter” to change the way the search box works

• “Contains” makes it so you don’t have to put a wildcard (%) at the

beginning of searches

• “Show as Multi-Select” lets you choose multiple options for fields that

are a list of values

Filtering Search Results

Highlighting Specific Results

• Calls attention to selected search results

• Can highlight:

• Rows

• Highlighting – basically a search inside a search

• Highlighting options:

• Images – beginning of line

• Text colors - row

• Background colors – highlights entire line

• Bold text - row

• Description – provides legend

• If more than one highlighting option used, it applies both but if

a conflict in type (e.g. background color) then first criteria has

higher priority

Highlighting Specific Results

• Conditions can include AND and formulas

• If exporting to Excel, highlights don’t come over

• If exporting as a PDF, then the highlighting stays

Highlighting Specific Results

Functions

•What are they

•Simplified SQL functions

•Used to transform data

•Executed dynamically when search is run

•Where to use them:

•Results tab only

•Can be used with

•Standard fields

•Join fields

•Formula fields

• % of Total

• Absolute Value

• Age in Days

• Age in Months

• Age in Weeks

• Age in Years

• Calendar Week

• Day - returns full date

• Month – returns month and year

• Negate

• Number as Time

• Quarter

• Rank

• Round

• Round to Hundredths

• Round to Tenths

• Week of Year

• Year

Functions

Formulas

•What are they

•Standard SQL functions based on Oracle 10g

•Mathematical operators

•Which SQL functions work

•Those listed in NS Help (see “SQL Expression”)

•Also see the available functions in the formula popup window

•Used to:

•Transform data

•Provide calculations

•Where to use them:

•Results tab

•Criteria tab

•Summary tab

•Highlighting tab

•Can be used with

•Standard fields

•Join fields

•Limitations:

•1000 character limit

• Formula (Currency)

• Formula (Date)

• Formula (Date/Time)

• Formula (Numeric)

• Formula (Percent)

• Formula (Text)

Formulas

To find out more:

•NS Help: “SQL Expressions”

•Google: “Oracle SQL functionname

•Go to SuiteAnswers (from support tab) for some self-paced training

•There are pre-recorded hour long webinars to help you learn

•Take 1 day class from NetSuite:

Formulas – CASE Statement

The syntax for the case statement is:

CASE [ expression ]

WHEN condition_1 THEN result_1

WHEN condition_2 THEN result_2

...

WHEN condition_n THEN result_n

ELSE result

END

•expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ...

condition_n)

•condition_1 to condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition

is found to be true, the case statement will return the result and not evaluate the conditions any further.

•result_1 to result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Notes:

•If no condition is found to be true, then the case statement will return the value in the ELSE clause.

•If the ELSE clause is omitted and no condition is found to be true, then the case statement will return NULL.

•You can have up to 255 comparisons in a case statement. Each WHEN ... THEN clause is considered 2 comparisons.

•NetSuite has a limit of 1000 characters in formula field

Formulas – CASE Statement

CASE

WHEN {item.type} = 'Inventory Item’ THEN {item.lastpurchaseprice}

WHEN {item.type} = 'Kit/Package' THEN {item.custitem161}

ELSE 0

END

This formula returns the last purchase price of an item, or the manually coded

cost (custitem161) of a kit/package. This is used on a transaction search.

Formulas – CASE Statement

CASE WHEN {amount} > 0 THEN (({amount} - (CASE WHEN {item.type} =

'Inventory Item' THEN {item.lastpurchaseprice} * {quantity} WHEN {item.type}

= 'Kit/Package' THEN {item.custitem161} * {quantity} ELSE 0 END) - (CASE

WHEN {partner} = 'Amazon' THEN {amount} *0.25 WHEN {partner} =

'Webstore' THEN {amount} *0.05 WHEN {partner} = 'eBay' THEN {amount}

*0.20 WHEN {partner} = 'eBay AU' THEN {amount} *0.19 WHEN {partner} =

'eBay UK' THEN {amount} *0.18 WHEN {partner} = 'Newegg' THEN {amount}

*0.20 WHEN {partner} = 'Buy.com' THEN {amount} *0.30 WHEN {partner} =

'FBA' THEN {amount} *0.27 ELSE 0 END) - (CASE WHEN {partner} = 'FBA'

THEN {amount} *.12 ELSE (CASE WHEN {item.custitem201} IS NOT NULL

THEN {item.custitem201} * {quantity} ELSE 0 END) END) + (CASE WHEN

{amount} < 15 THEN {custbody_ca_shipping_cost} ELSE 0 END))/{amount})

ELSE 0 END

This is a formula to return the Gross Profit of a sale based on the sales channel.

This has CASE statements embedded within other CASE statements.

(Sale amount – Cost – Site Fee – Shipping Cost) / Amount = GP%

Alerts

• Purpose, how it works

• Use a search to detect certain changes in the database, then send emails

to people who need to know about those particular changes.

• Examples:

• Alert a sales rep when a new customer has been assigned to her

• Alert a warehouse manager when an order exceeding a certain

quantity or amount has been approved

• Alert a customer when certain types of items are added to the

catalog

• When a new record is created that meets the search criteria, the email is

triggered

• Optionally, when a record that meets the criteria is changed

• Records that can be searched this way

• Help: Types of Saved Searches Available for Alerts

Email schedules and alerts

Alerts

• Steps

• Specify the saved search as usual

• Email tab

• Check the alert option

• Specify the recipients

• Specific recipients

• Recipients based on the results (such as the customer on a

sales order)

• Updated field – fields to be tracked for changes

• Customize message

• Can include fields, and formulas based on those fields

• Schedule (if selected in check box above)

Dynamic, custom field from saved search

Use saved searches to drive the value of a custom field

•Displays values derived from a summary saved search

•Summary search field displays a rollup value for a selected search

•Value is dynamically calculated each time form is displayed

•Limited to viewing on record only – can not export the data

Steps

1.Create a saved search with a summary type in the results of Count, Sum, Average,

Minimum, or Maximum.

2.Set the available filter to Internal ID

3.Create a custom field

4.Uncheck "store value" to display the field results on a form

5.In the Validation & Defaulting tab of the custom field settings add your saved search

6.To sort a search on a formula field, move the formula field to the top of the results list in

your saved search criteria

What we covered

• Filtering search results

• Highlighting specific results

• Using standard search functions

• Formulas (including the powerful CASE statement)

• Email schedules and alerts

• Dynamic, custom field from saved search