Calculate only if Referenced Item has specific category selected
I am working on a 'complex' calculation that I am sure is possible, but I don't have java background to make it happen.
In a Projects App I have a "Log Time" workflow that creates an item in a related Time Tracker App.
In the Time Tracker App there is a category field for Billable or Not Billable.
Back in my Projects App I need a calculation field showing the accrued amount of Billable Time logged.
It is super easy to show ALL time logged, but how can I say only add the value from Time Tracker App IF Billable=True?
I found this thread that seems somewhat similar, but haven't solved it yet for my need - https://help.podio.com/hc/en-us/community/posts/202572798-Calculation-based-on-choices-in-a-category-or-by-outgoing-related-items
Thanks in Advance!
-
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 - 40hThe 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] = 40hImage 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;
}};
sumEmptyBtw: There's been a typo in my first calculation. Must be cat[i] == "Billable" (double equal signs):
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 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! -
@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?
Please sign in to leave a comment.
Comments
8 comments