SuiteScript 2.0 – N/Search – Formula Columns

What I’m about to share can be found in NetSuite’s documentation. However, like most everything I write about, it was hard to find!

Unless you know the answer, finding this is rough. When you search for “formulatext”, it’s the first article that comes up in SuiteAnswers!

Answer Id: 74736

This is my version of what it says:

Code Example

This search is reading the items table and combining the itemid and displayname into basically one field. It does it in both the filter as well as the results.

FYI… these are bogus part numbers. However, you can see the formula field is correctly labeled and formatted in the results.

Results

Here is that code snippet in case you don’t like typing.

                function setSearchType(exact) {
                    if (exact) return search.Operator.IS;
                    else return search.Operator.CONTAINS;
                }
                var filters = new Array();
                var columns = [
                    search.createColumn({ name: ‘internalid’, label: ‘ID’ }),
                    search.createColumn({
                        name: ‘formulatext1’,
                        formula: [
                            “case”,
                            “when {displayname} != {itemid} then ‘<b>’ || {itemid} || ‘</b><br />’ || {displayname}”,
                            “else {itemid}”,
                            “end”
                        ].join(‘ ‘),
                        label: ‘Name/Disp. Name’ }),
                    //search.createColumn({ name: ‘displayname’, label: ‘Display Name’ }),
                    search.createColumn({ name: ‘description’, label: ‘Description ‘}),
                    search.createColumn({ name: ‘companyname’, label: ‘Vendor’, join: ‘vendor’ }),
                    search.createColumn({ name: ‘baseprice’, label: ‘Base Price’}),
                    search.createColumn({ name: ‘costestimate’, label: ‘Estimated Cost’})
                ];
                if (itemName.length > 0) {
                    filters.push([
                        [‘itemid’, setSearchType(itemNameMatch), itemName],
                        ‘or’,
                        [‘displayname’, setSearchType(itemNameMatch), itemName]
                    ]);
                }
                if (vendorName.length > 0) {
                    if (filters.length > 0) filters.push(‘and’);
                    filters.push(
                        [‘vendor.entityid’, setSearchType(vendorNameMatch), vendorName]
                    )
                }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s