Calculate only if Referenced Item has specific category selected

Comments

8 comments

  • Rainer Grabowski

    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

    1
    Comment actions Permalink
  • Rainer Grabowski

    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

    0
    Comment actions Permalink
  • Carson Young - Red Cliff Labs

    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?

    0
    Comment actions Permalink
  • Carson Young - Red Cliff Labs

    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!

    0
    Comment actions Permalink
  • Marcos Adlercreutz

    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!

     

    0
    Comment actions Permalink
  • Rainer Grabowski

    Hi Marcos,

    seems to be correct except this line:

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

    must be:

    sumBillable += time[i][0] || 0;
    0
    Comment actions Permalink
  • Marcos Adlercreutz

    That´s right! my bad

    Thanks!

     

    0
    Comment actions Permalink
  • Carson Young - Red Cliff Labs

    @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? 

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk