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.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- 102 useful excel formulas cheat sheet pdf free download
- microsoft excel advanced towson university
- s p capital iq s excel plug in v 8 x frequently used formulas
- excel 2013 formulas and functions
- excel formulas university of detroit mercy
- netsuite formulas
- using date and date time in formulas
- calculated fields redcap how to guide
- intermediate excel formulas and other tips and tricks
- your excel formulas cheat sheet 15 tips for calculations
Related searches
- microsoft excel formulas pdf
- excel formulas download
- excel advanced formulas pdf
- all excel formulas pdf
- excel functions and formulas pdf
- excel formulas cheat sheet
- interest formulas compounded monthly
- food cost formulas worksheets
- excel formulas for interest rates
- financial ratios formulas and explanations
- excel formulas for bonds
- excel codes and formulas list