I’m using an equation field to calculate the amount of time since an event happened; like “hours since X happened” that use:
currentTime()-{Date field X}
I currently have over 2000 records in this object but I only need this calculation happening on a few records at any given time based on certain conditions. I’m concerned about a potential performance hit with multiple fields like this going off once per hour on all of those records. If I don’t bound this with conditions, there will be a lot of calculations going off on a growing number of records forever and ever…!
Any suggestions on how I could do this calculation conditionally? I don’t see a way to do it directly with a task and I can’t conceive of how to run the equation conditionally by itself.
I only need this calculation happening on a few records at any given time based on certain conditions.
Would you please tell me what the conditions are?
For example, if your condition is the value of another field you can use the ternary operator:
condition ? code1 : code1
So you would write something like:
{Field Y} ? (currentTime()-{Date Field X}) : {Field Z}
If your goal is to show timestamps on posts, for example a social feed (“posted 5 minutes ago”), you can just use custom JavaScript along with Knack’s built in Moment.js library.
So if you share some more details about what your ultimate goal looks like, maybe I can offer more solutions.
I’m trying to calculate the # of hours since a certain action happened on an Object. When the status of the object changes, I capture the current time into a Date field “Time that X happened”.
The next step in the workflow becomes more urgent the longer we wait for it, so I’m calculating how long it has been since X happened, and then sorting on this “Hours since X happened” field in a table.
It appears that the ternary operator might be just what I was looking for. I’ll experiment with it.
If I make this equation field a Date, with the Date Type = days, I believe that will trigger this equation once per hour… does that sound right?
It appears that the ternary operator might be just what I was looking for. I’ll experiment with it.
Awesome
If I make this equation field a Date, with the Date Type = days, I believe that will trigger this equation once per hour… does that sound right?
This one I’m unsure about. Maybe someone else can hop in. According to the docs, setting Date Type = days does not impact how often the equation is triggered, instead it represents the time unit that you are calculating. They give the following example:
Date Type: The date type is used to convert any number value in the equation to a time unit before being calculated. For example, if you selected hours and are adding 5 to a date field, you will be adding 5 hours.
You need to update your record to trigger the equation to recalculate the current time, as far as I know. Because of that constraint, you are better off IMO sorting your table by Time that X happened and using Custom JavaScript to calculate current time - Time that X happened, which you don’t actually need to store in your database AFAIK, but maybe your app does require it.
Good luck. If you want to chat more feel free to DM me.
