Calculate time spent on category

Comments

11 comments

  • Rainer Grabowski

    Hi Steven,

    this is only possible with a workaround for which you need Podio Advanced Worksflows (or an extension like Globiflow) and one or several additional fields Do you want the amount of time for every status or for one only? I assume, status is a single choice category field, right? Do you use Podio Advanced Workflows?

    Rainer

    0
    Comment actions Permalink
  • Steven Burciaga

    Hi Rainer,

    Yes, a status is a single choice category field. I would like the amount of time for the current status. I do not know Advanced Workflows or Globiflow. Are they easy to work?

    Thanks!

    0
    Comment actions Permalink
  • Rainer Grabowski

    For Podio Advanced Workflows see here https://help.podio.com/hc/en-us/articles/204366037-Advanced-workflows.
    For Globiflow see here https://podio.com/extensions/2

    The problem in your case is, that a calculation field can't store the date when a new category is selecteds. Means:
    You can do: If status == "prospect" get and show current time.
    But if the status changes and the IF-condition is no more "true", than the time value vanishes.

    The workaraound would be: Create a second status field with exactly the same options but make it multi choice (put it at the bottom of your app template and name it something like [Status - for Backend use only]). Create Podio Advanced Flows which change automatically this additional status every time you change the normal status: If "prospect" selected, select "prospect" too in the additional field, if it changes to "qualified" select "qualified" too in the additional field. But cause the additional is multi choice there are both options selected ( = they are acitve).

    Now you can do a calculation like:
    var timeProspect = If additional field contains "prospect" get current time
    var timeQualified = If additional field contains "qualified" get current time

    Than you can calculate the difference:
    var AmountProspect = timeQualfied - timeProspect
    Result can be x hours/days/weeks - whatever you want.

    The code would be:

    var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : "" 
    var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" 
    
    var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
    "Prospect took " + AmountProspect + " days"
    

    In your calculation field you would see e.g.: Prospect took 5 days

    Rainer

    1
    Comment actions Permalink
  • Steven Burciaga

    Thanks!

    Do I need to do var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" for each option on the status category?

    0
    Comment actions Permalink
  • Rainer Grabowski

    You have to do it for each status you want to get the time for, if you want it for only one, you have to do it for this one and for the following one. Let's say you have 4 status options plus the option "closed" and you want the duration for all 4 options:

    var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : "" 
    var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : "" 
    var timeFact = @additional field.indexOf( "fact finding") > -1 ? moment() : "" 
    var timeQuote = @additional field.indexOf( "quotation") > -1 ? moment() : "" 
    var timeClosed = @additional field.indexOf( "closed") > -1 ? moment() : "" 
    
    var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
    var AmountQualified = timeFact != "" ? timeFact.diff(timeQualified, "days") : ""
    var AmountFact = timeQuote != "" ? timeQuote.diff(timeFact, "days") : ""
    var AmountQuote = timeClosed != "" ? timeClosed.diff(timeQuote, "days") : ""
    

    "closed" = your last status

    Rainer
    rg@delos-consulting.com

    0
    Comment actions Permalink
  • Steven Burciaga

    It is saying "The calculation field must reference at least one other field. It cannot be entirely based on static data, such as strings or numbers" Any way to fix this? Sorry for the lack of knowledge.

    0
    Comment actions Permalink
  • Rainer Grabowski

    Look here how to create calculations https://help.podio.com/hc/en-us/articles/201019358-Calculations

    As first line type:
    var status = @name of your status field

    When you type @ a dropdown list opens, click the status field and it will be inserted

    than:
    var timeProspect = status.indexOf( "prospect") > -1 ? moment() : ""
    and so on

    var stands for "variable". In the first line you declare the status field as a variable. So you can use the variable name instead of typing each time @field name.

    Rainer

    0
    Comment actions Permalink
  • Steven Burciaga

    It is coming back wit "Script syntax error: Unexpected token ILLEGAL"

    0
    Comment actions Permalink
  • Rainer Grabowski

    Maybe it's a typo : Copy and paste you calculation here.

    0
    Comment actions Permalink
  • Steven Burciaga

    var status = @Status (for backend use only)

    var timeProspect = @additional field.indexOf( "prospect") > -1 ? moment() : ""
    var timeQualified = @additional field.indexOf( "qualified") > -1 ? moment() : ""
    var timeFact = @additional field.indexOf( "fact finding/assessment") > -1 ? moment() : ""
    var timeQuote = @additional field.indexOf( "quotation") > -1 ? moment() : ""
    var timeClose = @additional field.indexOf( "closing") > -1 ? moment() : ""

    var AmountProspect = timeQualified != "" ? timeQualified.diff(timeProspect, "days") : ""
    var AmountQualified = timeFact != "" ? timeFact.diff(timeQualified, "days") : ""
    var AmountFact = timeQuote != "" ? timeQuote.diff(timeFact, "days") : ""
    var AmountQuote = timeClose != "" ? timeClose.diff(timeQuote, "days") : ""

    0
    Comment actions Permalink
  • Rainer Grabowski

    If you enter the @ you MUST select a field from the list, if you do not you'll get this error notification (also in some other cases) . "@additional field" is invalid if you would substitute "@additional field" by "status" you won't get this notification.

    BUT: I've made a very silly mistake, this calculation doesn't work. Instead of creating an additional multi choice category field, you have to create 5 additional date fields and 5 Podio advanced flows. If you want to do this I would show you how. One thing I've to know for that is, if the Status field is a required field, so that the first status option is automatically selected when the item is created.

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk