## Comments

11 comments

• 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

• Thank you very much Kent!

I think we are almost there. However when i tried your suggestion, I received the below:

Company A= \$NaN
Company B = \$NaN
Company C = \$NaN
Company D = \$NaN
Company E = \$NaN

What does it mean?

• I think it means that the cost value is empty.  can you please check by doing the following

on the last line comment out summary e.g.

//summary

then add a new line

@Sum of Cost Per Participant

and tell me what the output is

• wow.. it worked after i put "sum += cost" instead of "sum += cost[i]"

Strange, no?

• Indeed that is Strange.

Are you confident the data is accurate?

Can you send me a screen capture of your code

• Yes, the data is accurate. I tried it on different records with different inputs

I put @Sum of Cost Per Participant but podio did it find it

Here is the screen capture • 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 :)

click here to learn about arrays

• Thanks a lot Kent. I am not an advanced Java programmer but i know few stuff. It always feels good when you learn something you, especially from an expert like you.

Yes... it is working :)

• 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") + totalRow`

Rainer

Please sign in to leave a comment.