Help with HTML formatting in saved search result

Hi everyone. I am having difficulty getting this search to run with conditional HTML formatting in the results. I keep running into either the page returning a “An unexpected error occurred…” error or the search column returning “Error: Invalid Expression”.

 

 

CASE WHEN {locationnohierarchy} = ‘My Location’

AND SUM(CASE WHEN TO_CHAR({trandate},’MON’) = ‘JUL’ THEN {amount} ELSE NULL END)

BETWEEN .97 * 1836674.72 AND 1.03 * 1836674.72

THEN ‘<b><span style=”color:#ADFF2F;”>’ || SUM(CASE WHEN TO_CHAR({trandate},’MON’) = ‘JUL’ THEN {amount} ELSE NULL END) || </span></b>’

ELSE NULL

END

 

Any help is appreciated, thanks!

Rookie Asked on August 17, 2021 in Saved Searches.
Add Comment
3 Answer(s)

I personally expect you to have more issues trying to use SUM over the html strings. Although it probably isnt what you want, I’d try getting rid of the SUM’s to at least get the syntax correct first.

The most obvious problem that is seen here is the missing single quote in front of </span></b>'

Intermediate Answered on August 17, 2021.
Add Comment

Good catch, but unfortunately still the same error. I believe you are right though, the SUM function seems to be causing the issue. Any idea how to sum transaction line amounts by location and incorporating HTML formatting using a formula text?

Rookie Answered on August 17, 2021.
Add Comment

Its usually best relying on the filters, or in this case Summary Search Filters. Use a regular filter to only include rows belonging to your location and the summary filters to only gets groups of search results between your range.

If you want to continue suffering using a formula, then your next choice should be using the Analytic SUM over the Aggregate SUM. It sounds like you only need to partition by the internal id of the transaction. It relies on tricking Netsuite’s parser, so that should tell you how much I recommend my first alternative over this one.

Intermediate Answered on August 17, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor