Change a record when a specific time arrives?


#1

Good Morning,

Is there a way to make a formula to change a record when a specific time arrives?
It is to put a forgotten task marker on the table as it passes its execution time that is in a cell of the table.

I know it is updated when changing / modifying the registry but it does not change with time.

I hope you can answer this question.

Thank you very much in advance.


#2

You could create a formula field on your end which has the following formula

IS_AFTER({Deadline}, NOW())

Where {Deadline} is your date field.

After that create a view filtered by the above formulae.

Whenever there a new record in the above view use the Airtable API to update the record to update any field in that record.


#3

You can also use Record Coloring --a paid feature-- instead of a marker. Just use the conditions, such as On or Before Yesterday and a color to highlight the records. Will work with date, not time. You could have a second condition to color “due today”


#4

One thing to keep in mind is that as far as your base is concerned, if no one accesses it, time stands still.

For example, let’s say I have a date field called {Deadline} and a formula field called {Expired Deadline} with the formula IS_AFTER(TODAY(),{Deadline}). (That is, if today’s date is later than {Deadline}, return a value of 1 to show the deadline has expired.) On January 15, I set {Deadline} to equal ‘January 17, 2019’ — but no one accesses the base during the next 5 days.

Q: On January 21, a split-second before I open the base, what is the value of {Expired Deadline}?
A: 0

Q: On January 21, a split-second after I open the base, what is the value of {Expired Deadline}?
A: 1

At first, this may seem like a horrible flaw in Airtable, but it’s actually just a variation on the old ‘If a tree falls in a forest’ thought experiment. Yes, strictly speaking, the value of {Expired Deadline} is incorrect at the stroke of midnight on January 18. However, in practice it doesn’t matter, as no one is in a position to perceive it is incorrect — and the moment someone is, on January 21, the value of TODAY() is recalculated and {Expired Deadline} evaluates to 1.

So, the basis for your original question is correct: The value of the forgotten task marker (which is essentially a field with the same formula as {Expired Deadline}) does not change at the precise moment its execution time passes. However, it’s a moot point, as there is no mechanism allowing Airtable proactively to perform calculations or fire off alerts independent of there being an observer — and once an observer is present, field values displayed are those valid for that moment in time.

Then how does something like Zapier work, you ask. The very same way, I respond. Calculated values still aren’t updated in the absence of an observer — but in this case, the integration service (Zapier, Integromat, Stamplay, whatever) is the observer: At an interval, the service accesses the base and triggers the recalculation.


#5

Thank you all for your answers.

@W_Vann_Hall if we talk about simple dates there are no problems, how well you say until you enter AirTable no one will see that the task has forgotten to do, what happens to me is that I want to also work with hours and minutes.
To have a large screen with a grid of tasks where you can see the ones that have been forgotten, the ones that have to be done and the ones that have been done during the day.

Right now I have the formula made:

IF ({Finished} = 1, “:white_check_mark:Done​:white_check_mark:”, IF (IS_BEFORE ({Data Time test}, NOW (‘DD / MM / YYYY HH: MM’)), “:alarm_clock: Forgotten!!! :alarm_clock:”, ":running_man: To Do! :running_man: "))

But as I said earlier, it is only updated when there is a change in the registry or when AirTable is closed and reopened.

Thank you very much again.


#6

Oh, got it: You’re using Airtable in dashboard/mission control mode, keeping it open as a real-time process monitor.

I screwed around with Zapier for a while, looking for a magic combo of trigger options that would cause recalculation in existing records, without finding anything. Next I decided to use Zapier’s webhook retrieve poll trigger to drive a simple ‘list records’ request to the Airtable API, but I was stymied by the trigger’s lack of support for anything other than basic HTTP authentication. Zapier’s recommendation for users needing support for more-complex authentication methods is to use their development platform to create a private application — which, frankly, seemed like too much work to satisfy idle curiosity.

All I needed, I thought, was a way to refresh the page automatically, thus triggering recalculation. As I was using Chrome, I opened my text base in a new tab, popped up Chrome’s developer tools, and added a meta-refresh entry to the current page’s <head> declaration. I set a handful of records in my test base to have deadlines expiring over the next few minutes and, leaving that tab open and active in one monitor, went back to editing a document in another.

When I checked a few minutes later, a couple of records, at least, should have been highlighted with expired deadlines. None was. This is the point where I started getting grumpy…

This is also the point where, if I smoked, I would have gone out for a smoke — but I don’t, so I went and got another cup of tea, instead. Somewhere between the kitchen and here it occurred to me I was probably overthinking the solution,

I logged onto the Chrome Web Store and searched the extension section for the keyword ‘refresh.’ In response, I got a ridiculous number of hits, with at least a couple of dozen that appeared would work. I picked one at random, installed it, returned to my test base, and activated the extension on a 10-second cycle. The timer counted down, the page refreshed, and three more records whose times had expired suddenly displayed alerts.

So, there you have it: Perhaps more accurately a workaround than a solution, it provides comparable functionality to that requested, costs nothing, and was ready in 30 seconds (ignoring the hour-plus I spent attempting to be more elegant). While I don’t know enough about your solution to know if this approach suits it, if it does, it’s an easy fix.


#7

