Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Updating date field, invalid inputs

197 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I'm building a project calendar that has dates that can be adjusted by the user, so all the records in the table are events for the calendar, with their date in a "target date" date field. The project also has a due date that's supplied by another team, so even though it has to be entered in the "target dates" field to show up on the calendar, the user shouldn't be able to edit it and it should always match the date we get from the other team.

I get the due date from a lookup field, and then I have a formula field that returns the due date (the formula also calculates when the other events are supposed to happen). To make the other dates, not the due date, adjustable, I use an automation to copy the formula result into the "target date".

So then to make sure that the "target date" for the due date event matches the supplied due date, I was thinking I'd make another automation that compares the "target date" and the formula date, and if they aren't the same, update the target date to match the formula date. However, the automation isn't working, and it says the condition "received invalid inputs". It seems to be because I'm using a SWITCH() to choose between whether to return the lookup value or the other info, but I'm not sure why?

The formula field is called "Copy Me to Target" and has:

SWITCH({Event Type},
  "Due date", {REF Collateral Due},
  WORKDAY({Sub-Phase Start}, {Actual DfPS})
)

And the automation is:
When Event Type has any of "Due date",
If "Target" is not "Copy Me to Target",
Update record: "Target" to "Copy Me to Target"

If I delete the switch and just use the workday thing, it seems to work. But the lookup the switch returns is formatted as a date, so I don't know why the switch would make it throw an invalid input? The rest of the automation works fine.

2 Replies 2

Hm could you provide some screenshots of your automation setup and the relevant fields please?

I figured out how to do it. Comparing "Target" (a date field) and "Copy me to target" (a formula field) in the automation didn't work because of a variable type mismatch or something. Instead I added a new formula field to state if they're mismatched, set up a view that shows any records where the new formula returns true, and built the automation to trigger when any records enter that view. Seems like a clunky workaround, and not sure why the comparison works in a formula but not in the automation builder, but it works!