Creating NetSuite Saved Search Formulas | Questions and Answers

Joy Noreen Magoling-Oracle

Here is the list of the questions sent out during the webinar and the answers that were provided.

Is there a list of all the formulas/functions that can be used in searches?

Yes, you can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others.

Trying to create a saved search for Vendor Prepayments tied to PO's? Cant find anything online.

You can navigate to Reports > Saved Searches > All Saved Searches > New > Transaction. Under Criteria tab, you can add 'Type is Vendor Prepayments'. Under the Results tab, you can add the 'Applied to Transactions' to show the associated PO. Should you not be able to see desired results and need further assistance, feel free to post a question to the NetSuite Support Community and our gurus will be happy to help. :)

Are there any limitations or considerations to keep in mind when creating saved search formulas?

There is a 1000 character limit per formula expression. What this means is that we can only use 1000 characters when writing our formulas. You may be able to preview a search with a formula containing more characters, but when you run the search invalid expression errors occur. There is also a known 4000 character limit for results in a Formula (Text) field. 

What are some common troubleshooting techniques when encountering errors with saved search formulas?

This would depend on the error message encountered. For example:

- if you receive an “Invalid Expression” error, you can check if you are using the correct formula field type. You can also check if there is an incorrect or missing symbol like a parenthesis, quotations, or a comma.

- If you get an “Divide by Zero” error, you can use the NULLIF function in the denominator of a formula that uses division. 

- If you get a ""Field Not Found"" error, then you can check if the correct field ID is used, or if there are any hidden characters in the formula.

Can I reference a formula field in another formula field in a saved search?

Currently, referencing a formula field in another formula field is not yet possible in saved searches. This system limitation is tracked as Enhancement 121202 which you can vote for in SuiteIdeas.

Do these functions work the same when working with SuiteAnalytics datasets and workbooks?

Basically, yes, the logic would be the same. However, there might be some differences when working with formula in SuiteAnalytics datasets and workbooks. For example:

- We use the variable {today} in saved search formulas, while we use the function CURRENT_DATE in formulas for SuiteAnalytics datasets and workbooks.

- Another one is that we use the Applying Transaction and Applied To Transaction join fields in saved searches, while their equivalent in SuiteAnalytics datasets and workbooks is called Related Transactions – Next and Related Transactions – Previous.

You can check SuiteAnswers 82533 and 92588 for more information about formulas in SuiteAnalytics datasets and workbooks.

In the situation where the saved search is incredibly big and I try to export it but it errors out. What do I do?

You can try downloading it via CSV

Any way to clean up the field list by role? My role will most likely not use 95% of the fields listed.

You can customize the forms to be used by the role, on the form you can hide fields not to be shown on the role.

This is a saved search via the NS web service question. A result of Lists like the Ship Items provides the value. When using web services the same search provides the ID of the Ship Item value. When using web services how do I get the saved search to provide the value instead of its Internal Id?

This concern needs further testing. You can post this question to the Ask a Guru space of the NetSuite Support Community. Our gurus and members will be happy to assist you.

Is suitescript record browser being updated when there is a new NetSuite release?

Yes, always updated to the most recent data.

Is there a reason why nested joins are not accessible from the saved search UI, but accessible through scripted formulas?

This concern needs further checking. You can post this question to the Ask a Guru space of the NetSuite Support Community. Our gurus and members will be happy to assist you.

I can't download as a CSV due to the number formats getting altered such as UPC codes. Any other suggestions?

Some suggestions you can try are: 

1. To schedule the search and send it to email. Email has 10mb file size so it would be better to revise search to break in smaller chunks.  

2. Break the search to get partial results"

What is the formula for YoY sales on an item?

Try this formula: case when (({transaction.type} IN ('Invoice','Credit Memo')) and ({transaction.accounttype} = 'Income')) then {transaction.grossamount} else 0 end

I've noticed that when setting a starts with filter on the name field it acts as a contains filter. Is there a way around this?

You may check the custom fields details to check if there's any setting that causes this. Check SuiteAnswer 10078 Creating a Custom Field

Is there somewhere we can get a full list of the list of syntax's and their uses ? Or is it just sql syntaxs?

This can be seen in SuiteAnswers SuiteAnswers 10101.

How taxing on the search output are formulas? I noticed I had problems generating big saved searches when using a Formula(HTML) that was a little more complex.

