https://netsuiteprofessionals.com logo
Join Slack
Powered by
Is there any limitation on string functions for lo...
# suiteanalytics
m
Is there any limitation on string functions for long text fields in saved searches? It seems like INSTR, REGEX_REPLACE, REGEX_SUBSTR only work on the first 1k characters of the field. Any workarounds? Slack Conversation
s
Do you have joins/complex criteria in your specific search? I believe strings are truncated at 1k characters in scenarios where that is the case.
This forum question has a response from a netsuite dev related to that scenario if taht is the case https://netsuitehub.com/forums/topic/text-area-limit-reached-in-saved-search/
m
No joins at all. I've tried with only a filter for the internal id of an affected record and still no go
To clarify, I'm not trying to retrieve the entire string in the results like the linked thread. I wanted to extract a short portion of it to display in the result. If the keyword I am searching for is in the first 1k characters it works perfectly, but if it's positioned later INSTR etc just act as if they don't see it.
k
you only get the first 1k of any field no matter what your selection method is.
So you have to use a substring to recall the characters that are in the middle - and then run your logic on it.
m
Thanks for confirming. In my case it's pretty tricky because I don't know the position in advance
k
You're probably better off scripting your criteria into creating a checkbox/other text/list field rather than trying to do this in a single saved search - because I don't think you can run a search that hits the same field multiple times in order to get all the results - you'd have to use 3 or 4 seperate criteria to get it.
i.e. "is it in first thousand characters Yes/No" or "is it in second thousand characters yes/no" or is it in 3rd thousand characters "Yes no"
etc. Which of course - would be a mind numbingly slow search to run.
m
I'm trying to use the formula in results, not criteria but your point still stands
k
and then what happens if your results start at 999 and the string goes through the remaining columns.
so now you're doing hackneyed joins where if you're looking for something 10 digits long you've got multiple columns for 1-1000 990 to 1990 1980 to 2980 etc.
either way - your script can do the same recursive check on the field and check a box on the record where a search can't quite do that.
m
I suppose I could write a horrible formula like CASE WHEN INSTR(SUBSTR({field, 0, 1000)), 'searchTerm') <> 0 THEN .... ELSE WHEN INSTR(SUBSTR({field, 900, 1900)), 'searchTerm') <> 0 THEN ... END
It's not something worth writing a script for at this point.
k
you might get that to work - but I'd expect it to bomb.
it'd just be nice if we could tell NS that we want results longer than a thousand characters even if it makes the search slower.
m
Out of pure curiosity I'll give this a shot later. It's not super important, just was a nice to have to add this field to the search
15 Views
Open in Slack
PreviousNext
Powered by