How to calculate days past due?
My question has two parts.
First, how to calculate the total due for a tenant that is past due on their rent? The equation should look like this: "Total due" = "Rent" + "Late Fee" + "Late Fee Per Diem" x (Day of month(today) - (Day Due). Where Day of month(today) would return 4 (for the 4th of April - for example) and (Day Due) is an integer field in my item and is between 1 and 31 for the day of the month the payment is due.
Second, I would like to automatically send an email (using GlobiFlow) to be triggered when today's date is greater than the grace period. So for example, if the rent is due on the 1st, and the grace period is 3 days, then an email would automatically be sent.
Thanks!
-
Hi Martin,
in Podio calculation fields the value for "today" isn't dynamic cause the result only changes if the value of a variable used in the calculation changes. Example If you have a calculation with a value "today" - e.g. using moment() - and "today" = 4th of April you'll see the result "4th of April". But tomorrow you'll see also "4th of April" if no other value in the calculation haven't been updated. So you need in your calculation one value which is updated daily so that you can calculate with a value "today".
In your use case you can do that with an extra date field (e.g. called "Check-Date") in your App which will be updated daily by Globiflow.
So first create the date field "Check-Date", than create a Globiflow for your app which runs every day after midnight:
Trigger: By an item's date field In your App: Filters: If Date Field is current-Date -1 days Actions: Update This app item check-date(start) = value: current date
Than you can use a calculation for "Total due" in which you only sum your fields Rent, Late Fee and Late Fee Per Diem and multply it with the number of days, like:
var NumDays = +moment(@check-date).format('D') - @Day due; (@Rent + @Late fee + @Late Fee Per Diem) * NumDays
If you want to send an E-Mail you need a value which indicates that the tenant is past on their rent. And you need a field which indicates that an E-mail has already been sent (e.g. a catogory field "Past Due Rent Mail" with categories like "No E-Mail necessary" and "E-Mail sent". If you have these indicators you can create another Globiflow which checks every night after the Check-Date has been updated, like:
Trigger: When an item is updated
In your App:
Filters:
If Field "Check-Date" has changed
and if "Check-Date" equal to current date
and if indicator "Past due on rent" is true
and if field "Past Due Rent Mail" equal to "No E-Mail necessary"Actions: sanity check: date("j") == [field-token: Day due] + 3 send mail .... update this item in field "Past Due Rent Mail" = value: Mail sent
There could be other ways to trigger the "Send Mail"-flow, but that depends on your app setup.
Rainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
1 comment