Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

Tip: How to Correctly Reference Transaction Types in Saved Search Formulas

2 min read

When crafting saved search formulas that depend on transaction types, one might be tempted to use the screen names of those transaction types (e.g. “Bill”, “Invoice”, “Journal”, etc.). However, since NetSuite allows users to rename transactions, this approach is brittle. In this article, I show you a more stable, account-independent approach to capturing transaction types in NetSuite Saved Search formulas.

TL;DR

Instead of using the customizable {type} field in your transaction Saved Search formulas, use the account-independent {typecode} field.

Situation

Recently, I needed to create a saved search in which, based on the transaction type, a different action was required. If you’re familiar with NetSuite saved search formulas, you know that a CASE or DECODE statement would work in this scenario. In my case, DECODE produced a more compact and readable formula. (Refer to SuiteAnswers Answer ID: 10101 for a list of supported SQL functions.)

Prefer DECODE over CASE whenever possible as DECODE generally produces more compact and easier-to-read formulas which can become critical if your formula is long and complex. Click To Tweet

Weak Approach

Consider the following simplified example. The function produces an identifier for different transaction types and defaults to “9” if no match is found.

DECODE({type}, 
  'Bill',    '1',
  'Invoice', '2',
  'Journal', '3',
  '9'
)

Unfortunately, the above solution is weak because it depends on the default names of the transaction types. However, these names can be changed via Setup >> Company >> Rename Records/Transactions! Consider the following results from an account where the “Bill” record has been renamed to “Vendor Bill”. As illustrated below, the formula incorrectly produces a “9” for Vendor Bill instead of a “1” due to the renaming.

Example showing incorrect results when using the type field

Better Approach

Consider the following improved approach. Instead of using the {type} field, we use the {typecode} field. The latter is fixed in the system for each transaction type and is not affected by renaming records.

DECODE({typecode}, 
  'VendBill', '1',
  'CustInvc', '2',
  'Journal',  '3',
  '9'
)

With {typecode} our formula works as expected:

Example showing correct results when using the typecode field

Granted, it is not typical for records to randomly get renamed in an account. In most cases, renaming would occur during the initial NetSuite implementation. Therefore, one might argue that the weak solution, though brittle, is good enough. However, that argument is not true if you are crafting solutions that should work across accounts, in which case, it is important that you make no assumptions about parameters that are configurable such as the screen names of transactions.

Reference List of Transaction Type Codes

I was unable to find any SuiteAnswers article that lists the {typecode} values for all transaction types. Gladly, there’s an easy way to extract this information: create a temporary transaction type saved search with criterion Type = any of <select all available transaction types>. Using a tool like the very useful NetSuite Search Export Chrome extension, you can then export the types. To increase your coverage, be sure to do this in an account with as many transaction types enabled as possible.

For your convenience, here’s a list of the types that I extracted using the above method, at the time of writing. While the list might not be complete, the values presented are accurate.

Transaction TypeType Code
BillVendBill
Bill CreditVendCred
Bill PaymentVendPymt
Bin Putaway WorksheetBinWksht
Bin TransferBinTrnfr
Blanket Purchase OrderBlankOrd
Cash RefundCashRfnd
Cash SaleCashSale
CCard RefundCardRfnd
CheckCheck
CommissionCommissn
Credit CardCardChrg
Credit MemoCustCred
Cross Charge JournalXChgJrnl
Currency RevaluationFxReval
Customer DepositCustDep
Customer RefundCustRfnd
DepositDeposit
Deposit ApplicationDepAppl
Deprecated Custom TransactionDeprCust
Expense ReportExpRept
Finance ChargeFinChrg
Inventory AdjustmentInvAdjst
Inventory Cost RevaluationInvReval
Inventory CountInvCount
Inventory DistributionInvDistr
Inventory TransferInvTrnfr
Inventory WorksheetInvWksht
InvoiceCustInvc
Item ReceiptItemRcpt
Item ShipmentItemShip
JournalJournal
Netting SettlementNettStlm
OpportunityOpprtnty
PaymentCustPymt
Purchase OrderPurchOrd
QuoteEstimate
RequisitionPurchReq
Return AuthorizationRtnAuth
Sales OrderSalesOrd
Sales Tax PaymentTaxPymt
Statement ChargeCustChrg
System JournalSysJrnl
TransferTransfer
Transfer OrderTrnfrOrd
Vendor PrepaymentVPrep
Vendor Prepayment ApplicationVPrepApp
Vendor Return AuthorizationVendAuth
WO BuildBuild
WO UnbuildUnbuild
Work OrderWorkOrd
Work Order CloseWOClose
Work Order CompletionWOCompl
Work Order IssueWOIssue

I hope you found this article useful.

NetSuite Insights is on a mission to raise the standards around NetSuite practices, one insight at a time. If that resonates with you, check out how you can become an author/collaborator here.

Also, consider subscribing to our no-nonsense email list to get these insights delivered to your inbox as soon as they’re published. Sometimes, ignorance is a choice. Choose wisely!

Related Posts

Chidi Okwudire IT Professional. ERP Enthusiast. NetSuite Certified (Administrator, SuiteCloud Developer II, and ERP Consultant). Celigo Certified (Level 4+). Passionate About Empowerment Through Knowledge Sharing. Always Eager to Learn.

8 Replies to “Tip: How to Correctly Reference Transaction Types in Saved Search Formulas”

  1. Thanks for the reference list, it might come handy. I didn’t know about {typecode} but you would get the same result with {type.id}.
    Another disadvantage of {type} in formula is that it’s language specific. So if user is using different language than English he would get transaction type translated. So {typecode} or {type.id} is the way around it.
    There is also a field {recordtype} (or Record Type in UI) that could be used but it’s returning different values. However, I remember a case when I used it to return record type of the transaction referenced in a custom record field and it didn’t work as expected, it returned the custom record type instead. But it only happened if I set it in UI as Custom Record Field : Record Type, as formula {custrecord_field.recordtype} it worked fine.

Leave a Reply

Your email address will not be published. Required fields are marked *

You deserve to know as soon as we share a new NetSuite Insight.
We won't disturb you with anything else or share/sell your data.