One more twist to this formula....is there anyway to set the (MIN) within the Formula: CASE WHEN {activity.status} = 'Scheduled' THEN {activity.date} ELSE NULL END The reason is we have 4 custom fields included in the search results and when I set the Summary Type to Group for the non-Formula Date fields we lose the…

That seems simple enough but no records are returned when I use this and I know there are opportunities that match the criteria.

Would I use the same Formula (Text) if I want to reference this from an existing Saved Customer Search or would this require a different formula?

Still getting an Invalid Expression when I use the Formula Field (Text): CASE WHEN {systemnotes.field.id} = 'entitystatus' AND {systemnotes.newvalue} = 'Suspect' THEN {systemnotes.date} END

Ok. I tried to work around this by created a Custom Entity Field 'Next Scheduled Activity Date'. Type: Date Store Value: not checked Default Value: CASE WHEN {activity.status} = 'Scheduled' THEN {activity.date} ELSE NULL END But the date of the LAST COMPLETED activity is displayed INSTEAD of the next scheduled activity.…

Brilliant! Thank you so much! I used this formula in case anyone else needs this: Formula Date (MIN): CASE WHEN {activity.status} = 'Scheduled' THEN {activity.date} ELSE NULL END

I take that back..I had the wrong value in the criteria so now I have search results but the System Notes: Date in the results does not display the date the actual Milestone Status changed.

That was it! I used the following formula to get the hours. Thanks again for the help! trunc(nvl(max(decode({systemnotes.newvalue},'Ready for Approval',{systemnotes.date},NULL))-min(decode({systemnotes.newvalue},'Awaiting Discovery',{systemnotes.date},NULL)),0)*24)

Thank you so much for your help but I can't seem to get either formula to work. I appreciate your help as I am not familiar with functions and how I can use them in Saved Searches. The result is 'field cannot be found' when I use: formula(text) max to_char(max(case when {system notes: new value} = 'awaiting discovery' then…

Evan - Do you have any suggestions on how to formulate the search results to only display the first opportunity converted to Closed Won with projected total of $15,000.00? I think I should be able to do this in the Results NOT the Criteria. Am I on the right track? Do you have any ideas for the formula I should use?

Wouldn't the min(date closed) in may would be a problem when more than one opportunity exists under the customer? How can I account for this scenario: Upon closing a customer that has two opportunities...One with $20,000.00 is Closed Won in May = eligible for incentives. The second with $25,000.00 is Closed Won in June =…

I think the problem lies in "min (date closed) in may." I almost think I need to somehow set the results to only show the FIRST opportunity to close... We did something very similar to this awhile back for a last activity date report. https://usergroup.netsuite.com/users/showthread.php?t=10693 I've filed a case with…

You were right, Evan. Once I removed "MOD" the formula returns the percent. Thank you for your help! :) Jenny

You've got my vote! I am also an Administrator and I'm stuck with saved searches in Netsuite because a past employee went crazy by creating unnecessary searches. No one likes dirty data.

When field is Formula (Currency), Formula (Percent), or Formual (Numeric) and formula is MOD{projectedtotal}/nullif({customer.custentity15},0) result = "ERROR: Invalid Expression"

According to NetSuite Help, MOD MOD(n2, n1) returns the remainder of n2 divided by n1

Jeff, I think my last activity date formula is hosed. I found two separate examples that the saved search is incorrectly computing the last activity date. I was wondering if you have any ideas. Here is the formula we are using to display only the most recent activity date: Formula (Date) Maximum…

Got it. Now what formula do I use to only pull the first message? We only want to see the original request made by the customer. With this formula min(to_char(substr({messages.message},0,1000))) keep (dense_rank last order by {messages.messagedate}) - we get the most recent message... Thanks in advance! What's your secret?…

You guys are brilliant! Now, it looks like I'm referencing the wrong field in the formula. I want to see the actual Interations > Message from the customer. Right now, it appears I'm getting the Email Subject. 24170 01/29/2008 3:05 pm Escalated Orders-Rush Request (Ops) RE: Case # 24170 Escalated: "Reverify the…

My thinking exactly. I submitted an online case to NS Support to investigate. It looks the search is now hosed. #674977 I will try to recreate the search from scratch and post my findings.

I tried this and I get an error on the page: Line: 2829 Character: 13 Error: Invalid argument Code: 0 URL: https://system.netsuite.com/app/common/search/search.nl?id=368&e=T&cu=T

Now I'm back to: An unexpected error has occurred. Please click here to notify support and provide your contact information.

We couldn't be this lucky. When I recreate the search from scratch, and Save & Run I get: An unexpected error has occurred. Please click here to notify support and provide your contact information. ----- Also, when I remove the Formula (Text) field I do not recieve this error. The results display with no problems.

Ok, I put in the request to NS Support & was told he'd get back to me by the end of the week with an Enhancement Number. I will post it here as soon as I receive it.

Enhancement #136071, please vote. I'm very surprised that NS Support had to create a new enhancement for this request...

Hi Krish0nan - Unfortunately, I don't have a solution to provide but like you, I would prefer to have the option to remove/inactivate the standard views. Maybe NS can make this similar to the custom forms - we can enable/disable standard customer/contact forms with no problem. I really hope someone from NetSuite can jump…

Yes, but this only gives me: Date Set By Type Field Old Value New Value I was hoping there was a way to tell which transaction was added/removed?

Thank you - I am always stunned by the obvious solution I should start with. Thanks for your help!

I may have misrepresented our situation. I have successfully customized entity records for lead, prospect, and customer. My question is how do I assign a custom lead contact form, a custom prospect contact form, and a custom customer contact form to the appropriate entity?

I guess if SuiteScript is the only way to accomplish this...I'm curious what other sales teams' are using. Is it picky on my part that it blows my mind that I have to get a developer involved just to customize a subject field??