13

[multiple blogs/posts out there noted the issue, no solution was ever published]

What is required to save a Localized Calculated Column Formula using JSOM/REST?

I am injecting the script below into a FldEdit.aspx page.

  • It saves the Formula you are editting on every keypress
  • providing immediate feedback on errors

This al works fine on English sites but on non-english sites the Formula is displayed in a Localized format

But I can NOT save the Localized format, using REST or JSOM code, the Formula is not accepted.

This screenshots shows the Dutch Localized UI,
not making any changes to the Formula, my custom save action is rejected (red error)

If I change the Formula (in that Localized Dutch site!) to English notation I can save the Formula using JSOM or REST.

It IS stored. a reload of the FldEdit.aspx page displays it in dutch again.

A click on the standard OK button saves the Localized Formula without any issues.
If I change the Formula to English Locale and use the SharePoint OK Button to save/close the page I (ofcourse) get an error.

?? What is required to save a Localized Calculated Column Formula using JSOM/REST?

.

Scriptfile

execute in an existing Formula (FldEdit.aspx page)
as Chrome snippet or as a Bookmarklet or as a GreaseMonkey/TamperMonkey script or stick it into your MasterPage.

/*global SP,document,STSHtmlDecode,clearTimeout,setTimeout,_spPageContextInfo*/
SP.SOD.executeFunc('sp.js', 'SP.ClientContext',
    function () {
        var textarea = document.getElementById('onetidIODefTextValue1'),
            status = function (text, color) {
                SP.UI.Status.removeAllStatus(true);
                SP.UI.Status.setStatusPriColor(SP.UI.Status.addStatus(text), color);
            };
        status('CalcMaster loaded', 'yellow');
        textarea.onkeyup = function (event) {
            if ([37, 38, 39, 40].indexOf(event.keyCode) < 0) { //ignore arrow keys
                clearTimeout(typing);
                var typing = setTimeout(function () {
                    var guid = _spPageContextInfo.pageListId.slice(1, 37),
                        title = document.getElementById('idColName').value,
                        formula = STSHtmlDecode(textarea.value),
                        ctx = SP.ClientContext.get_current(),
                        fields = ctx.get_web().get_lists().getById(guid).get_fields(),
                        field = ctx.castTo(fields.getByInternalNameOrTitle(title), SP.FieldCalculated);
                    //formula = ('=IF(TRUE,1,2)');
                    field.set_formula(formula);
                    field.update();
                    status('Saving: ' + title, 'blue');
                    ctx.executeQueryAsync(function () {
                        status('Formula saved!', 'green');
                    }, function (sender, args) {
                        status(args.get_message(), 'red');
                    });
                }, 500); //debounce, do not save while typing
            }
        };
    }
);

Additional info for 200-point Bounty hunters

You can extract the English formula using JSOM from the schemaXml

        var schemaXml=document.createElement('DIV');
        schemaXml.innerHTML=field.get_schemaXml();
        schemaXml=schemaXml.firstChild;
        var formula=schemaXml.getElementsByTagName('Formula')[0].innerText;

BUT

  • This formula returns the Internal (and english) names for Columnnames, so saving it causes an error because it does not match the Displaynames (eg. Taskname/Title)
  • The , or ; separator also needs to be tackled

iJS

  • Without any code, can you save your formula using the dutch columns ? Because me, in french, I get the message saying the column doesn't exists. Looks like the formula needs to be created in the base language of the site. – JayHell Sep 18 '15 at 16:54
  • Yes, JayHell, The OK button (normal SharePoint behaviour) works just fine. And yes, thomius, your link describes the same issue, yet without a solution. – Danny '365CSI' Engelman Sep 18 '15 at 21:47
  • There's also FormulaDisplayNames available (exposed at least via web services) but it didn't help solve the issue I think. – thomius Sep 19 '15 at 11:20
3
+200

Sadly a lot of things don't work as expected in CSOM. As a last resort I have a small hack - simply open the desired page as iframe, modify the content and submit the page. Quite slow but at least it works.

//quite dirty hack but at this point the only possibility to set the localized formula via clientside
function setFieldFormula(listGuid, fieldInternalName, newFormula, cb) {
    var iframe = document.createElement('iframe');
    var url = _spPageContextInfo.webAbsoluteUrl + '/_layouts/15/FldEdit.aspx?List=%7B' + listGuid+ '%7D&Field=' + fieldInternalName;
    //create hidden iframe
    iframe.setAttribute('src', url);
    iframe.setAttribute('style', 'display:none');
    iframe.onload = function () {
        //this will fire 2 times - the first time when its initially loaded. the second after the changes were applied (page is reloaded (postback) after the submitbutton is triggered)
        var formularInput = iframe.contentWindow.document.querySelector('.ms-formula');
        if (formularInput) {
            //initial load - apply settings
            formularInput.value  = newFormula;
            //trigger the post
            iframe.contentWindow.SubmitPage(false)
        } else {
            //second load - do callback
            cb && cb();
        }
    };
    document.body.appendChild(iframe);
}

setFieldFormula(_spPageContextInfo.pageListId.slice(1, 37), 'Berechnet', '=WAHR', function(){  //=WAHR is german for =TRUE and Berechnet is the title of my calculated column
    console.log('Done')
});

Due to the livesync in your code this is not applicable for your exact usecase but maybe you can replace the livesync with a triggered save (by a button for example).

If someone want's to discuss if this is a valid way of doing things - I've already asked a question for this:

Manipulating SharePoint UI over an IFrame to add missing clientside Features

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.