Here are some common troubleshooting techniques when encountering errors with saved search formulas: • If you receive an “Invalid Expression” error, you can check if you are using the correct formula field type. You can also check if there is an incorrect or missing symbol like a parenthesis, quotations, or a comma. • If you get an “Divide by Zero” error, you can use the NULLIF function in the denominator of a formula that uses division.  • If you get a "Field Not Found" error, then you can check if the correct field ID is used, or if there are any hidden characters in the formula. Further, there is a 1000 character limit per formula expression. What this means is that we can only use 1000 characters when writing our formulas. You may be able to preview a search with a formula containing more characters, but when you run the search invalid expression errors occur. There is also a known 4000 character limit for results in a Formula (Text) field.

How to add hyperlink of the record in the results?

You may check this SuiteAnswer: 61522 Saved Search with Hyperlink

When I try to add a formula, I get a note that Description is mandatory but I can't edit it in the formula. How can I adjust that?

I assume you are under the Criteria tab > Filter. When adding a filter, descriptions are required. You should see an icon with an arrow beside the field dropdown. That is for setting description.

Do formulas implemented in saved searches affect the performance of the search in regards to time?

Yes, formulas could potentially affect saved search performance.

How would I make the days calculated show with 1 decimal?

Under Results tab, 'Function' column, select 'Round to Tenths'.

Regarding {today} - {datecreated} is there a way to set the results to only show two decimals or no decimal places?

Under Results tab > Function column, you may use either 'Round to Hundredths' or 'Round'.

It seems like the field names aren't consistent. The field name in the schema doesn't always match the dropdown list in the search editor. Is there a master list or conversion somewhere?

You may check the schema and list of field IDs on the records browser:  https://system.netsuite.com/help/helpcenter/en_US/srbrowser/Browser2017_1/script/record/account.html

Is there a way to remove the parent prefix in front of the child matrix items in a saved search?

You can refer to this discussion in NSC https://community.oracle.com/netsuite/english/discussion/4473093/community-care-and-share-remove-parent-item-name-in-saved-searches#latest

How does NS_CONCAT differ from CONCAT and the double pipes?

NS_CONCAT converts an array of results into a single text. CONCAT pulls in together strings into a formula. Double pipes work like CONCAT.

Is there a document or reference guide somehwere that lists all the functions available and a description and/or example of expressions expected?

Yes, you can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others.

Is there a way to show all items on a transaction (say a Sales Order) on a single line in saved search results?

This may be possible by use of SQL expressions in formulas.

What is the best vendor prepayment saved search criteria?

If you are creating a Transaction Saved Search, you must select Vendor Prepayment as a Type in the Criteria of the Transaction Saved Search, you might also want to check SuiteAnswers 94401

What can be used to compare the total of all invoices from a sales order to the sales order total (1 to many comparison)?

You may try using a transaction search, then try joining applying transaction fields. You may also add to the criteria, applying transaction : type = 'Invoice'

It looked like the floor & ceil columns were switched

FLOOR (returns the largest integer value less than a given number) while CEIL (returns the lowest integer value greater than a given number).

What is the benefits achieved between using concat and ||? Just easir to read formula?

Concat can be used for 2 expressions while || can be used for 3 or more expressions.

For 7.50, FLOOR should've returned 7 and CEIL should have returned 8, correct?

Yes, that's correct.

I read that text fields would not accept HTML commands anymore. Is the BR code still valid after this happens?

I think this needs more testing, but you may try using Formula (HTML) instead, if Formula (Text) is no longer valid.

The schema lists the field as ItemId but that field doesn't exist in the editor.

This may require further investigation. You can post this question to the Ask a Guru space of the NetSuite Support Community. Our gurus and members will be happy to assist you.

I didn't understand the Floor and Ceiling. I thought floor rounded down and ceiling up. But I took a screen shot and think the first few rounded up on the floor except the 504.85.

You may check SuiteAnswers 10101 SQL Expressions for more details.

I would like to create a saved search showing role permission difference with base role as "administrator"

There's actually a native Netsuite feature for this. You may navigate to Setup > Users/Roles > Show Role Differences.

What is the best way to determine which type of formula is needed: text vs numeric vs date. Sometimes it's not intuitive

The best choice for a formula type depends upon the calculation the formula will perform, because validation varies according to the formula type. For example, to do character manipulation using regular expressions, you should select Formula (Text). To do numeric calculations, Formula (Numeric) is likely a better choice. To include both positive and negative values in search results when using Quantity, set Formula (Numeric) in the Filter field and {Quantity} in the Formula field. If you use Quantity in the Filter field, you will receive only positive values in search results. All negative results are automatically filtered out. Options vary depending on the type of formula selected. For example, date formulas can be filtered based on whether the date occurs before or after a specified date, whereas numeric formulas can be filtered based on whether the resultant value is greater than or equal to the specified criteria.

