Calculating Time Past Since a Date With Actions

Comments

1 comment

  • Rainer Grabowski (FVM)

    Hi James,

    a calculation field will only be updated when one of the values of the calculation changes. So you need an additional date field "Check Date" which has to be updated daily and will be compared with "List Date". If I got you right, you don't have a category status field with "Listed" and "Escrow", but you want to show the status in the "DOM" field. And I assume, you enter the Escrow Date when a house is escrowed, till than the Escrow Date is empty.

    First step: Create the "Check date" field in your app

    Second step: Create a picture with a big red "!", store the file in your workspace and make a note of the URL which looks like: https://files.podio.com/123456789

    Third step: Create the calculation in the "DOM" field.

    SCENARIO A:
    The following calculation shows everthing in one field:

    • the status
    • days from List Date to curent date if status = Listed
    • a warning sign if 30 ore more days listed and if status = listed
    • days from List Date to Escrowed Date if status = Escrowed

    The calculation for scenario A:

    var listDate = moment(@List Date);
    var checkDate = moment(@CheckDate);
    var daysDiff1 = checkDate.diff(listDate, 'd');
    var picURL = "![warning](https://files.podio.com/123456789)";
    var escrowDate = @Escrow Date;
    var daysDiff2 = moment(escrowDate).diff(listDate, 'd');
    
    escrowDate == null && daysDiff1< 30 ? "Listed: " + daysDiff1 : escrowDate == null && daysDiff >= 30 ? "Listed: " + daysDiff + "\n" + picURL : "Escrow: " + daysDiff2; 
    

    The disadvantage of scenario A is, that you can't do further easy number calculations with DOM cause it is a text field (you could do but it would require some more javascript manipulations in the further calculation). If you want to do easy further calculations with DOM (e.g average DOM of all hours in May or in 2015 or overall etc.) you better use 3 calculation fields: DOM, STATUS, WARNING (btw: the warning signs wouldn't appear in the mobile apps cause it needs markdown syntax which isn't supported in the mobile apps; instead of a sign you would see the text+url, also not in the app views in your browser on your computer, the sign appears only in the item).

    SCENARIO B:

    STATUS calculation:

    @Escrow Date == null ? "Listed" : "Escrow"
    

    DOM calculation:

    var listDate = moment(@List Date);
    var checkDate = moment(@CheckDate);
    var daysDiff1 = checkDate.diff(listDate, 'd');
    var escrowDate = @Escrow Date;
    var daysDiff2 = moment(escrowDate).diff(listDate, 'd');
    
    escrowDate == null  ? daysDiff1 : daysDiff2; 
    

    WARNING calculation:

    @DOM >= 30 && @STATUS == "Listed" ? "![warning](https://files.podio.com/123456789)" : null
    

    Forth step:
    Create a flow in Globiflow which updates "Check Date" daily to current day and notifies you if the 30 day limit is reached:

    Trigger: By an Item's date field:
    Filter: If check field equal to current day -1

    For Scenario A:
    Action Custom Variable:
    create a new variable: Status, calc:

    current(array_slice( explode( " ",[DOM] ), 0 ))
    

    create a new variable: Days, calc:

    current(array_slice( explode( " ",[DOM] ), 1 ))
    

    Action Sanity Check: [(prep field) Status] == "Listed:"
    Action Update this item in field Check Date with value "current date"
    Action Sanity Check: [(prep field) Days] == "29"
    Action Send E-Mail, Add comment, Send Message or Add task (by whatever you want to be notified that the limit of 30 days is reached)

    For Scenario B:
    Action Sanity Check: [status] == "Listed"
    Action Update this item in field Check Date with value "current date"
    Action Sanity Check: [DOM] == "29"
    Action Send E-Mail, Add comment, Send Message or Add task (by whatever you want to be notified that the limit of 30 days is reached)

    Rainer
    rg@delos-consulting.com

    0
    Comment actions Permalink

Please sign in to leave a comment.

Powered by Zendesk