[theideman]

So what I have is a search (Customer search) that has 2 field in the Criteria (Inactive and status), and just a few standard field in the Results field, when I run this search I get 52,000 results that is what I expect… Now if I add a formula “Case when {purchaseditem.parent}= ‘Software’ Then ‘AD’ end” the total is reduced to 17,000.

Can anyone explain what is happening here? Is this normal? Why would a formula field in the Results reduce the total records reported? Is there something wrong in my formula that is causing this issue?

Thanks for any Help.

Tom
This is a cached copy. Click here to see the original post.

[eleran]

I am going to ask a dumb question. Is that formula in the criteria or the results?

[fourthwaveconsulting]

It sounds like it’s filtering out customers who haven’t purchased… maybe add an else clause?

[theideman]

Well I will answer both questions, First is the formula in the criteria, and the answer is NOPE there are only 2 items in the criteria and they are is inactive and status. As to adding an else clause, that was tried it I did the (else ‘ ‘ end) and there was not change in the filtering.. Nice Try and I appreciate the ideas..

Tom

[al3xicon]

Tom –

Are you grouping your results? When I try running a search, adding that formula column actually increases the number of rows returned, as I believe it is returning more than one result per customer (each customer has purchased multiple different items).

[khultquist]

I believe what’s happening is this: if the join doesn’t exist, then the results can act like a filter.

For example, you have a simple search for all items, and you get 100 results. Only 10 of these have ever been sold, 2 times each. If you add a result column of transaction.amount, you will get 20 lines in the results.

[j.j]

its not weird. its native netsuite behavior. in case you use a field in column or filter and the transaction does not exist at all for the customer it will not show up. e.g. i used a workflow field in column of a saved search, than the results only show which have the workflow record else they dont show up.

theoritically it should pull up ALL results and show the column value as None but seems this has changed off late. earlier i remember getting all values and for data that did not exist showing up as none.

[theideman]

Originally posted by al3xicon

View Post

Tom –

Are you grouping your results? When I try running a search, adding that formula column actually increases the number of rows returned, as I believe it is returning more than one result per customer (each customer has purchased multiple different items).

The search is grouped on Company ID or Internal ID ( I tried both and got the same result) so I can get just one result per company. I agree with J.J and I thought in the past it would result in a Null response, what surprises me is that the else side of the case statement is having no results as well..

This is a very simple I have attached images of the Criteria PIC1 and the Results Pic2. If you want to see if your system does the same thing please let me know if your system behaves differently..

[Voltron]

Seems strange. A value in the Results should not be able to reduce the number of returned rows, only increase. I often put formulas in Results prior to putting them in Criteria to ensure they’re doing what I think they are; this apparent change in behavior neuters that possibility.

Knowing you and your capabilities with saved searches in particular, Tom, I’d think you would have quickly identified this as a bug and reported it to Support!

---

theideman replied on 08/25/2016, 06:26 AM: Thanks for you kinds words! I have created a case for NS but have not heard back from them yet. I went to the community first hoping that someone else had experienced the same issue and had some work around.. And my "trust" in NS support not real high they will say yup it is a problem and slap a S3 which always seems to mean " When we get around to it".

Tom

[noehcc]

seems like you have prob on the filter issue.

[david.smith]

Not sure if this will help but I recently ran into a similar issue. My search results were decreased when I entered specific fields into my result columns. It turned out to be an issue with the role that was performing the search. I had to change the department, class, and location defaults to none.

[Author]

Posts