Category Archives: formula

Easy 3D hyperlink buttons using SVG VisualForce pages

This article gives an example of how to use an SVG VisualForce page to generate images for buttons.

The button is a formula that uses HYPERLINK() and IMAGE() functions that link to a VisualForce page passing parameters color and text.

HYPERLINK( “/” + Id + “/e”
, IMAGE( “/apex/ButtonImage?color=”
+ CASE( TEXT( Industry ), ‘Retail’, ‘darkgreen’, ‘Food & Beverage’, ‘orange’
, ‘Apparel’, ‘blue’, ‘Entertainment’, ‘cyan’, ‘red’ )
+ “&text=Edit+”
+ SUBSTITUTE( Name, “&”, “%26amp;” ) + “.”, “Test” ), “_self” )

The result is shown below in a report:  the VisualForce page renders a button varying the color according to the Account.Industry field and the text “Edit <Account.Name>”.
Screen Shot 2016-05-19 at 11.20.12 PM

It requires this simple VisualForce page with no controller (GitHub link below):

As explained in the first article of this series, this is a VisualForce page that uses the Scalable Vector Graphics format to specify an image using plain XML.

In addition to that, the page uses a few interesting resources to create a button image with 3D effect:

  • apex:variable tags to calculate the size of the image based on the length of the text, and sets default colors when parameters are missing
  • linearGradient tags (SVG) to create a highlight at the top of the button and a shadow at the bottom
  • rect tags (SVG) to create the button itself and add the gradient effects to make the button appear 3D-like
  • text tags (SVG) to display the button text with a shadow (more easily noticed when the color is light like the cyan button above)

There is a quick tutorial on SVG available on w3schools.com

The next article will show an example of how barcodes can be dynamically rendered by an SVG VisualForce page to be used in reports and page layouts.
Screen Shot 2016-05-19 at 11.51.26 PM

Advertisements

Bending fields to our will in reports and page layouts with SVG VisualForce pages!

If you add a long text field to a report, you may end up with something undesirable like the image below:  a field of plain text squeezed and stripped of any formatting.

Screen Shot 2016-05-17 at 10.36.35 PM.png

The “normal” way of fixing that would be to create a plain text field and use a workflow to copy the first 255 characters of the long text field to it – that is, truncating the long text field.

This article shows another way of overcoming that limitation and making long text fields display with a fixed size and word wrap and in color without truncation.

In the previous article it was shown how to display formatted text using a VisualForce page that accepted parameters and converted text to a SVG image.
Now that idea will be expanded:   create a page that will accept any field from any object and generate an image after applying word wrap and color to its text value. The result is below:

Screen Shot 2016-05-17 at 11.00.43 PM.png

Much nicer!

Here is how it was done:  an IMAGE formula field (below) that sends parameters to a VisualForce page which returns a SVG image.

IMAGE( ‘/apex/FieldToImage?wrap=60&color=’
+ IF( CONTAINS( ‘Energy|Construction|Transportation|Shipping|Utilities|Manufacturing|Agriculture’, TEXT( Industry ) ), ‘red’, ‘green’ )
+ ‘&object=Account&fieldName=description&id=’ + Id, ‘Color Description’ )

Please notice the following parameters passed to the FieldToImage VisualForce page:

  1. wrap=60 – this limits each line to 60 characters
  2. color= red or green, depending on the Account Industry – self-explanatory
  3. object=Account – this tells the page controller to retrieve data from the Account
  4. fieldName=description – this tells the page controller to retrieve data from the Account.Description field
  5. id= the Account ID – self-explanatory

Instead of passing the text directly to the page, we passed the names of the object and field because formulas cannot reference long text fields directly (by the way, this idea needs more upvotes).

The code that implements the page and controller is here (GitHub links):

  1. FieldToImageController.cls
  2. FieldToImage.page

