NetSuite Formulas

NetSuite Formulas



Please feel free to "give back" and share some. Send your formula examples to dhynek@business-

.

Contributing Organizations:

MasterGraphics Apogee WiscNet

DIY Home Center Forte Research Cielo

Table of Contents

Case When Formulas .................................................................................................................................... 4 Multiple Case When statements in a single formula................................................................................ 4 Using Case When to locate text ................................................................................................................ 4 Using date in Case When .......................................................................................................................... 5 Case When with more than one select..................................................................................................... 5 Multiple Case When statements with Wildcards ..................................................................................... 5 Case When statement to look up Multiple Zip Codes .............................................................................. 5 Using Case When to select all date for the last 7 days (Time for last week by employee) ...................... 5 Using Multiple "Not" in a Case When statement ..................................................................................... 5 Multiple Case When satements Grouped................................................................................................. 5 To display the milestone status with corresponding colors (red, yellow, green)..................................... 5 Using Case When to determine when an item was sold for free ............................................................. 6 Mass Update for Last Sales Face to Face .................................................................................................. 6

Decode .......................................................................................................................................................... 6 Simple Case When with Decode ............................................................................................................... 6 Decode with Multiple Case When ............................................................................................................ 6 Look at date and determine the month ................................................................................................... 6

Dealing with Null Data .................................................................................................................................. 7 NVL............................................................................................................................................................ 7 NVL2.......................................................................................................................................................... 7 Use NVL2 to provide link when a field is not null ..................................................................................... 7 Nullif ......................................................................................................................................................... 7

Using SUM and Nullif to Divide 2 formulas... ............................................................................................ 7 Managing Text .............................................................................................................................................. 7

Remove characters from text ................................................................................................................... 7 Display only portion of text....................................................................................................................... 8 Concat ....................................................................................................................................................... 8 Concatenate two fields ............................................................................................................................. 8 Using CONCAT Statement ......................................................................................................................... 8 Using CONCAT Statement in a Mass Update Fields Formula.................................................................... 8 RPad/LPad ................................................................................................................................................ 8 SUBSTR...................................................................................................................................................... 8 Clean up Phone Numbers using "Replace" ............................................................................................... 9 Formatting .................................................................................................................................................... 9 Using check Box in a Formula and Inline HTML ........................................................................................ 9 Controlling Column and Row data in Searches ....................................................................................... 9 HTML Text Tags ......................................................................................................................................... 9 NetSuite Custom Label coding .................................................................................................................. 9

Formula Example: ............................................................................................................................... 10 Column Label Example:....................................................................................................................... 10 Quick Links .................................................................................................................................................. 11 Using quick link pop-up for inline editing ............................................................................................... 11 Customer Quick View Link (Text Formula).............................................................................................. 11 Log a Call with loaded customer and contact information..................................................................... 11 Log a Call with loaded customer, contact, and support contract information....................................... 11 View Contracts ........................................................................................................................................ 12 Send Custom Email ................................................................................................................................. 12 Create a Deal........................................................................................................................................... 12 View Activities......................................................................................................................................... 12 Edit Customer ......................................................................................................................................... 12 Selecting an Email Template and assign Contact from a Link (Text formula)......................................... 12 Sending an Email from a transaction search (Text formula)................................................................... 12 Calculation Functions.................................................................................................................................. 13 Math ....................................................................................................................................................... 13 Show quantity within a range ................................................................................................................. 13

Calculate Gross Profit.............................................................................................................................. 13 Calculate Days to a Date ......................................................................................................................... 13 Calculate Days between Dates using System Notes ............................................................................... 13 Total sales ............................................................................................................................................... 13 Total Cost ................................................................................................................................................ 13 Gross Profit Dollars ................................................................................................................................. 14 Getting year to date (YTD) revenue in transaction search ..................................................................... 14 Calculate Subtotal ................................................................................................................................... 14 Date......................................................................................................................................................... 14 Used to calculate a date 60 days in the future ....................................................................................... 14 Show the day of the week ...................................................................................................................... 14 Using MAX with Date range to select Month range ............................................................................... 15 Using TO_CHAR to find last month in a date field .................................................................................. 15 Using the TO_CHAR to select data for a specific day of the week.......................................................... 15 Search to compare an Expiration date to today's date .......................................................................... 15 Scripts ..................................................................................................................................................... 15 Sample Script (Work Flow Action Script) ................................................................................................ 15 Additional Script Example ....................................................................................................................... 16 Miscellaneous ............................................................................................................................................. 17 Using a MAX Command inside a formula (used in actual VRS Forecast)................................................ 17 Who Last Modified.................................................................................................................................. 17 Search for a work for sequence in a field using REGEXP_INSTR............................................................. 17 Not Equal To............................................................................................................................................ 17 Finding First not Null field from list of fields using Coalesce .................................................................. 17 Using an URL to Enter Data on an Online Form...................................................................................... 17 Using a Saved Search to fill as a default value in field ............................................................................ 18 Using MAX and SUM in the same formula.............................................................................................. 18 Wildcards ................................................................................................................................................ 18

Case When Formulas

Multiple Case When statements in a single formula

