CODEWITHSUNDEEP
netsuite
user3859504
user3859504

Reputation: 3

Netsuite Saved search to create substring from single field

I want to create saved search (Netsuite) which return me number of time we got same answer.

Scenario: Suppose we have question and 4 answer for that question(User can select multiple answer) and all answer are stored in same field separated by comma.

Now I want to create saved search which return me number of time user has selected same answer.

Example: Suppose Question 1 has four multiple choice as A, B, C, D

Response 1--> Question1--> A, B, C, D

Response 2--> Question1--> A, B,

Response 3--> Question1--> A, B, C

Response 4--> Question1--> A

Now my saved search should return me

Question --> Answer --> Count

Question 1 --> A --> 4

Question 1 --> B --> 3

Question 1 --> C --> 2

Question 1 --> D --> 1

Upvotes: 0

Views: 2094

Answers (3)

nzaleski
nzaleski

Reputation: 441

A search like this should work. This is results portion of the search.

Not sure if you need to use SUBSTRING expressions, but I could be getting something wrong. NetSuite does provide substring and regex expressions. See help 'SQL Expressions' 

Question GROUP BY   //first column 
Response GROUP BY   //second column
Formula(Numeric) SUM   // third...n columns is number of responses 
CASE {custbody_response1} WHEN 'A' 
   THEN '1' ELSE NULL END

Create n number of columns for responses with the same case statement {custbody_response2..n}

I am just guessing the name of the column is custbody_response. Also I am assuming you have a custom field for the question?

Upvotes: 0

Adolfo Garza
Adolfo Garza

Reputation: 3029

Use a Formula(Numeric) column and use the following formula:

LENGTH({QUESTION1})-LENGTH(REPLACE({QUESTION1}, 'A', ''))

Where QUESTION1 is the fieldid and 'A' is going to return you the number of times A is in the specified string. You can modify that formula and have it in 4 different columns, one for each letter, or you can expand on it using CASE, etc.

Upvotes: 0

TonyH
TonyH

Reputation: 1181

I think you should consider building a RESTlet to answer your queries. You could set it up to take a parameter for question number, or have it simply spit out all answers.

Inside your scriptlet, you'd have a saved search to return all your question data. Then, you'd iterate through the list, digest the answers and build your response.

Ultimately, your RESTlet will then split out your response as a JSON object.

OR

If you need a GUI, build a Suitelet instead. The logic would be the same for the RESTlet, but you'd pipe the resulting data into a simple table view.

Upvotes: 1

Related Questions