Sum up items in a calculated field
Hi all,
I created a calculation field where i have calculated the fees that each organization in my group need to pay for the training department. I extracted the organization filed from the contacts who participated in trainings. The outcome of the calculation looks something like this:
Company A $60
Company A $60
Company B $60
Company C $60
Company C $60
Company D $60
Company D $60
Company E $60
Company C $60
Company A $60
Is there a way where i can sum up the fees of the same company? Please note that i am using the below java script:
var organiz = @All of Organisation
var cost = @All of Cost Per Participant
var total = ""
for(var i = 0; i < organiz.length; i++) {
total += organiz[i] + " $" + cost + "\n"
};
Any help is highly appreciated.
Regards,
Fady
-
Try This Fady
note that for each entry in @all of organisation there needs to be an entry in @all of cost per participant even if it is $0
var oraniz = @All of Organisation;
var organizsort = organiz.sort(); //sort organiz to help get unique
var cost = @All of Cost Per Participant;
var txt = "";
var unique = []; //create new array of unique organisations
var summary = "";
for (var i = 0; i < oraniz.length; i++){ //for loop to create new unique array
if (organizsort[i] != txt){
txt = organizsort[i];
unique.push(txt); //create new array of unique organisations
}};for (var j = 0; j < unique.length; j++){ //loop for each unique organisation
var sum = 0;
for (var i = 0; i < oraniz.length; i++){ //loop to compare unique organisation against all organisations
if (oraniz[i] == unique[j]){
sum += cost[i] //when match is found add the cost
}};
summary += unique[j] + " = $" + sum + "\n"; //compile output with each unique company on a new line
};
summary -
Try This Fady
note that for each entry in @all of organisation there needs to be an entry in @all of cost per participant even if it is $0
var organiz = @All of Organisation;
var organizsort = organiz.sort(); //sort organiz to help get unique
var cost = @All of Cost Per Participant;
var txt = "";
var unique = []; //create new array of unique organisations
var summary = "";
for (var i = 0; i < organiz.length; i++){ //for loop to create new unique array
if (organizsort[i] != txt){
txt = organizsort[i];
unique.push(txt); //create new array of unique organisations
}};for (var j = 0; j < unique.length; j++){ //loop for each unique organisation
var sum = 0;
for (var i = 0; i < organiz.length; i++){ //loop to compare unique organisation against all organisations
if (organiz[i] == unique[j]){
sum += cost[i] //when match is found add the cost
}};
summary += unique[j] + " = $" + sum + "\n"; //compile output with each unique company on a new line
};
summary -
Ah OK I get why you don't need cost[i] in your formula. It is because cost is stored in the current app not the related app
I can tell this because you called up var cost = @Cost per Participant which is coming from the home app
if you were to call it up from var cost = @All of Cost per Participant it would be coming from the related app and hence an array
This also explains why the @Sum of Cost per Participant didn't work. this will only work coming from a related app
basically information from related apps is stored as a javascript array and the cost[i] calls the value from position "i" in the array. Since the information is stored in the home app there is no array and happy days. With this in mind you could probably make the formula simpler, but if it works happy days :)
-
Similar situation but adapting the code above is returning incorrect values for me. I have a payroll period app that is linked to timesheets based on date, and would like to show on the pay period record a summary of the number of hours worked (from timesheet app field "Hours Worked") per employee (from timesheet app field "Employee") for that period.
I tried this, based on the above solution, but the calculated numbers end up wrong in unpredictable ways (some over, some under). The total # hours ends up correct, but the totals per employee are wrong. Thoughts on where I'm going wrong?
var emp = @All of Employee;
var empsort = emp.sort();
var hours = @All of Hours Worked;
var txt = "";
var unique = [];
var summary = "";
for (var i = 0; i < emp.length; i++){
if (empsort[i] != txt){
txt = empsort[i];
unique.push(txt);
}};for (var j = 0; j < unique.length; j++){
var sum = 0;
for (var i = 0; i < emp.length; i++){
if (emp[i] == unique[j]){
sum += hours[i]
}};
summary += unique[j] + " - " + sum + " hours "+ "\n";
};
summary -
Hello Melon,
your uniques are not unique.var emp = @All of Employee;
var empsort = emp.sort();
var hours = @All of Hours Worked;
var unique = empsort.filter(function(item, pos) {
return empsort.indexOf(item) == pos;
});
var rows = [];
var totalHrs = 0;
for (var j = 0; j < unique.length; j++){
var empHrs = 0;
for (var i = 0; i < emp.length; i++){
if (emp[i] == unique[j]){
empHrs += hours[i] || 0;
}
};
totalHrs += empHrs;
rows.push(unique[j] + " - " + empHrs + " hours");
};
var totalRow = "\n**TOTAL - " + totalHrs + "hours**";
rows.join("\n") + totalRowRainer
-
Hi Rainer, tried a few things and followed your support to many guys... I think I did the right thing, but dont know why the sum function below is only considering the last item instead of adding base in the condition.
// Crea tabla de campos relacionados, con 1ra columna con link
var id = @All of simple id;
var nombre_oc = @All of Nombre OC;
var fecha = @All of Fecha OC;
var valor_clp = @All of Valor en CLP;
var st_oc = @All of Status OC;
var st_pago = @All of Status de Pago with nulls;
var url = "https://podio.com/b-agrocom/02-de-compra-a-factura/apps/orden-compra/items/"
var lines = [];
for(var i = 0; i < id.length;i++){
var sum = 0;
if(st_oc[i] == "Factura Creada" ){
sum += Number(valor_clp[i]);
}
var d = fecha[i];
var gmt = moment(d).tz("America/Santiago").format("ZZ");
var gmtDiff = parseFloat(gmt)/100;
var gmtDiff = Number(String(gmtDiff).replace(".3",".5"));
var timeStamp0000 = moment(d).format("HH:mm") == "00:00" ? moment(d) : moment(d).add(gmtDiff,"h");
var timeStampOwnTZ = timeStamp0000.toString().split("GMT").shift() + "GMT " + gmt;
var asStringFormatted = moment(timeStamp0000).format("DD.MM.YYYY HH:mm");
var asStringFormatted_noTime = moment(timeStamp0000).format("DD.MM.YYYY");
var asDate = moment(timeStampOwnTZ).toDate();
lines.push(
"[" + id[i] + "](" + url + id[i] + ")"
+ " | " + nombre_oc[i]
+ " | " + asStringFormatted_noTime
+ " | " + st_oc[i]
+ " | " + st_pago[i][0]
+ " | " + valor_clp[i].toFixed(0).replace(/\B(?=(\d{3})+(?!\d))/g, ","));
};
"Id | OC | Fecha Mov | St OC | St Pago | Valor \n" +
"--- | --- | --- | --- | --- | ---: \n"+
lines.join("\n") +
"\n | | | **Total OCs** | | **" + sum.toFixed(0).replace(/\B(?=(\d{3})+(?!\d))/g, ",") + "**\n"
Please sign in to leave a comment.
Comments
14 comments