Match field strings in NetSuite

• Introduction
• The Setup
• Build the formula
• CASE WHEN
  • Results Field
  • Highlighting
• Conclusion

---

Introduction

I had a reader of this blog reach out with a question about how to check if the field text in different free form text fields matched using formulas in NetSuite. The use case involved matching a hash generated by an integration to see if parent and child records are associated in the integrated external system.

Luckily, NetSuite makes this pretty easy to accomplish using a CASE WHEN formula.

The Setup

I created 2 custom records to illustrate how this might work. I created custom record Parent Record and custom record Child Record. I added a field called Hash to both records and a field called Parent Record Link on the Child Record to link back to the Parent.

I have created one Parent Record with hash filled in and two Child Records with slightly different hashes, one matching the parent and one not.

Here are what the forms end up looking like.

Parent

Child

---

Build the formula

I created a saved search on Parent Records including the Child Record : Name so I can see the Parent Record and each child in it's own line.

CASE WHEN

Case is a powerful tool in NetSuite that allows for complex if/then logic. The syntax is pretty unforgiving (No double quotes allowed!), but once you get it down, you can really do a lot with this formula. The formula syntax is pretty conversational:

CASE
    WHEN
        {SomeField}
    LIKE
        {SomeOtherField}
    THEN
        'Say This'
    ELSE
        'Say That'
END

In this scenario I want to compare the values in the Parent : Hash ({custrecord_test_parent_hash}) field and the Child : Hash field ({custrecord_test_child_parentlink.custrecord_test_child_hash}).

If they match, I want to output some text that indicates a match and if not then output text that denotes the mismatch.

Results Field

Add a Formula(text) field to the saved search and add this formula. (Note - the formula does not have to be entered and tabbed like below. I wrote it that way for readability)

CASE 
    WHEN
        {custrecord_test_parent_hash}
    LIKE
        {custrecord_test_child_parentlink.custrecord_test_child_hash}
    THEN
        'HASH MATCH'
    ELSE
        'MISMATCH' 
    END

The Saved Search build tool should look like this now.

When the search is run now, there is a new column called Hash Match that shows a match on line 1 and a mismatch on line 2

Highlighting

We can use the same formula to highlight the saved search as well.

1. Edit the search again and go to Highlighting and add a Formula(text) field.
2. Copy the formula in again have the FORMULA(TEXT) condition set to "starts with" Hash.
3. Choose your highlighting method (I chose Checkmark)

Now there will be a green checkmark to the right side of records where Parent/Child have matching hashes.

Conclusion

The CASE WHEN formula is a deep topic and it has many use cases. It can be used every day in your saved searches, workflows, custom records and fields. The list goes on and on. This is a simple use case that allows for excellent comparison across records and fields.

Need help with NetSuite?

I build saved searches like this for clients every week. If you need help with reporting, formulas, or SuiteAnalytics — let's talk.

Book a Free ConsultationView Services & Pricing

By: Patrick Olson 12/27/2018

Tags: CaseWhenFormulasSuiteAnalytics

Read Next - Suite Analytics Workbook