CASE {custitemcommission_parent}when 'Autodesk Software Vertical' THEN 1 WHEN 'Autodesk Software Vertical Subscription New' THEN 2 WHEN 'Autodesk Software Vertical Subscription Renewal' THEN 3 WHEN 'Autodesk Software Horizontal' then 4 WHEN 'Autodesk Software Horizontal Subscription New' then 5 WHEN 'Autodesk Software Horizontal Subscription Renewal' THEN 6 WHEN 'Hardware Equipment ' THEN 7 WHEN 'Refurb Equipment' THEN 8 WHEN 'Hardware Maintenance Program' then 9 when 'Software -Non Autodesk' then 11 when 'Software - Kubotek' then 12 when 'Imaging Services - All Formats' then 13 when 'InkJets' then 14 When 'Consulting' then 15 when 'Training' then 16 when 'Software Support' then 17 when 'Drafting Supplies' then 18 When 'S&R Parts & Labor' then 10 ELSE 0 END

case {item.custitemcommission_parent} when 'Autodesk Software Vertical' then {salesrep.custentityplan_ad_sw_vert_pro_q1} when'Autodesk Software Vertical Subscription New' then {salesrep.custentityplan_ad_sw_vert_sub_q1} when'Autodesk Software Vertical Subscription Renewal' then {salesrep.custentityplan_ad_sw_vert_sub_rn_q1} when'Training' then {salesrep.custentityplan_sw_training_q1} when'Consulting' then {salesrep.custentityplan_sw_serv_con_q1} when'Software Support' then {salesrep.custentityplan_sw_support_q1} when'Imaging Services - All Formats' then {salesrep.custentityplan_img_srv_all_for_q1} when'Hardware Maintenance Program' then {salesrep.custentityplan_hw_maint_q1} when'Hardware Equipment ' then {salesrep.custentityplan_hw_equ_q1} when'Autodesk Software Horizontal' then {salesrep.custentityplan_ad_sw_hor_pro_q1} when'Autodesk Software Horizontal Subscription Renewal' then {salesrep.custentityplan_ad_sw_hor_sub_q1} when'Software -Non Autodesk' then {salesrep.custentityplan_sw_

case {item.custitem_commission_code}when '1' then {salesrep.custentityplan_ad_sw_vert_pro_q1} when'2' then {salesrep.custentityplan_ad_sw_vert_sub_q1} when'3' then {salesrep.custentityplan_ad_sw_vert_sub_rn_q1} when'16' then {salesrep.custentityplan_sw_training_q1} when'15' then {salesrep.custentityplan_sw_serv_con_q1} when'17' then {salesrep.custentityplan_sw_support_q1} when'13' then {salesrep.custentityplan_img_srv_all_for_q1} when'9' then {salesrep.custentityplan_hw_maint_q1} when'7 ' then {salesrep.custentityplan_hw_equ_q1} when'4' then {salesrep.custentityplan_ad_sw_hor_pro_q1} when'5' then {salesrep.custentityplan_ad_sw_hor_sub_q1} when'11' then {salesrep.custentityplan_sw_non_ad_q1} when '8' then {salesrep.custentityplan_hw_referb_q1} when '14' then {salesrep.custentityplan_hw_inkjets_q1} end

Using Case When to locate text

It looks at the name of a records and Identifies when "Manfact" is in the text

case when {altname} like '%'||('Manufact')||'%' then 'Y' end

Using date in Case When

Case when (to_char({customermain.custentitydata_ad},'MM/DD/YYYY'))= '01/07/2010' then '1/7/10 Data' end

Case When with more than one select

case when{item.custitem_vertical}='AEC' and {item.custitem_product_type}IN('Software','Fee') then {amount}end

Multiple Case When statements with Wildcards

case when {transaction.type}in ('Invoice','Cash Sale') and {transaction.item}like'%Software - Non%' then {transaction.trandate} else (case when {transaction.type}in ('Invoice','Cash Sale') and{transaction.item} like 'Autodesk%' then {transaction.trandate}end)end

Case When statement to look up Multiple Zip Codes

To use this formula, make sure you are in the Criteria section of the search, select "Formula (Numeric)" then place the formula below in the "Formula*" section, select equal to, in the "Formula Numeric" section and the number 1 in the "Value" section. Make sure there are no line breaks or weird spacing after you copy the Numbers into NetSuite. CASE WHEN TO_NUMBER({zipcode}) IN (55122,55425,55123) THEN 1 END

Using Case When to select all date for the last 7 days (Time for last week by employee)

CASE WHEN ({today} - {time.date}) BETWEEN 0 AND 7 THEN {time.durationdecimal} end

Using Multiple "Not" in a Case When statement

This is used when you want to eliminate more than one value from a Case statement lookup, in the example below we want to display the income account only it is not UPS or Freight.

case when {item.incomeaccount} not in ('UPS', 'Freight') then {item.incomeaccount}end

Multiple Case When satements Grouped

Case When {type}in ('Invoice','Cash Sale') then ({netamount}*{contribution}) else (Case When {type}='Sales Order' and {status}in ('Pending Approval', 'Pending Fulfillment', 'Partially Fulfilled', 'Pending Billing/Partially Fulfilled', 'Pending Billing') then ({netamount}*{contribution}) else (Case when {type}='Deal' then (case {custbody_forecast_stage} when 'Commit ' then (({netamount}*{contribution})*.75) when 'Upside' then (({netamount}*{contribution})*.25)when 'Pipeline' then (({netamount}*{contribution})*.1) end)end) end) end

To display the milestone status with corresponding colors (red, yellow, green)

CASE WHEN ({status} = 'Completed' OR ROUND({percentworkcomplete}*100, 1) = 100.0) THEN 'Completed' WHEN (TO_DATE(TO_CHAR({startdate}, 'MM/DD/YYYY'), 'MM/DD/YYYY') - TO_DATE(TO_CHAR(SYSDATE, 'MM/DD/YYYY'), 'MM/DD/YYYY')) < -

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download