Formula-XHTML

Formula – XHTML provides the 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 of you who dealt with ASP should be familiar with the syntax. Each formula piece is evaluated; the result of evaluation is properly encoded and inserted in the markup.

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

Here are typical usage examples for the formula:

Example 1

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

You can add a new Formula – XHTML and name it “Status(Colored)”. This column can be added to a Table View instead of the existing “Status” text column that can be used only for record editing. When the Formula – XHTML column is created, you can enter the following formula there:

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

Example 2

Decorate contacts name as an e-mail link:


Create the “Contact Person” Formula-XHTML column, where the existing “Name” column is associated with a corresponded 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 HTML encoding function, such as the code below produced from the example:

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

Example 3

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>

Example 4

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

Create the “Colored Date” Formula-XHTML column, where “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>

Example 5

How to 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 whole result is also NULL – first example produce 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, a <%= [Column] %>. It creates 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, other types converted via ToText(). In second example, <%= [Value] %> produces Encode(Nz(Format( [Value] ))).

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

1. Markup will be displayed as text in:

Dropdown lists, as dropdowns do not support markup;

Calendar views and Lookup columns with "Display As Link" options checked, as very limited set of tags can be displayed inside of link;

Documents as the Word file format have no connection to HTML;

2. Some tags are disabled to avoid interference with the rest of the page:

<html>, <head>, <script>, <noscript>, <title>, <meta>, <link>, <base>, <style>, <body>, <form>, <input>, <isindex>, <textarea>, <select>, <optgroup>, <option>, <fieldset>, <legend>.

<button> tag is allowed only when its type attribute is set to "button".

3. All allowed tags require closing tag, even if it is not required by HTML. For example, in HTML you can write:

<p>First paragraph
<p>Second paragraph

In formula code you should closing </p> tag before opening new <p> tag:

<p>First paragraph</p>
<p>Second paragraph</p>

4. Tags that cannot have inner content, such as <img>, <br>, <hr> and <param> should be self-closed, e.g. written as <br/> or <img src="image.gif"/>

Next: Reference Columns