How to manipulate text and values in a column using scripting

1 post / 0 new
Terje
Terje's picture
How to manipulate text and values in a column using scripting

Sometimes it you'll want to change how the text or values are displayed in a table column. You can do some modifications straight in our dialogs, but by creating a small script you can do even more.
When editing a column in the editor, navigate to the expression tab. Here you can write your own custom code to do all sorts of great stuff.

Here is the basic script you need:

=eval(switch("[ProbabilityName]"){
   case "Sold":
      1;
      break;
   case "Lost":
      2;
      break;
   default:
      0;
});

[ProbabilityName] is the name of the column you want to check.

case: create one case statement for each value you want to check and replace.
In the case statement, enter the value, text or html you want to replace the original value with. If you insert html here you need to allow the column to show html (column setting in the DataCentral).

In the example above we replace the text "sold" and "lost" with a number.

 

Example 2: Replace a text with another text.

=eval(switch("[ProbabilityName]"){
   case "Sold":
      "This is sold";
      break;
   case "Lost":
      "You lost this one";
      break;
   default:
      "-";
});

 

Example 2: Replace a number with text

=eval(switch([Amount]){
   case 10000:
      "This is 10 000";
      break;
   case "100000":
      "This is 100 000";
      break;
   default:
      "-";
});

NOTE: The column name is now not inside quote marks ("") because it's a number.

 

If you want to replace a number or text with html, just insert the html code in quote marks:


"<img border='0' src='../../Common/Images/IconTemplates/Faces/greenlight.png'>";

 

 

TIP! If you replace a text with a number, you can the apply standard range indicators on the column to show graphic and set color to the background and text.