Definite solution for "today" handling in calculated fields (days since, days until, countdown,...) / incl. possible solution
Hi folks,
sorry for opening this many requests but I just had to dive into Podio and Podio API for a current project and although I like the idea a lot and see the potential Podio offers, I hit walls every now and then and have to find solutions for trivial stuff. The worst thing in my opinion is that many problems feel "unnecessary" ie. could easily be fixed in my understanding.
One such thing which seems that many many users here are facing is the fact that calculated fields are not recalculated unless a variable changes. This is a big problem for every use case having to handle anything like e.g. "days since x" and "days until x". So it is a much requested feature and there are many posts about it. Sadly many of them are answered, although answers are wrong (do not take into account that formula is not recalculated) or recommend to use third party tools or some hacky workarounds which are not only dirty but also not guaranteed to be future-proof and safe for production use like use fake apps that you reference from every item, update calculation formula with fake timestamps, switch to number fields that are daily updated via API,...
@Podio officials/devs:
I would really appreciate an official answer what is considered the best way of doing this and which way is also the "official" one, meaning it can be safely used in client projects.
So to not only bring up questions, I also want to share what I feel should be a viable solution (coming from database and software development myself).
Why not simply introduce an implicit variable like @currentdate in calculated field formulars and update all calculations that use this on a daily basis? If you are brave (and users could need it) you could also add @currentyear, @currentmonth, @currenthour,... I understand this recalculations eat server resources, but be honest - now we also have some workarounds out there which do the same and cause the load but are at least to say "dirty" and make Podio feel weak. So I think this would be a very good improvement which could help lots of (potential) Podio users and make such a trivial thing like calculate "days until next weekend" a no-brainer.
Kind regards,
Andreas
-
Official comment
Hi all,
I don't have an official solution for you, but I want to make a comment about the reasoning behind the current limitations.
You could argue that calculation fields behave a little counter-intuitively when it comes to using "current time", but there's a good reason for this. It's a little tricky to explain, but bear with me. They key point is that calculation fields are only updated when the data they depend on change. As an example, the calculation
x + ywill be updated every timexorychanges. Now consider the calculationCurrent hour + x. The intent of this calculation is to show9 + xwhen the time is between 9:00 and 9:59,10 + xwhen the time is between 10:00 and 10:59, etc. The problem is that the calculation will only be updated whenxchanges. Ifxdoes not change, then the calculation field will showwhatever the hour was when x last changed + x.
The reason we have to do this is to avoid updating calculation fields all the time. The above calculation would need to be updated every hour, but we could just as well have a calculationCurrent second + x, which needed to be updated every second! Keeping such calculations up to date would require an enormous amount of computing resources.We're working on coming up with a solution that balances flexibility and cost. In the meantime, my best suggestion is to use the API or to set up a "Current time"-app as alluded to in the question.
Best,
AndreasComment actions -
First try for workaround is completed.
Data entry of either one date or two dates is expected - which will create external link to calculate Since and Countdown and results like the following will be delivered by clicking external link.(1)
Welcome
Number of days since 01012015 till 12312015 = 364 days
Countdown to 01012015 from 12312015 = -364 days(2)
Welcome
Number of days since 12312015 = -169 days
Countdown to 12312015 from today = 169 days -
Andreas, thanks for your great explanation. I think, most wanted feature is "current date", not so much "current hour". And most people would be glad to have a token "current date" - which wouldn't need so much computer resources as your example with hours and which could be updated over 24 hours due to the timezones. A token like "@created on" on which you can use moment() to then get current week, current month, current year.
We solve this with Globiflow and also with a "current day App" (with one item which has one date field only). But all these workarounds are cumbersome ( "dirty" as I mentioned in another comment here). Especially the "current day App", which has to be related to every item in each app which needs to deal with current day cause Podio Advanced Flow don't allow to relate a new item automatically to a specific item (Btw: Why not?")Cheers,
Rainer -
Indeed I use a workaround. It's a current time app. All my clients can count days and in some way hours (related to current date/time). With the right app design you can use it in many apps across workspaces. And it helps for creating live reports.
Rainer
rg@delos-consulting.com -
Hello Veerle
the Current Date App has only one Item with 2 fields: A title field and a date field (you even don't need the title field). This date field is updated every day to the the current date. You can do that manually or (more comfortable and more reliable) automatically by a flow with Globiflow.
The trick is to have access to this current date item in every app where you need it. To have access means: You need a relationship to this item. The relationship can be direct or indirect. Direct means: In App A you have a relationship field which points to the Current Date app. Indirect means: App A has this relationship and App B is related to A, but not to Current Date. But with the right app design, App B can get the current date through App A.
One example how I often build these relations for my customers:
First I create the Current Date App (CD), with an item which has the title "Central Timer" and a date field "Today's Date".
Then I look for an app which is related to many other apps. That's often an employee app (with an item for each employee) or a contact app (cause these apps are often related to Projects, Deliverables etc. ), but it can be every other app.In this Central App (CA) I add a relationship field "CD Relation" to CD. Then I export the existing CA- items. In the sheet I enter in each cell in the empty column the title of the CD-item "Central Timer" and re-import the sheet. Now every CA-item is related to the CD-item. If you make the field CD-Relation a required field you won't forget to link new CA-items to CD (can also be done by Globiflow automatically).
Next step: In CA I add a calculation field "Today" and enter: @Max of Today's Date (this gets the date from CD). Click done, now you have the daily updated current date in each CA-item. And in each app which is related to CD you now can grab the current date from the field "Today" and use it in the calculation fields in the other apps.HTH,
Rainer -
Thank you Rainer! Your explanation was very clear and it works. May I ask something else? I want to program a calculation:
If @variabele is divisible by 365 (round number), then I want the text "it's time for a conversation with staf". Is this possible? I have to learn a lot! Thank you very much. Kind regards, Veerle
-
That's
@variable % 365 == 0 ? "it's time for a conversation with staf" : "";
One tip: For such calculation questions there's an own forum https://help.podio.com/hc/communities/public/topics/200101427-6-Calculations
Rainer
-
Hey Andreas,
just in case this would interesting for evaluation: I would pay for that feature to compensate your additional required resources. I also have to do that for other tools in online marketing when it comes to tracking e.g. Supermetrics. Just from my perspective I wouldn't have any problem to pay for that.
Please sign in to leave a comment.
Comments
32 comments