Pages

2010/07/16

Formula Fields and Saved Searches

Formula Fields are a powerful tool that let you combine and manipulate multiple field values within NetSuite Saved Searches. In this post, I will show you how to manipulate date fields, though it should be noted that formula fields can also be used for currencies, numbers and even text fields.

For today, let's use the Formula (Date) field to manipulate the value of a Date/Time field. For example, let's say you want to know the last day of the month in which a transaction was created for an opportunity. First thing you would need to do is create a search for opportunities. Then, in the Results tab, choose Formula Field (type) in the field column of the Columns Sub-tab.




Click on the Formula column of the new field row and then click on the Set Formula button.

You will be presented with a pop-up that has three fields: Function, Field and Formula.

The Functions drop-down offer you multiple SQL functions. For our example we will choose "LAST_DAY" because we want to know the last day of the month in which a transaction was selected.

"Last_Day(date)" should now have automatically been copied in the Formula field.

Next, we will choose our field in the Field drop-down. In this case, we want the Date Created. Choosing it will copy the field into the Formula field.

You may notice that Fields are always surrounded by curly brackets, whereas Functions have parentheses at the end of their names.

Now if we were to leave the Formula as is, we would get an error. The (date) means nothing so you need to use your cut/paste skills to move the {datecreated} into the parentheses of the LAST_DAY function (see screenshot below).

Regardless of what information you are trying to manipulate you will always have to delete the word that's in the parentheses and move what's in the curly brackets into the parentheses. A complicated but necessary fact.


We're done! Click on the Set button, click Add (or Done) on the column you've added, click Preview on your search and voilà, for each opportunity you can now see the date of the last transaction.


To become comfortable with formula fields and their effects I suggest playing around on a Test Saved Search and experimenting. Formula Fields give you endless possibilities and only by playing around will you be able to see just how powerful this feature is.

3 comments:

  1. I recently did a fairly complicated search and I used a formula that NetSuite help told me to use. Where can I find more examples of using formulas? Do you know of any? Do you have any more examples I can see? Or, is there a more in-depth coding book besides the developer guide pdf and the SuiteScritpt class (which I took, but made no mention of formulas in the UI as it was a scripting class)?.

    Also, is there a way to kind of chain searches so that you have, what amounts to be a union of selects, and return a long result set rather than a wide one. Something like select all the PM Contacts from Yadda company union all the Other Contacts union the accounting contacts, etc. And, as a plus, a way to get distinct returns.

    I wish there were more blogs like this. I just read all of you old posts when I ran across it yesterday and I was surprised to see so few comments. Don't go the way of the Dodo (or the NS forums). I need resources :)

    ReplyDelete
  2. Hello Corey,

    Indeed, Searching is not considered part of Suitescript or coding, so you won't find many resources there. However, if you look under the following Help Topic:
    SuiteAnalytics (Dashboards, Searches, & Reports) : Search : Running Searches : Using Formulas in Search : Search Formula Examples and Tips

    You'll find a number of actually pretty useful formulas. Another good resource is the User Group. This thread in particular - https://usergroup.netsuite.com/users/showthread.php?t=5889 - has many useful formulas. Also, in general, posting for help about a particular search usually yields answer, often by none other than Netsuite founder and CTO Evan Goldberg who happens to be an SQL Jedi.

    As for making joins, you are limited to the current Saved Search interface which does not allow outer joins, arbitrary joins, etc. Netsuite DOES have plan to release a freeform SQL query builder, but no release date for that yet... just stay tuned!

    As for our Blogs - thank you so much for reading and we are happy you find thyem useful! And don't worry, we're not going anywhere! We also (some would say obsessively ;) peruse the User Group and post answers there, so that is another option for anyone that needs help. And I wouldn't be a good consultant if I didn't point out the best help of all is to engage us in a support contract, where you get direct access to your own personal ERP Guru to pick his brain :)

    Cheers
    Olivier

    ReplyDelete
  3. Thanks for the heads up. I was able to log in to the user group today and it is a huge resource. I already have a better understanding of the formulas.

    ReplyDelete