Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 04, 2020 11:08 AM
I am trying to set up a project management portal. I have two tables, projects and tasks. When I update the status of a project, I want to create a task in the tasks table, and set the due date of the task ahead one week from today.
All is well until I get to entering the value for the due date field. I can’t seem to get it to resolve functions. As a test, tried to use TODAY() in the textbox of my field in the automation, but when I trigger it, the “runs” section claims it errors out with:
Field “Due” cannot accept the provided value: Could not convert string to date.
So it is trying to convert the string “TODAY()” to a date. I’ve tried {TODAY()} and =TODAY() with no success. Am I missing something or is this impossible?
The only thing I can think of as a dirty workaround is to create a new formula field in the projects table that does something like DATEADD(TODAY(), 7, “days”), then reference this field when setting the value of “due” in my automation. This actually DOES work, but man that seems messy.
Oct 04, 2020 11:48 AM
Yes, you’re right! It is messy, and it adds unnecessary overhead to an otherwise clean Airtable system.
But you have discovered what is probably the best & quickest workaround for this, outside of writing your own custom JavaScript script to handle this for you.
Unfortunately, Airtable’s automations don’t support formulas at this time, so your workaround is probably the best solution.
Of course, you can always turn to Integromat too, which is a super awesome automation platform (my absolute favorite automation platform, and way better than Zapier) — because it actually lets you perform math & logic & other functions within any type of a field! It does a zillion other things, too. It’s pretty awesome. I’ve got most of my clients using it for their “more advanced” automation needs.
Oct 04, 2020 01:43 PM
You’re partly right; it’s actually very messy. :winking_face:
I have pummeled this community for almost two years with observations about the inability to apply formulas to existing fields. Indeed, there shouldn’t even be a “formula” field data type which is equally confusing when you consider there’s a field of type “button”.
A button is a UI control not much unlike a select list, or a video player. Fields contain data; they should support manipulation from keyboards, APIs, automation processes, and yes - formulas regardless of where or how the formula is executed.