Netsuite Saved search to create substring from single field

Question

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

score 1 · Answer 1 · answered Jul 19 '16 at 14:36

1

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.

answered Jul 19 '16 at 14:36

TonyH

1,117
8
18

Thanks Tony for your response. i will try that but is there any way we can do it using SQL query...... – user3859504 Jul 20 '16 at 04:33
Try looking up PLSQL commands relating to substrings and substring indexes. Also, this SO seems like it could be adapted for your proposes. http://stackoverflow.com/questions/4389571/. You might try re-asking this question specifically formulated as a SQL question, with your data formatted like the actual DB tables. – TonyH Jul 20 '16 at 12:24

score 0 · Answer 2 · edited Jul 24 '16 at 13:01

0

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.

edited Jul 24 '16 at 13:01

halfer

19,824
17
99
186

answered Jul 23 '16 at 07:43

Adolfo Garza

2,966
12
15

nzaleski · Answer 3 · 2016-07-25T13:10:44.387

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?

Netsuite Saved search to create substring from single field

3 Answers3

OR