Have to go (meeting), did enjoy your presentation, thank you. Please send me a cheat sheet of formulas (SQL) with examples, I use the NetSuite Help and has good info, but maybe you have something better.

You may check SuiteAnswers 10101 SQL Expressions for more details.

NS_CONCAT is not a documented function. Do you have a list of undocumented functions?

Unfortunately we don't have a list of undocumented functions.

I got the PO number to show. What would be a good way to show remaining vendor prepayment amount that ties total to Balance Sheet account

This concern needs further testing. You can post this question to the Ask a Guru space of the NetSuite Support Community. Our gurus and members will be happy to assist you.

What does summary type maximum mean?

Summary Type 'maximum' displays the maximum amount, or the most recent value. You can check Summary Type Descriptions in SuiteAnswers ID 8444.

Unfortunately when I select Formula (Date) or any other formula, and click on the icon with the arrow , and set my formula, no description appears and I can not add the line. Any thoughts on what might be going wrong?

Make sure that the field you are trying to add on your formula is available to be viewed by the role. You may check the actual record if the field you are trying to use is available.

I often have several rows returned on a search that appear to have all the same information. For example receipt lines listed multiple times. There must be something unique to these rows. How would I go about finding it?

You may need to identify the column/field wherein the value differs. This would cause the line item to show in multiple lines.

How could I return a percentage instead of the quantity for inventory in each location?

This is possible by incorporating SQL expressions in your saved search formula.

Does CEIL and FLOOR work with non-numeric values? like the minimum and maximum function

This may need to be tested, but I believe they don't work with non-numeric values.

What are some super common saved searches where these formulas would come in handy? demonstrate?

You may refer to SuiteAnswers 8439 for some Search Formula Examples and Tips

Is there a way to select all fields?

You may add separate columns/fields for each field you would need.

Is there a good resource to look up how to type the "if", "show", "then" etc?

You can refer to SuiteAnswers ID 10101, specifically the Case table. You may use the function syntax available there as a guide.

When using the NVL or NVL2, is it possible to add a formula that finds information in a memo field and presents it in the field (i.e. the memo has detail and i want to search the 6th position for the information)

Yes it is possible, just need to check the field/formula requirements to be used.

Why is the summary type for case and decode maximum?

You may check SuiteAnswers 8444 Summary Type Descriptions for more details.

I know there are some formulas in reports, but they seem to be confined to just arithmetic formulas and are pretty simple. Or am I just looking in the wrong place?

That is correct. There is a predefined formulas in reports for columns which are not subject for editing

I was trying to create saved search for the same but there was no filter to get 'list'

You may check SuiteAnswers 8474 Defining a Saved Search to check on how to set criterias and filters.

How can we dynamically change the custom retail fiscal period (custom calendar) in the CRITERIA FIELD, so that fiscal period automatically changes when there is period/date change say from July to August?

This concern needs further testing. You can post this question to the Ask a Guru space of the NetSuite Support Community. Our gurus and members will be happy to assist you.

Will a formula that compares [today} - some other date, will it work when it crosses years? And if you use sysdate will it work the same way?

Yes, per testing, using {today} and sysdate produces the same results

Did I miss the explanation of the difference between a search and a report?

You may check the following video: https://youtu.be/8-1UEMZmy2Y?list=PL8NUb0IcRT92j8ac489lpCEdM8KFwXh_F

Why did you use Maximum instead of Sum in the numeric formula example?

Are you referring to the formula used in the Demo for CASE/DECODE function to get the Item Qty per Location in columns? This was the formula used: CASE WHEN {inventorylocation} = 'Location A' THEN {locationquantityonhand} END. What we want to get is only 1 value, which is the value of the Qty On Hand on the specific location, which is why I used Maximum. Sum is usually used when we want to get the total value of mulltiple results. However, note that using this formula, it is expected that only the value of the Location Qty On Hand will be pulled in the search, so you can use either Sum or Maximum, and it should still show the same results in this case.

Is there a way to use a Left Outer Join in a saved search?

If you are referring to the ability to support multiple levels of join or subqueries (without using ODBC driver and 3rd party application), then currently it is not yet possible in saved searches. This is logged as enhancement # 125071

Can you use these formulas in other areas or just on the search field?

Yes, you may use this with other areas not only in search field like Search Criteria, Highlighting, or even in Custom Fields and Workflows.

Will the Saved Searches ever have an option to validate formulas, just like in Analytics?

As of now, error message will appear on the search result page if an issue on the formula is encountered.

What is the difference between null and blank?

