Multilingual text in a calculated column

Have you ever wanted to have the text in a calculated column translated according to the selected language in a SharePoint sites multilingual user interface?

Lets say you want to have a task list view grouped by the status of the tasks, but the statuses are in Norwegian: Ny, Under behandling and Fullført. For an English, or German speaking person these groups will not make much sense:
IMG

How about having it like this:
IMG

Or like this:
IMG

This is achieved by using a bug(?) in SharePoints handling of calculated fields that return number. We use a HTML image tag with an onload attibute to call a function.

Add a this code to the TOP of the page – ABOVE the list view:

<div id="translateCalcColumn_findAndHide" style="display:none;">This is a dummy used to remove white space above list view</div>
<script type="text/javascript" src="/Scripts/jquery-1.10.2.min.js"></script>
<script type="text/javascript">
// Remove whitespace from top of list view
$("#translateCalcColumn_findAndHide").parents("td.s4-wpcell-plain").parent().hide();
	
var spjs_translateFilterDisabled = {};	
function spjs_translate(elm){
	var $elm, oVal, a, id, o, l, td, s, v1, v2, v3;
	$elm = $(elm);
	oVal = $elm.attr("val");		
	a = $elm.attr("arr").split("#");
	id = $elm.attr("fin");
	o = {}
	$.each(a,function(i,v1){
		s = v1.split("|");
		$.each(s[1].split(","),function(j,v2){
			if(o[s[0]] === undefined){
				o[s[0]] = {};
			}
			v3 = v2.split(":");
			o[s[0]][v3[0]] = v3[1];	
		});			
	});
	if(o[oVal] !== undefined && o[oVal][g_wsaLCID] !== undefined){
		l = o[oVal][g_wsaLCID];
	}else{
		l = "<span title='Missing translation for LCID "+g_wsaLCID+"' style='color:red;cursor:no-drop;'>"+oVal+"</span>";
	}
	td = $elm.parents("td:first");
	if(spjs_translateFilterDisabled[id] === undefined && $("table.ms-listviewtable").length > 0){
		cIndex = td[0].cellIndex;
		if(!$(td).hasClass("ms-gb") && !$(td).hasClass("ms-gb2")){				
			spjs_translateFilterDisabled[id] = true;
			th = $elm.parents("table.ms-listviewtable").find("th:eq("+cIndex+") div.ms-vh-div");
			th.attr("FilterDisable","TRUE").html(th.text());
		}
	}
	if($(td).attr("class").match("ms-gb") === null){
		$(td).html(l);
	}else{
		$elm.replaceWith(l);
	}
}
</script>

And then add a calculated column to the list using this format:

="<img src='/_layouts/images/loadingcirclests16.gif' onload='spjs_translate(this)' val='"&Status&"' fin='TranslatedStatus' arr='Ny|1033:New,1031:Neue#Under behandling|1033:In Progress,1031:In Bearbeitung#Fullført|1033:Completed,1031:Fertiggestellt'>"

The red text indicates that the original value from the field “Status” is pulled in to the attribute “val” of the image. The green text is the FieldInternalName of the field, but it can be any unique value (must be unique when using multiple translatable calculated columns in one view). The blue text is the translated values in this format:
[The original value option 1]|LCID1:translated text,LCID2:translated text#[The original value option 2]|LCID1:translated text,LCID2:translated text

Please note that for this trick to work, you must set “The data type returned from this formula is: Number (1, 1.0, 100)” in the calculated column.

PS: this trick can be used in DispForm as well, but you must use this “init_fields” function to convert the HTML formatted text into proper HTML as the bug(?) only applies to list views.

function init_fields_v2(){
	var res = {};
	$("td.ms-formbody").each(function(){
	var myMatch = $(this).html().match(/FieldName="(.+)"\s+FieldInternalName="(.+)"\s+FieldType="(.+)"\s+/);	
		if(myMatch!=null){
			// Display name
			var disp = myMatch[1];
			// FieldInternalName
			var fin = myMatch[2];
			// FieldType
			var type = myMatch[3];
			if(type=='SPFieldNote'){
				if($(this).find('script').length>0){
					type=type+"_HTML";
				}else if($(this).find("div[id$='TextField_inplacerte']").length>0){
					type=type+"_EHTML";
				}				
			}
			if(type==="SPFieldLookup"){
				if($(this).find("input").length>0){
					type=type+"_Input";
				}
			}
			// HTML Calc
			if(type==='SPFieldCalculated' && $(td).text().match(/(<([^>]+)>)/ig)!==null){
				$(td).html($(td).text());
			}		
			// Build object
			res[fin] = this.parentNode;
			$(res[fin]).attr('FieldDispName',disp);
			$(res[fin]).attr('FieldType',type);
		}		
	});
	return res;
}

Let me know in the comments below if you have trouble, or you get it working.

Alexander

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.