Good for this inspiring tea cup!
The truth is that I had not thought about this “solution”
I have done some tests and it works.
Now to see if the AirTable take this as a function to add to the “wish list” and so you can save install extensions and also that if everything goes integrated could be used on a Smart TV without having a computer connected to a screen.

Thank you very much @W_Vann_Hall for your interest and provisional solution I hope :wink:


#8

I suspect implementing such a beast would fly in the face of a number of principles underlying design of the Airtable platform.¹ Like lawn darts, bigotry, and Sansabelt slacks, In 2019 full-container (screen or iframe) refresh is considered démodé. (Even at the height of its popularity, it was only slightly better-received than the <blink> tag: an improvement over mindless pounding away at the ‘reload’ button only in that it kept others from seeing you do so.) Today, there are far more acceptable methods for updating the screen, designed to transfer only those data that have changed since the previous update. Most involve some sort of subscription to or registration of elements of which one wishes to be notified of changes. (Unfortunately, that suggests information is contained in a sequence of element states that can’t be discerned from a single state in isolation, which flies in the face of other generally accepted principles of application design…)

The problem is that not only are element-level notification schemes far more difficult to design than is a brute force refresh of the entire container, their successful implementation assumes deep familiarity with how the product is used — familiarity that may be a little difficult to acquire if one is busily assembling the initial release.

Consider your use case: When a task’s execution time expires, if the task record is contained in the currently active view, you want it to reflect this state change. For that to work, here are a few of the issues that need to be addressed:

  1. How to indicate which fields trigger updates? (‘Indicate’ here has two possible meanings: How the user informs the client, and how the client informs the server. Unless otherwise noted, moving forward it’s safe to assume there are at least two components to each issue raised.)
  2. At what granularity should the system check for updates? Presumably some sort of sweet spot exists that balances responsiveness and overhead: How is it to be determined? Validated? Re-evaluated?²
  3. What throttling or clamping mechanism is needed to protect Airtable servers and bases?
  4. How does one determine which is the current app and view?
  5. Equally importantly, if the record exists in the current view, how does one determine if it is part of the current viewframe?
  6. If the record is not visible in the current viewframe, what behaviors should be exhibited upon record update? Record updated by not displayed? Record updated and viewframe shifted so that record is center-most within the frame? Top-most? Bottom-most?

And those are off the top of my head; undoubtedly several times as many would emerge from any serious design brainstorming session. Furthermore, these are issues that arose in isolation; no doubt additional concerns will emerge when element updates are considered in conjunction with other existing or desired functionality.

It should come as no surprise, then, that Airtable’s first incarnations chose simply to ignore most instances of element update: To do otherwise would have required a depth of information on user requirements, methods, and practices that simply doesn’t exist for a product in initial deployment. Unfortunately, acquiring the data and experience necessary to address such concerns only makes it possible to address them; it doesn’t make them any easier to address.

Ultimately, I am of two minds when it comes to the possibility of Airtable implementing a built-in container refresh function. On one hand, it would allow a more smoothly integrated refresh mechanism than is possible through a browser plugin. (Simply being able to refresh only the innermost relevant container would greatly improve the overall user experience.) If nothing else, javascript-based refresh would be cleaner, more easily configured, and provide more options than meta refresh.

At the same time, though, resources directed towards screen- or container-based refresh are resources not brought to bear on the actual endgame: element-based refresh. At best, they represent wasted cycles; at worst, by implementing methods or functionality that conflict actively with their element-based equivalents, they could end up delaying the final product by more than merely the amount of effort diverted.

Finally, there are reasons to establish and adhere to overarching principles of architecture and design that go beyond simple gains in efficiency. It’s one thing, as creator and architect, to convey its intended design to the engineers and developers tasked with building it; do a good-enough job communicating the detail, and the final product could end up a lot as you had imagined and perform to the full extent of your ability. Manage to inculcate a vision of the product, on the other hand, and you may find your grasp can exceed your reach. With a shared understanding of not just the goal but the reasons and methodology underlying it, you can unlock the potential for synergy, the team’s capabilities as a whole outstripping that of their combined strengths. Start tossing in caveats and exceptions, though, and it becomes much harder to sustain such a common vision.

That said, though, I’m with you on this request: Seemingly, everything needed to implement a safe and streamlined container refresh function already exists in the Airtable libraries; all that are missing are table- or view-level configuration options to enable refresh and specify the interval between refreshes. This strikes me as low-hanging fruit unlikely to cause a tummy-ache, and I hope Airtable seriously considers its implementation.

Vann


  1. This assumption is not based on my having hacked into Howie’s laptop, surreptitiously photographed whiteboards after hours, or dumpster-dived in the alley behind 799 Market. Instead, it comes primarily from my understanding of best practices in application architecture and design at the time Airtable was developed and how the current product behaves, secondarily from occasional dives into the application’s javascript library.
  2. It’s essential, with such an app as Airtable, regularly to revisit one’s initial metrics and assumptions, as users are a notoriously unruly bunch with the exasperating tendency to use a product as they see fit, rather than as one intended. Even minor changes to the predicted use case can result in significant increases in resources and bandwidth required. For instance, I’ve wondered for a while whether Airtable’s data forwarding parameters may need fine tuning, as they appear to fire too easily. This can most easily be seen while entering data into a field referenced by a calculation also visible in the current view. Often, the formula recalculates after every keystroke, generating unnecessary traffic and degrading performance.