Calculated Percentage = 100% or can not save

[Eric Dickerson]

I have 3 cost allocation fields; East, West & USA that are %numbers. I have a “Cost Allocation Total field” that is a calculated field that is =[East]+[West]+[USA] as a %number.

The cost allocation must equal 100% for the form (new or edit) to be saved.

Ideas for accomplishing this?

I have tried a few ideas from this forum and I got close with:

var qRes = spjs.utility.getItemByID(
{
“listName”:_spPageContextInfo.pageListId,
“id”:spjs.dffs.data.thisItemID,
“viewFields”:[“Cost_x0020_Allocation_x0020_TL”]
}
);
$(“span.CATL”).html(qRes[“Cost_x0020_Allocation_x0020_TL”].split(“;#”)[1]);

To get the calculated field as a heading… except that is not updated with field change… ie; if I change the % in East… there is no live update.

Is there no rule setting that will allow calculation like a calculated field? This would be better than a SP calculated column! I tried {East}+{West}+{USA} and all kinds of variations like [={East}+{West}+{USA}], etc… all to no results. I know you can add days to a date col. with [today]+5, etc. Being able to do simple math via rule would be fantastic!

Any direction on how to get this as a reality would be much appreciated… I can prevent the saving no problem once I have the East West USA fields adding up on field change.

[Alexander Bautz]

You can use this as a starting point (add to Custom JS):

function customCalculate(){
    $(".custom_calc_err").remove();
    var c1, c2, c3, tot, min = 100, max = 100;
    c1 = getFieldValue("Cost1");
    c2 = getFieldValue("Cost2");
    c3 = getFieldValue("Cost3");
    // verify number input
    c1 = isNaN(Number(c1)) ? 0 : Number(c1);
    c2 = isNaN(Number(c2)) ? 0 : Number(c2);
    c3 = isNaN(Number(c3)) ? 0 : Number(c3);
    tot = c1+c2+c3;
    if(tot < min || tot > max){
        spjs.dffs.toggleSaveBtnByRule(false);
        $("#dffs_Cost1 td:last, #dffs_Cost2 td:last, #dffs_Cost3 td:last").append("<div class='custom_calc_err' style='color:red;'>The sum of Cost1, Cost2 and Cost3 is "+tot+". This is out of range (min="+min+" and max="+max+")</div>");
    }else{
        spjs.dffs.toggleSaveBtnByRule(true);
    }
}

// Trigger on change
$("#dffs_Cost1 input, #dffs_Cost2 input, #dffs_Cost3 input").on("change",function(){
    customCalculate();
});

// Trigger on load
customCalculate();

Change “Cost1”, “Cost2” and “Cost3” to match your fields.

Let me know how this works out.

Alexander

• This reply was modified 7 years, 5 months ago by Alexander Bautz. Reason: Moved the error message so it won't interfere with the % sign

[Eric Dickerson]

This is FANTASTIC!

2 minor tweaks if I may impose…

1) Can the sum live update into a field please? field internal name: Cost_x0020_Allocation_x0020_TL

2) Can the Red text only appear under the Sum field in #1 above

Here is the modified js I am using:

function customCalculate(){
$(“.custom_calc_err”).remove();
var c1, c2, c3, tot, min = 100, max = 100;
c1 = getFieldValue(“East”);
c2 = getFieldValue(“West”);
c3 = getFieldValue(“USA”);
// verify number input
c1 = isNaN(Number(c1)) ? 0 : Number(c1);
c2 = isNaN(Number(c2)) ? 0 : Number(c2);
c3 = isNaN(Number(c3)) ? 0 : Number(c3);
tot = c1+c2+c3;
if(tot < min || tot > max){
spjs.dffs.toggleSaveBtnByRule(false);
$(“#dffs_East td:last, #dffs_West td:last, #dffs_USA td:last”).append(“<div class=’custom_calc_err’ style=’color:red;’>The sum of East, West and USA is “+tot+”. This is out of range (min=”+min+” and max=”+max+”)</div>”);
}else{
spjs.dffs.toggleSaveBtnByRule(true);
}
}

// Trigger on change
$(“#dffs_East input, #dffs_West input, #dffs_USA input”).on(“change”,function(){
customCalculate();
});

// Trigger on load
customCalculate();

[Alexander Bautz]

Here is an updated code snippet:

function customCalculate(){
    $(".custom_calc_err").remove();
    var c1, c2, c3, tot, min = 100, max = 100;
    c1 = getFieldValue("East");
    c2 = getFieldValue("West");
    c3 = getFieldValue("USA");
    // verify number input
    c1 = isNaN(Number(c1)) ? 0 : Number(c1);
    c2 = isNaN(Number(c2)) ? 0 : Number(c2);
    c3 = isNaN(Number(c3)) ? 0 : Number(c3);
    tot = c1+c2+c3;
    setFieldValue("Cost_x0020_Allocation_x0020_TL",tot);
    if(tot < min || tot > max){
        spjs.dffs.toggleSaveBtnByRule(false);
        $("#dffs_Cost_x0020_Allocation_x0020_TL td:last").append("<div class='custom_calc_err' style='color:red;'>The sum of East, West and USA is "+tot+". This is out of range (min="+min+" and max="+max+")</div>");
    }else{
        spjs.dffs.toggleSaveBtnByRule(true);
    }
}

// Trigger on keyup
$("#dffs_East input, #dffs_West input, #dffs_USA input").on("keyup",function(){
    customCalculate();
});

// Trigger on load
customCalculate();

PS: When creating fields in a list, enter the name without spaces – save it, and then change the display name to the proper value with spaces. This way your fieldinternalnames will be nicer.

Thanks for the “beer” and best regards,
Alexander

[Eric Dickerson]

That works GREAT!!

nice tip on the field names… if only I got to create them from the start most of the time… still a great idea!!

[Eric Dickerson]

I have a few issues coming up on this specific code, and a few that are loosely related:

1) the sum calculation is not working on the display form using the same code above… do I need a modified version for the display form? Or should I have a “display allocation TL” column that is getting it’s value from the “Cost_x0020_Allocation_x0020_TL”, then replace the Cost Allocation TL field on the display form with the Display allocation TL field?

2) I notice that when fields are set as read only in DFFS rules that their number formatting and descriptions are removed. For example, I have a currency field that displays as: $4,000.00 with a description that says “Sum of X & Y”. When this field is set as read only on the edit or display form the description is removed and the number shows as 4000.00. Anyway to keep the description and the formatting in read only?

Thanks as always!!

[Alexander Bautz]

Hi,
1: The function “setFieldValue” wont work in DispForm. I’m not sure what you are trying to do, but using a standard calculated column will most likely be the easiest in DispForm.

2: When setting the field as readonly you have an option in the “Misc” tab to show field description. Also, the currency symbol is only available in DispForm – and there is not need to set a field as readonly in DispForm?

Which version of SharePoint are you using in this form, and which version of SharePoint are you using?

Alexander

[Author]

Posts