The Apex controller uses the parameters “object”, “fieldName” and “id” to create a dynamic SOQL query. It then fetches the text from the object and substitutes a couple of special characters in order to not break the SVG syntax.

// fetch the field and convert to text
List<SObject> objList = Database.query( ‘SELECT ID, ‘ + fieldName
+ ‘ FROM ‘ + objectName + ‘ WHERE ID = \” + theID + ‘\’ LIMIT 1′ );
if( objList.size() <= 0 ) {
return;
}
String rawText = String.valueOf( objList[ 0 ].get( fieldName ) );
if( rawText == null ) {
return;
}
rawText = rawText.replace( ‘<‘, ‘&lt;’ ).replace( ‘&’, ‘&amp;’ );

This idea can be expanded to make the VisualForce page return and display long text from related records or combined with parent records.
This is something that is not possible to do with just a formula since long text fields cannot be referenced in formulas and there are limited ways of accessing children records in the context of a page layout or report.

The next article will show a way to create dynamic hyperlink buttons – like the ones below – to display on page layouts and reports:

Screen Shot 2016-05-17 at 11.48.14 PMScreen Shot 2016-05-17 at 11.38.52 PM

 

 

 

Colors in reports and page layouts with SVG VisualForce pages

Other than using Rich Text fields, there are rigid limits in regards to how fields appear on a page layout or report.
In the Account report below, you can see a text field that appears in different colors depending on the industry of the account. This article shows one way to obtain that functionality.

ReportColoredText

Below is the formula used to display the colored text:

IMAGE( “/apex/TextToImage?color=”
+
IF( CONTAINS( ‘Energy|Construction|Transportation|Shipping|Utilities|Manufacturing|Agriculture’, TEXT( Industry ) ), ‘red’, ‘green’ )
+ “&text=”
+ SUBSTITUTE( Name, “&”, “%26amp;” ) + ” (”
+ IF( CONTAINS( ‘Energy|Construction|Transportation|Shipping|Utilities|Manufacturing|Agriculture’, TEXT( Industry ) ), ‘Basic’, ‘Service’ ) + ” Industry)”, “Test” )

Notice how it starts with an IMAGE() function with a concatenation of:

  1. a link to a VisualForce page
  2. a parameter “color” followed by…
  3. …an IF() function that determines whether the text should appear in red or green
  4. a parameter “text” followed by…
  5. …a SUBSTITUTE() function that replaces any “&” character in the Account Name with an equivalent escape code
  6. another IF() function that determines whether to append the words “(Basic Industry)” or “(Service Industry)” depending on the picklist value of the Account Industry

That is right, this formula transforms a text field into an image using a VisualForce page! How is that possible?

Here is the page markup for TextToImage:

<apex:page sidebar="false" showHeader="false"
 applyHtmlTag="false" applyBodyTag="false" 
 standardStylesheets="false" 
 ContentType="image/svg+xml" >
    <apex:variable var="textWidth" 
         value="{! LEN( $CurrentPage.parameters.Text ) * 10 }"/>
    <apex:variable var="textColor" 
         value="{!IF( $CurrentPage.parameters.Color = null, 
                  'red', $CurrentPage.parameters.Color )}" />

    <svg id="layer_1" 
        xmlns="http://www.w3.org/2000/svg" 
        xmlns:xlink="http://www.w3.org/1999/xlink" 
        height="20" width="{! textWidth }">
      <text x="0" y="15" font-family="Verdana" 
          font-weight="bold" fill="{! textColor }">
          {! $CurrentPage.parameters.Text }
      </text>
    </svg>
</apex:page>

In this page there are 3 unusual things:

  1. no controller, no header, no sidebar, no style sheet
  2. the ContentType is set to image/svg+xml
  3. the SVG tag containing a TEXT tag

The SVG image format – Scalable Vector Graphics – is relatively less known standard but it is very versatile and compatible with virtually all browsers. It allows us to define an image using plain XML.