Basically, null represents the absence of a value or an intentionally undefined value while blank refers to an empty field that has no visible characters or data.

After grouping, how can you show the results with amount totals?

You can mark the Show Totals checkbox under the Results tab.

Is there a demo for simple saved searches?

Since we have limited time to share more demos, you can actually post a question to the NetSuite Support Community should you need additional guidance on Saved Searches. Our gurus will be happy to help you.

If I want to extract data from the right until the first space, are REPLACE and SUBSTR the best place to start?

You may use the CASE WHEN, INSTR and SUBSTR functions to achieve your desired result.

What were the SuiteAnswers for the SuiteScript Field Viewer?

You may check SuiteAnswers 10493 Working with the SuiteScript Records Browser and SuiteAnswers 74610 SuiteScript Records Browser

Why is the dialog box so small when adding fields to a saved search? We should have the option of viewing a full screen field list to choose from

A popup of the formula will open when you click the arrow beside the text box.

Is there a master list of all the functions and a summary of what they do?

Yes, you can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others.

In the results, the column headers were reversed

Some examples in the demo part were in negative. In the demo, the first row had amount = -7.50. So FLOOR(-7.5) = -8 since -8 is lower than -7.5 and CEIL(-7.5) = (7) since -7 is greater than -7.5.

Can you use Formula(Date) in the Criteria to filter by Last Fiscal Month?

I couldn't find Last Fiscal Month as an option in the available selectors, so at this moment, it doesn't seem possible.

Where can I find the information and examples showed in the course last hour?

You can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others.

For the decimal formula, can the results still display 2 decimals even though you are rounding to just 1 decimal?

Yes, use Formula (Currency).

Is there a similar process to use formulas to summarize fields across lines (like the total summary at the bottom of the search), as opposed to adding columns?

There isn't a native preference/option for this yet, but you can use a formula to add the value of the fields or expressions that you want the summary total of. Example: {fieldid1} + {fieldid2} + {formula1}

Difference between formula(numeric) and formula (text)?

If the expected result is a text, the Formula Field type should be Formula (Text). If the expected result is a number, the Formula Field type should be Formula (Numeric).

When using invoices in a saved search what is the importance and use of Main Line =true? Or if set to False?

You can refer to https://suiteanswers.custhelp.com/app/answers/detail/a_id/48812/loc/en_US

How do you source the different qty colums in the sales pricing tab on a item. I can only source the first column

You may check the following SuiteAnswers: https://suiteanswers.custhelp.com/app/answers/detail/a_id/34301/loc/en_US

What is the best way to organize and deploy save searches (ex share between IT and Supply Chain?

You may set the visibility of searches on the Audience tab. There are options there to share in selected departments and/or groups.

So floor and ceil does not use the conventional 5 and up round up and round down if decimals is 4 or under?

No, it doesn't.

Can you nest the functions for absolute value and floor or ceiling?

Yes.

Could you send a link to more formula help?

Yes, you can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others

Where did she say the 100 ways to use formula fields was?

You may check this link: https://community.oracle.com/netsuite/discussion/4224310/100-ways-to-use-formula-fields/p1

In a formula, can I access other tables than the primary table the saved search is being performed on?

Currently, referencing a formula field in another formula field is not yet possible in saved searches. This system limitation is tracked as Enhancement 121202 which you can vote for in SuiteIdeas.

Can Ivy provide the formulas she used today?

You can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others.

Where to we find "100 ways to use formulas... ?

You may check this link: https://community.oracle.com/netsuite/discussion/4224310/100-ways-to-use-formula-fields/p1

Can you reference the results of a formula to be the input to another formula. or a formula of a different type. Like doing a numeric formula on column1 and then the results are used for a column 2 text output

Currently, referencing a formula field in another formula field is not yet possible in saved searches. This system limitation is tracked as Enhancement 121202 which you can vote for in SuiteIdeas.

Questions look similar to the ones we use in Microsoft Excel

If you are referring to the SQL formulas, then yes, some of these look similar to those used in MS Excel. However, one major difference is that in MS Excel, we use "" or double quotes when referencing Text/Strings, while in NetSuite search formulas, we use '' or single quotes.

I missed a reference to a list of functions/formulas - believe it was 10101. Can you confirm where to find it? Thank you!

Yes, you can refer to SuiteAnswers 10101 for the common SQL Expressions that can be used in NetSuite. We also have a thread called 100 Ways to Use Formula Fields in the NetSuite Support Community. You can browse that for some more examples and tips, or you can also post some of your own formula that may be useful to others

📺Watch NSC Webinar Replay | Creating NetSuite Saved Search Formulas here! 😊