Formula - XHTML

Formula - XHTML provides a way to decorate column values with tags and styles. It is a mixture of limited (X)HTML markup and TeamDesk formula language.

Using Formula - XHTML, you can write HTML markup as is, decorating TeamDesk formula pieces (code points) with <%…%> marks; those who have dealt with ASP should be familiar with the syntax. Each formula piece is evaluated; the result of evaluation is properly encoded and inserted into the markup.

Code points can appear inside attribute values and/or in tag contents, and dynamic tag generation is not allowed. We designed it this way to ensure the formula result is well-formed HTML and won’t affect the rest of the page’s content.

Here are typical usage examples for the formula:

Coloring the Status

Formula-XHTML color status

Color a Status value green if it is "Completed", red if it is "Overdue", otherwise black.

To do this, add a new Formula - XHTML column and name it "Status(Colored)". This column can be added to a Table View instead of the existing "Status" text column, which can be used only for record editing. When the Formula - XHTML column is created, you can enter the following formula:

<font color="<%Case([Status],"Completed","#339900","Overdue","#ff3333","")%>"><%=[Status]%></font>

Contact Name as Email Link

Formula-XHTML E-mail Link

Decorate contact names as e-mail links.

Create a "Contact Person" Formula-XHTML column, where the existing "Name" column is associated with a corresponding e-mail listed in the E-mail column.

Enter the following formula:

<a href="<% Nz("mailto:" & [E-mail]) %>"><% [Name] %></a>

Behind the scenes, Formula - XHTML creates a text formula: a concatenation of markup code and formula results enclosed in an HTML encoding function, such as the code below produced from the example:

"<a href=\"" & Encode(Nz("mailto:" & [E-mail])) & "\">" & Encode([Name]) & "</a>"

Coloring Numeric Values

Formula-XHTML color numbers

Color a value red if it is less than 100.

Create the "Value (Colored)" Formula-XHTML column, where the values should be colored.

Enter the following formula:

<span style="<% If([Value] < 100, "color:red", "") %>">
<%= [Value] %>
</span>

Highlighting Date Background

Formula-XTML highlight date background

Highlight the background of an Expiry Date value in red if it is less than today+60 days and in yellow if a value is less than today+180 days.

Create the "Colored Date" Formula-XHTML column, where the "Expiry Date" column value background should be highlighted.

Enter the following formula:

<span style="background-color:<%If([Expiry Date]<Today()+60d,"red",[Expiry Date]<Today()+180d,"yellow","")%>"><%=[Expiry Date]%></span>

Changing the Font Size

Formula-XTML font size

Increase/decrease the font size.

The Formula XHTML keeps the following formula:

<span style="font-size:300%"><%[Test Column]%></span>

You can specify the percentage value you need and write a corresponding column name instead of "Test Column".

Be aware of the way concatenation handles NULL values - if one of the parts is NULL, the whole result is also NULL. The first example produces no markup at all when the value of [Name] is blank. While it is useful in some scenarios, enclosing each and every field you want to display in a sort of NULL checks is a cumbersome task. To handle this case, we created a shorthand for code points containing a sole reference to a column, <%= [Column] %>. It creates an Nz() wrapper so that NULL values produce empty strings that can be safely concatenated. Numbers, dates, times, and timestamps are converted to text via locale-aware Format() function, and other types are converted via ToText(). In the second example, <%= [Value] %> produces Encode(Nz(Format([Value]))).

The usage of HTML and Formula - XHTML is somewhat limited due to technical reasons.

  1. Dropdown lists, as dropdowns do not support markup;
  2. Calendar views and Lookup columns with "Display As Link" options checked, as a very limited set of tags can be displayed inside of a link;
  3. Documents as the Word file format has no connection to HTML.