Calculating in an item on basis of filtered items
I often find myself in a situation where I want to make calculations on basis of filtered items. An example:
I have a time app where I register all the time spent on different cases. The time spent is linked to the case by referencing the case in a case app. I would like to have a calculation field in the individual item in the case app stating all the time spent on the individual case this month.
I can currently only obtain this information by using Podio's report feature. But for various reason I would like to display the information directly in the individual case item and I have many other similar use cases where this "filtering approach" would be very useful.
Can it be done?
-
Hi Rainer
I have a dedicated workspace for each of my regular customers. The workspace amongst others contains the following apps:
[Cases]
[Time spent]The [Time Spent] app contains a reference to the [Cases app]. So when I register time I enter in the time spent and choose the case the time is related to.
I would for example like to have a calculation field in each of the items in the [Cases] app stating:
--> total time spent on all cases this month <--
The reason for this is, that I can then use Globiflow to sent messages to my customers once certain thresholds are exceeded. This allows me to automatically push information to my customers rather than requiring them to login to their Podio account to view reports on their own initiative.
Was that detailed enough?
-
Hi David,
sounds simple but it's a bit tricky. GlobiFlow can't be triggered by a change in a calculation field (cause Podio sends no webhooks for calculation fields) and in Podio calculation field it's a bit cumbersome to get this sum of all time sheet for a specific period because values in calculation fields only change if the value of a variable used in the calculation changes.
I would recommend the following setup (assuming that your [Time spent] app has a date field and the [Cases] app is related to a central [Customers] app):
1. In [Time spent] you make your entries like " 5 hours" (number field] on " 11.02.2015" (date field) and link this time sheet to a Case.
2. In [Case] you need a calculation field "Total time spent this month for this case" , a relation field to [Customers] and an extra "Check-Month" date field (where the date should be a date of the current month, like "01.current-month.current-year")
3. In [Customers] you need a calculation field "Total time spent this month for all cases of this customer" and a category field "Treshold-Check actual month" with 2 categories like "Under limit" and "Treshold passed - Notification sent"
4. In GlobiFlow you need 2 flows "By date or day":
- one running every day (or any period you want) which checks the field "Total time spent this month for all cases of this customer" and if the thresholds are exceeded, sends a message to the customer and sets the category field "Treshold-Check actual month" to "Treshold passed - Notification sent".
- one running once a month on midnight between last day of the expired month and the first day of the now current month which sets a) in [Case] the "Check-Month" to the first of the current month in all cases and b) in [Customers] the Check-Field back to "Under Limit".Code for (2.): The Javascript code for the calculation field "Total time spent this month for this case" in [Case]:
var check = moment(Check-Month).format("MM-YYYY");
var time = All of Date; // Dates from [Time spent]
var hours = All of hours; // Hours from [Time Spent]total = 0 for(var i = 0; i < hours.length; i++){ if(moment(time[i]).format("MM-YYYY")== check){ total +=hours[i] } } total;The result of this code is the current sum of hours out of [Time spent] for this case for the month which is in the date field "Check-Month". Everytime a new time sheet is added via relation field the sum will be updated (if you change the "Check-Month" date to a previous month the sum of hours for this previous month will be shown)
Code for (3.): The calculation field "Total time spent this month for all cases of this customer" needs only a simple entry:
@sum of Total time spent this month for this case
It sums all numbers of all cases for this customer.Rainer
-
Hi Rainer
FIRST: I am really sorry for the very long response time. Reason: wanted good time to thoroughly follow up on your answer so I postponed it a little ... and a little more ... and then again a little more. The length of the postponement became embarrassing and I thought I really had to make to for a good follow up so I postponed it some more .... Don't know if you know this dynamic which results in unending postponements?
SECOND: Thank you so much for your very detailed and thought through answer. I really appreciate you taking the time to help me.
THIRD: I have now begun to implement the first part of your solution (item 2). However the calculation field does not produce the right result. I have inserted the following into the calculation field:
var check = moment(@Tjek-måned).format("MM-YYYY");
var time = @All of den; // Dates from [Tidsoversigt]
var hours = @All of Tid; // Hours from [Tidsoversigt]
total = 0for(var i = 0; i < hours.length; i++){
if(moment(time[i]).format("MM-YYYY")== check){
total +=hours[i]
}
}
total;
where "@tjek-måned" is the check-month date field
where "@All of den" is the date field in my [time spent] app
where "@All of tid" is the duration field in my [time spent] app.I am in doubt in relation to the part concerning:
Dates from [Time spent]
Hours from [Time spent]as this seem to reference the [Time Spent] app. But I am not able to use the "@" sign to reference just the app. I have tried the following solutions:
(a) inserting the text "[tidsregistering]"
(b) inserting the reference "@all of time"My [time spent] app is named "Tidsregistering"
Both (a) and (b) produces the result 0 in relation to all the item in my [case app] even though this is not the right result.
Have I made a mistake in entering the Javascript code into the calculation field?
-
Hi David,
that's right - in Javascript everything behind // is not active and used for additionla information. It can be used e.g. too for "deactivating" vars or result lines when you are playing with code and want to look what happens if you use that var/result or not (// instead of deleting vars/results). The square brackets and the terms in it I used as you used it in your second posting here.
Hope the code helps you.
Rainer
-
Hi Rainer
Thank you for a very fast reply.
Actually no - not so far. I have begun implementing item 2 in your list. However the calculation field does not produce the right results. I have inserted the following into the calculation field:
var check = moment(@Tjek-måned).format("MM-YYYY");
var time = @All of den;
var hours = @All of Tid;
total = 0
for(var i = 0; i < hours.length; i++){
if(moment(time[i]).format("MM-YYYY")== check){
total +=hours[i]
}
}
total;
where "@tjek-måned" is the check-month date field
where "@All of den" is the date field in my [time spent] app
where "@All of tid" is the duration field in my [time spent] app.However it produces the result "0" in relation to all items even though this in several instances is wrong.
Have I somehow mistyped the Javascript?
-
HI David,
the code seems to be ok, it should work and it works here. Do you get the "0" in the template creator or when you look into a case item?
If you get the "0" in a Case to which time spent items are related, please check, if
1. the caculation field is number type (if not, create a new calculation field).
2. the check-date field in [Case] has a date of the current month
3. at least one the [time spent] items which are related to the case where you check has a date of the current month.Rainer
-
Hi Rainer
My follow up:
(1) The calculation is a number field displaying the result with two decimals after the integer
(2) The check-date field is set to 2nd March 2015
(3) The case I am testing on has three items which are related to the case and which are registered the 23rd of March and the 25th of MarchThe @all of hours reference in the code references a duration field in the [time spent] app. Don't know if this matters.
I get the zero both in the template generator and when I look into the case item.
Any suggestions?
-
Hi David,
it doesn't matter if the type of the hours field is number or duration.
Please try in the calculation field only
@sum of hours
(as last line below the rest of the calculation). Is the result also "0"?Do you have a 2 relations between the apps [Cases] and Time spent?
If so please control if you have selected the right tokens (incoming!) from the list you see when you type @.Next step to prove:
create a new calculation field (just for testing):var check = moment(@Tjek-måned).format("MM-YYYY"); var time = moment(@All of den[0]).format("MM-YYYY"); check + " " + timeand check if both values are equal (have to be both "03-2015").
Rainer
-
Hi Rainer
There were two different references to choose from when choosing "All of hours" and "All of dates". This puzzles me as there - as far as I can see - is only one relation. But nevertheless it worked to choose the second reference - thank you for that.
Now that it is working I have moved on to implementing the rest of your solution and it struck me that can actually become quite complex to set up flows continously updating the "Check-month" field. And now that you have provided me with a near-perfect solution I simply have to ask the following question:
Is is possible to let the Javascript fetch the Month/Year value?
That would make the solution perfect. I Googled the question and came up with this:
var month = dateObj.getUTCMonth() + 1; //months from 1-12
var day = dateObj.getUTCDate();
var year = dateObj.getUTCFullYear();newdate = year + "/" + month + "/" + day;
But I have absolutely no idea if this can be used or if works within Podio.
Any thoughts about that?
-
Hi David,
if you see two references than there are two references (maye incoming/outgoing or from other apps). You should check that and maybe delete one if it's obsolete. If you can't find a reference field for the second reference and this reference is incoming you could use "Cleanup deleted field values" in the app modification menu of the Time app.
The javascript should work in principle in a Podio calculation field. But it doesn't solve the issue that a calculation will only be updated if one of the value changes. You need the check-month field and a Globiflow for updating it. Don't use a flow "By Date or day" but a date triggered flow in App Cases (runs every day after midnight).
Trigger: By an item's date field In App: Cases Filters: If Date Field is current-Date -1 days Actions: Update This Case check-month(start) = calc: date("Y-m-t", strtotime("+0 month") ) // sets date to last day of current month Get Referenced Item(s) from App Customers Wait/delay Update all collected Customer(s) in App Treshold-Check Actual month = value: Under LimitThis flow checks every day if the check-month date is current date - 1day. On every first day of the month the following actions will be executed cause check-month-date = last day of the previous (to make it run the first time you have to set the check-month-dates of all Cases items to April, 30th).
For the second part - the check if treshold is passed - you can use the new functions of Podio Advanced Workflow which are available since some days (instead of a Globiflow checking the items every day).
In the Customer App you already should have a calculation field "Total time spent this month for all cases of this customer" with the entry:
@Sum of Total time this month(sums all fields "Total time this month" from the related Cases)
Create a second calculation field in the Customer App " Treshold-Status":
@Total time spent this month for all cases of this customer >= 20 ? "Treshold passed" : "Under Limit"And create a category field " Treshold-Check actual month" with categories like "Under limit", "Treshold passed", "Mail sent":
Than create a Podio workflow in Customer App:
If customes was updated "Treshold-Status" has changed and if Future "Treshold-Check actual month" field value is equal to Under limit Than update item here "Treshold actual month" -> select fixed option "Treshold passed"So when the sum of hours reaches the treshold, the calculation field "Treshold-Status" changes from "Under limit" to "Treshold passed". This change triggers the Podio Workflow to change the Category field to "Treshold passed".
Now you have the choice: should the category change trigger directly a Globiflow to send the E-mail to the customer or should it notify you via the Podio Workflow so that you can decide to let Globiflow send an E-Mail - e.g. via a click on category "Send E-Mail". If it should trigger a Globiflow automatically you have to create a Globiflow:
When item updated and if field "Treshold-Status" changed and is equal to "Treshold passed" sanity check: [field-token: Total time spent this month for all cases of this customer] >= 20 than send E-Mail and update this item in field "Treshold-Status" to value "Mail sent".On the first of a new month (just after midnight) all fields "Total time this month" are set to 0 cause of the date change in the field "check-Month", which triggers the change of the calculation field "Treshold-Check" back to "under limit".
Rainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
14 comments