0

I'm wondering if anyone can help me with a calculate column/JS Link formula.

I have a monthly performance indicator which is a count of the number of inspections I perform. There is a target number per month (same for each month).

I have a custom list that shows the count per month and I've created a calculated column that shows the percentage of the target achieved. I wws hoping to color code the font like so:

Actual count is equal to or greater than target - font is green

Actual count is between 75% and 100% of target - font is orange

Actual count is less than 50% of target - font is red

As shown below July to Sep would be red, Oct to Nov orange and December green.

JUL  AUG  SEP  OCT  NOV  DEC  Target
0    10   50   75   80   100   100

I don't have SharePoint Designer.

Thank you!

1

Preferably you do not hardcode Fieldnames in your CSR code, so you can re-use your code or make it into a generic function

This is more generic and easier to maintain:

var name = ctx.CurrentFieldSchema.Name;
var value = ctx.CurrentItem[name];
var percentage = value / 100;
var color = 'red';
if (percentage >= .5) {
    color = 'orange';
} else if (percentage === 1) {
    color = 'green';
}
return String.format("<span style='color:{0}'>{1:0}</span>", color, value);
0

Here is a CSR script:

SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
    function getBaseHtml(ctx) {
        return SPClientTemplates["_defaultTemplates"].Fields.default.all.all[ctx.CurrentFieldSchema.FieldType][ctx.BaseViewID](ctx);
    }
    function init() {
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
            Templates: {
                Fields: {
                    "PercentageOfTargetAchieved": {
                        View: function(ctx) {
                            var percentage = ctx.CurrentItem["Inspections"] / 100;
                            switch (true) {
                                case (percentage >= 1):
                                    return "<span style='color: green'>" + ctx.CurrentItem["PercentageOfTargetAchieved"];
                                    break;
                                case (percentage >= .5 && percentage < 1):
                                    return "<span style='color: orange'>" + ctx.CurrentItem["PercentageOfTargetAchieved"];
                                    break;
                                case (percentage < .5):
                                    return "<span style='color: red'>" + ctx.CurrentItem["PercentageOfTargetAchieved"];
                                    break;
                                default:
                                    return ctx.CurrentItem["PercentageOfTargetAchieved"];
                            }
                        },
                    }
                },
            },
            ListTemplateType: 100
        });
    }
    RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~siteCollection/Style Library/AchievementColorCoding.js"), init);
    init();
});

I don't know the name of your columns so you might have to adjust the corresponding parts of the code. Also, the conditions you have mentioned don't cover the percentage range of >50% && <75%. I think that's a mistake so the script has the ranges

Red:    <50%
Orange: >=50% && <100%
Green:  >=100%

UPDATE

After reading your comments, here is some updated code. Assuming that you have the columns JL, JL% (internal name: JL_x0025_) and Target:

SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
    function getBaseHtml(ctx) {
        return SPClientTemplates["_defaultTemplates"].Fields.default.all.all[ctx.CurrentFieldSchema.FieldType][ctx.BaseViewID](ctx);
    }
    function init() {
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
            Templates: {
                Fields: {
                    "JL_x0025_": {
                        View: function(ctx) {
                            var percentage = ctx.CurrentItem["JL"] / ctx.CurrentItem["Target"]
                            switch (true) {
                                case (percentage >= 1):
                                    return "<span style='color: green'>"  + ctx.CurrentItem["JL_x0025_"];
                                    break;
                                case (percentage >= .5 && percentage < 1):
                                    return "<span style='color: orange'>" + ctx.CurrentItem["JL_x0025_"];
                                    break;
                                case (percentage < .5):
                                    return "<span style='color: red'>"    + ctx.CurrentItem["JL_x0025_"];
                                    break;
                                default:
                                    return ctx.CurrentItem["JL_x0025_"];
                            }
                        },
                    }
                },
            },
            ListTemplateType: 100
        });
    }
    RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~siteCollectio/Style Library/AchievementColorCoding.js"), init);
    init();
});

Screenshot:

enter image description here

You'll have to adjust the path in the RegisterModuleInit-function if you want it to work with activated Minimal Download Strategy.

  • Thank you very much Daniel. Unfortunately I cant get the js file to fire. This is the first time I've tried using the JS link function. – Brice235i Jul 24 '16 at 4:49
  • Create a new file called 'AchiementColorCoding.js'. Copy the CSR code into it. Then upload the file to '~siteCollection/Style Library/' (don't forget to check-in/publish [if required]). Go to your View, edit the page, edit the List View Web Part, put '~siteCollection/Style Library/AchiementColorCoding.js' into the JS Link property. Save the page and then it should work. – Daniel Stölzner Jul 24 '16 at 4:52
  • Ah, and as I said, you have to correct the column names. I assumed "Inspections" and "PercentageOfTargetAchieved". You'll have to change them to the correct names of the columns in your list. – Daniel Stölzner Jul 24 '16 at 4:54
  • I don't have access to the site collection, so I'm just uploading to the Site master pages and Page Layouts directory. With the columns, there is an inspection count for each month. the columns are JL, AU, SE, OC, NO, DE. Then there is a target column. I've created a calculated column for each months JL%, AU%, SE% and so on. This column is =(JL/Target). I guess I would just copy and paste the code for each month? – Brice235i Jul 24 '16 at 5:00
  • I tried editing the code, but I can't paste in here as it's too long. – Brice235i Jul 24 '16 at 5:01
-1
    SP.SOD.executeFunc("clienttemplates.js", "SPClientTemplates", function() {
    function getBaseHtml(ctx) {
        return SPClientTemplates["_defaultTemplates"].Fields.default.all.all[ctx.CurrentFieldSchema.FieldType][ctx.BaseViewID](ctx);
    }
    function init() {
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides({
            Templates: {
                Fields: {
                    "JL%": {
                        View: function(ctx) {
                            var percentage = ctx.CurrentItem["JL"] / 100;
                            switch (true) {
                                case (percentage >= 1):
                                    return "<span style='color: green'>" + ctx.CurrentItem["JL%"];
                                    break;
                                case (percentage >= .5 && percentage < 1):
                                    return "<span style='color: orange'>" + ctx.CurrentItem["JL%"];
                                    break;
                                case (percentage < .5):
                                    return "<span style='color: red'>" + ctx.CurrentItem["JL%"];
                                    break;
                                default:
                                    return ctx.CurrentItem["JL%"];
                            }
                        },
                    }
                },
            },
            ListTemplateType: 100
        });
    }
    RegisterModuleInit(SPClientTemplates.Utility.ReplaceUrlTokens("~siteCollection/Style Library/AchiementColorCoding.js"), init);
    init();
});

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.