In order to generate an image, this VisualForce page uses the parameters Text and Color. Color can be any CSS color code such as #FF00FF, #00A0C0, etc or a CSS color name such as red, green, darkblue, etc.
An URL like below would make the text “This is a test.” appear in cyan:

/apex/TextToImage?color=cyan&text=This+is+a+test.
Screen Shot 2016-05-17 at 12.59.35 AM

This way of manipulating text with SVG opens the door to a few ideas and allows interesting features that will be explored in the next articles… (hint:  check out the bar codes in the first image!)

3 Steps to optimize a complex formula in Salesforce

This article was also posted in LinkedIn:  3 Steps to optimize a complex formula in Salesforce.

The formula below was created straight from the client requirements and didn’t fit the maximum formula size (5k bytes) when compiled.
It was intended to display a red, yellow or green square depending on how many days the account was past due and according to the type of the account.
Here are 3 steps to optimize it to fit the limit.

IF(
ISBLANK(Type__c),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
OR(
(Type__c = “TA”),
(Type__c = “DB”),
(Type__c = “AC”)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
(Amount__c < 1), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
(Days_Past_Due__c <= 7)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “C2”), (Amount__c > 1),
(Days_Past_Due__c > 7)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
OR(
(Type__c = “PC”),
(Type__c = “CN”)),
(Amount__c > 1),
(Days_Past_Due__c >= 1)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “P2”),
(Amount__c > 1),
(Days_Past_Due__c <= 7)), IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10), IF( AND( (Type__c = “P2”), (Amount__c > 1),
(Days_Past_Due__c > 7)),
IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10),
IF(
AND(
(Type__c = “WT”),
(Amount__c > 1),
(Days_Past_Due__c <= 14)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
(Days_Past_Due__c > 14),
(Days_Past_Due__c <= 21)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WT”), (Amount__c > 1),
(Days_Past_Due__c > 21)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “WF”),
(Amount__c > 1),
(Days_Past_Due__c <= 28)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
(Days_Past_Due__c > 28),
(Days_Past_Due__c <= 36)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “WF”), (Amount__c > 1),
(Days_Past_Due__c > 36)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “MT”),
(Amount__c > 1),
(Days_Past_Due__c <= 37)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
(Days_Past_Due__c > 37),
(Days_Past_Due__c <= 45)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MT”), (Amount__c > 1),
(Days_Past_Due__c > 45)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
IF(
AND(
(Type__c = “MS”),
(Amount__c > 1),
(Days_Past_Due__c <= 67)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
(Days_Past_Due__c > 67),
(Days_Past_Due__c <= 75)), IMAGE(“/img/samples/color_yellow.gif”, “Yellow”, 10, 10), IF( AND( (Type__c = “MS”), (Amount__c > 1),
(Days_Past_Due__c > 75)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),
NULL
))))))))))))))))))))

1) Reorganize/group the conditions by their outcome

Notice the repeated references to red, yellow and green image links scattered throughout the formula. That sequence of the conditions probably match what makes sense to the end user but this arrangement leads to redundancy in the formula.

Below is how it looked like when I started grouping the conditions for Green:

IF((Amount__c < 1), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “WT”), (Amount__c > 1), (Days_Past_Due__c <= 14)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “WF”), (Amount__c > 1), (Days_Past_Due__c <= 28)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “MT”), (Amount__c > 1), (Days_Past_Due__c <= 37)), IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),
IF( AND( (Type__c = “MS”), (Amount__c > 1), (Days_Past_Due__c <= 67)),
IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),

The purpose is to reorganize the conditions in preparation for the next step, which is:

2) Remove nested IFs – use ORs and line breaks

This is easier to show than to explain, but it means to replace the several nested IFs with a single IF with a long sequence of conditions, each condition separated by || which is the shorthand for OR.

It follows the pattern IF expression1 || expression2 || expression3 … where each OR expression is in a separate line for legibility (use Ctrl or Shift + Enter).
Each expression can (usually) contain nested ANDs.

