Calculating Time Past Since a Date With Actions

Follow

James Bowie

• March 28, 2015 04:57

I'm in the real estate business and trying to use Globiflow to help us with calculating how long a house has been on the market - expressed by calculation field "Days on Market" or "DOM". Once DOM hits 30 days, I want to be able to have the DOM field turn red, or have a big "!" icon to show it's overdue, and also create a notification of some sort to us that we need to look at it.

Secondly, once the field "Status" changes from "Listed" to "Escrow" I would like the DOM field to stop counting. So if it took 60 days from "List Date" to "Escrow Date" the DOM field should stop and remain at 60.

The 3 fields we have to build this equation are: List Date, Escrow Date, and a category field where status would change from "Listed" to "Escrow".

My experience with javascript is extremely limited, so if anyone can offer help on how I would do this using GlobiFlow and Podio I would be most grateful!

(Then I also need to do the same thing in reverse for counting down days until construction is supposed to be completed)

0

• 

  Rainer Grabowski (FVM)

  • March 29, 2015 12:54

  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.