Hi Carson,

this one should work:

var cat = @all of your category field;
var time = @all of your time field;
var sum = 0;
for(var i = 0; i < cat.length; i++){
if(cat[i] = "Billable"){
sum += time[i];
}};
sum

Rainer

Rainer - as always you are a genius. Thank you.

That code worked, with one caveat...

Items that are marked as Billable Do get calculated.

Items that are left blank on that category field do Not get calculated.

However, items marked as "Not Billable" on that category field Do still get calculated.

I assume it would be a slight change to say - Calculate those marked as Billable, don't calculate anything else (empty or 'Not Billable')

Any ideas what I have missed there?

Hi Carson,

@all of field returns the items as an array. The items are the elements in the array. Element [0] (=the first cause JS starts with 0, not with 1)) is always the oldest item. In the for-loop the [i] stands for the element number.

But Podio gets only those items which have a value. So if you have 3 items:
Item 1: Billable - 10h
item 2: (no cat value) - 20h  (no value = null)
item 3: Not Billable - 30h
item 4: Billable - 40h

The number of Elements in var cat is 3 (= length), but in var time it's 4. That means
Cat-Element [0]  = Billable, Time-Element [0] = 10h
Cat-Element [1] = Not Billable - Time-Element [1] = 20h  (wrong time value).
Cat-element [2] = Billable - Time-Element [2] = 30h (wrong time value)
Cat-Element [3] - doesn't exist - Time-Element [3] = 40h

Image that as a table with 4 rows. Cause the cell in row 2 Column "Category" is empty, the values from row 3 and 4 in this column jump one cell up, but the values in column "Time" stay

When you now do the for-loop it runs only 3 times cause cat has only 3 elements. It starts with i = 0 and looks if Cat-Element [0] = billable . It is equal so it adds time-element [0] = 10h (that's ok); the next loop iteration for Cat-Element[1] does nothing, for cat[2] it adds 30h  - but's that's wrong cause in the third item "Not Billable" is selected.

You expect a result = 50, but you get 40. The issue is, that you can't see at the first look, if it calculates correct or not. If not item 2 doesn't have a cat value but item 4, you would get the right result.

To avoid this problem some weeks ago Podio introduces new value tokens: @all of field with nulls. If you use these tokens you can handle that issue. But they are bit more complicated to use cause they are returned as nested arrays.

var cat = @all of your category field with nulls;
var time = @all of your time field with nulls;
var sumBillable = 0;
for(var i = 0; i < cat.length; i++){
if(cat[i][0] == "Billable"){
sumBillable += time[i][0] || 0;
}};
sumBillable

// Not Billable
var sumNotBillable = 0;
for(var i = 0; i < cat.length; i++){
if(cat[i][0] == "Not Billable"){
sumNotBillable += time[i][0] || 0;
}};
sumNotBillable
// Empty
var sumEmpty = 0;
for(var i = 0; i < cat.length; i++){
if(cat[i][0] == null ){
sumEmpty += time[i][0] || 0;
}};
sumEmpty

Btw: There's been a typo in my first calculation. Must be cat[i] == "Billable" (double equal signs):

Rainer

Thank you so much Rainer. I truly appreciate the detailed explanation, and even taking the time to write it out so a non-coder like myself can understand it.

you rock!

Hi Rainer and community!

I was asking... what if you want to calculate with two categories selected instead of only one?

Woud this be correct?

It seams to work but i haven´t try it yet...

var cat = @all of your category field with nulls;
var time = @all of your time field with nulls;
var OtherCat = @all of your OtherCat field with nulls;
var sumBillable = 0;
for(var i = 0; i < cat.length; i++){
if(cat[i][0] == "Billable" && OtherCat[i][0] == "Category Name"){
sum += time[i][0] || 0;
}};
sumBillable

Best regards!

Hi Marcos,

seems to be correct except this line:

sum += time[i][0] || 0;

must be:

sumBillable += time[i][0] || 0;

Thanks!

@Rainer - I hate to bug, but I have another calculation scenario that is fairly similar to this thread.

The situation is: a time entry app that records Time In/Time Out for various tasks. Then I have a calculated field which provides elapsed time (eg. 2.5 hours) and a Relationship field to an employee listed in a separate Admin workspace.

Back in that Admin space, on the Employee Item, I would like to show a calculated field of Total Elapsed time logged by them during the current month.

I can easily get the total elapsed time, but not sure how to make it display only the current month's hours. Is this something you (or anyone) can shed light on?