Screen Shot 2015-09-25 at 9.17.49 PM

Here is how it looks like for the Green conditions reorganized in the previous step.
Notice that now there is only a single reference to the green image link.
The compiled formula is now smaller because the redundant nested IFs and IMAGE references were replaced.

IF((Amount__c < 1)
|| AND( (Type__c = “WT”), (Amount__c > 1), (Days_Past_Due__c <= 14))
|| AND( (Type__c = “WF”), (Amount__c > 1), (Days_Past_Due__c <= 28))
|| AND( (Type__c = “MT”), (Amount__c > 1), (Days_Past_Due__c <= 37))
|| AND( (Type__c = “MS”), (Amount__c > 1), (Days_Past_Due__c <= 67)),
IMAGE(“/img/samples/color_green.gif”, “Green”, 10, 10),

Additionally, you can use && (shorthand for AND) and remove unneeded parenthesis:

Screen Shot 2015-09-25 at 9.25.49 PM

This way you can further identify repeating patterns that you can optimize and eliminate redundancy.

At this point, the formula was pretty close to fitting the limit, but if the formula was lengthier, there are other optimizations to use.

3) Identify repeated conditions and create separate formulas/expressions for them… using a few tricks!

IF( Amount__c < 1
|| ( Type__c = “WT” && Amount__c > 1 && Days_Past_Due__c <= 14 )
|| ( Type__c = “WF” && Amount__c > 1 && Days_Past_Due__c <= 28 )
|| ( Type__c = “MT” && Amount__c > 1 && Days_Past_Due__c <= 37 )
|| ( Type__c = “MS” && Amount__c > 1 && Days_Past_Due__c <= 67 )
, IMAGE( “/img/samples/color_green.gif”, “Green”, 10, 10 ),

Notice in the above formula that for type WT the past due limit is 14, while type WF has limit of 28 and so forth for MT and MS? Notice how the amount > 1 condition is the same for all of the 4 cases.

You can condense all the “type-past due” conditions into a single CASE() function like below:

Screen Shot 2015-09-25 at 10.01.23 PM

IF( Amount__c < 1
|| ( Amount__c > 1 && Days_Past_Due__c <= CASE( Type__c, “WT”, 14, “WF”, 28, “MT”, 37, “MS”, 67, -9999 ) )
, IMAGE( “/img/samples/color_green.gif”, “Green”, 10, 10 ),

That way Days_Past_Due__c will be compared to a different value depending on the Type__c. The -9999 is there just to fail the condition when the type is not one of the 4 (WT, WF, MT or MS).
The CASE expression could also be placed in a separate formula named “Past_Due_Limit_Per_Type__c” to help with legibility (it doesn’t reduce the compiled size of the formula though).

Another way of condensing multiple OR conditions like the ones found below is to use CONTAINS().

IF(
OR(
(Type__c = “TA”),
(Type__c = “DB”),
(Type__c = “AC”)),
IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),

See below:  you can use CONTAINS and group all the types to check in a single string with vertical pipes as delimiter. When the type matches TA, DB or AC, it will find the type inside the string “|TA|DB|AC|”.

IF( CONTAINS( “|TA|DB|AC|”, Type__c )
, IMAGE(“/img/samples/color_red.gif”, “Red”, 10, 10),

The higher the number of values to check, the more you benefit from this trick.

Beware of when the value does not have a fixed length:  if Type__c is “A” it will match “TA” in the above expression, so be careful! To make the formula safer, you can use “|” + Type__c + “|” instead of Type__c.

In summary…

The 3 steps are:

  1. Reorganize the complex formula by outcome/result

  2. Replace nested IFs with the pattern OR ( exprA1 AND exprA2 AND … ) OR ( exprB1 AND exprB2 AND … )

  3. Identify repeated conditions and create formula/expressions for them