CODEWITHSUNDEEP
htmlsqloracle-databasenetsuite
Lee Tillman
Lee Tillman

Reputation: 1

NetSuite Saved Search using formula (numeric) with html for formatting

I need to make a saved search which sum's all quotes made per sales rep by month and highlight certain cells based on sum amount of numeric formula.

This is what I have now (it's a Saved Transaction Search)

Criteria
Type is Quotation
Main Line is True
Sales Rep is any of ,,...
Date is after start of this year.

Result
Sales Rep Group
Formula (Numeric) SUM CASE WHEN to_char({custbody_Lastupdateddate}, 'MM')= '01', then 1 else 0 end.
Formula (Numeric) SUM CASE WHEN to_char({custbody_Lastupdateddate}, 'MM')= '02', then 1 else 0 end.
Formula (Numeric) SUM CASE WHEN to_char({custbody_Lastupdateddate}, 'MM')= '02', then 1 else 0 end. ...This continues until May.

I now need to highlight the cell based on quantity of quotes per rep per month. I have dabbled with HTML but cannot seem to figure out how to write the formula to accomplish this.

Does anyone have any feedback? Thanks in advance.

Upvotes: 0

Views: 2796

Answers (1)

Krypton
Krypton

Reputation: 5231

You can return HTML from your formula, using Formula(text). The trick is to move your aggregate functions inside the formula, and simply use MINIMUM as the Summary Type. The following formula should get you close to what you want:

CASE
   WHEN
      COUNT(
      CASE
         WHEN
            to_char({trandate}, 'MM') = '01' 
         THEN
            {internalid} 
      END
) > 50  --more than 50 quotes in the month should be green
   THEN
     '<div style="color:white;background-color:green;">' || 
COUNT(
      CASE
         WHEN
            to_char({trandate}, 'MM') = '01' 
         THEN
            {internalid} 
      END
) || '</div>' 
   ELSE   --the rest will be red
      '<p style="color:white;background-color:red;">' || COUNT(
      CASE
         WHEN
            to_char({trandate}, 'MM') = '01' 
         THEN
            {internalid} 
      END
) || '</p>' 
END

You'll see I used <div> and <p> tags - both work. <span> and <table> tags work too; what you end up using might depend on other specific display characteristics I won't go into here.

Upvotes: 1

Related Questions