Skip to main content

I have a task table with start date, due date, and completion date.  Each task is marked open, attempted, or done via a single select.  Working to create an automation that creates a copy of a task marked as "Attempted."  here's the flow of my automation:


Trigger:  Record meets condition / condition is Status field is NOT open (aka, either Done or Attempted)


Action:  Insert the value of a formula field into the completion date field.  The formula field is simply NOW(), so it will change every day and as we complete tasks, the completion date will change to the current date.


Conditional Action:  If Status field equals "Attempted"


Action:  Create new record with the following dynamically inserted fields:



  • Task Description

  • New Start Date

  • New Due Date

  • Assignee


To calculate the new start and end dates, I have created two columns in the Task table with very similar formulas like this:


DATEADD({Completed On}, 2, 'day')


The new due date field adds 4 days and the new start date adds 2 days to the completion date.  The formulas work as expected and the formula field allows me to format the result as a DATE.  The only issue I have is that open tasks result in ERROR for these two formula fields, but as they operate in the background, I'm not overly concerned about that.


When I use these formula fields as the values of my new start and due dates in the newly created record, my runs fail with this error:


Field "Start Date" cannot accept the provided value: Could not convert string to date.


I am confused because the new start and end date fields ARE date formatted values, not strings.


Anyone have any thoughts???

Can you share screen shots of the triggering record and the automation?

Have you retested the trigger for the automation?
When you test the automation, can you expand the screen to see the field values for the triggering record?
Are you getting the formula values from the triggering record, or some other record such as the newly created record?

It is possible that you are somehow getting the formula value with the error instead of a date.

Can you share screen shots of the triggering record and the automation?

Have you retested the trigger for the automation?
When you test the automation, can you expand the screen to see the field values for the triggering record?
Are you getting the formula values from the triggering record, or some other record such as the newly created record?

It is possible that you are somehow getting the formula value with the error instead of a date.


Hi, Thank you for the help!  I did as you asked and here are the screen grabs:

This is the test task I'm using

 

This is the automation overview:

 

This is the result of testing the trigger and I think it's where the problem lies:

It seems like the automation is grabbing the value of the formula fields BEFORE the formulas have a chance to update in the first action.  I thought that by ordering them this way, it would process in that order - but I guess not.  Is it possible to delay the create record step somehow?  Any ideas how to make this work?  TIA!

Hi, Thank you for the help!  I did as you asked and here are the screen grabs:

This is the test task I'm using

 

This is the automation overview:

 

This is the result of testing the trigger and I think it's where the problem lies:

It seems like the automation is grabbing the value of the formula fields BEFORE the formulas have a chance to update in the first action.  I thought that by ordering them this way, it would process in that order - but I guess not.  Is it possible to delay the create record step somehow?  Any ideas how to make this work?  TIA!


OK, I think I figured a workaround.  The completion date field is a formula that looks up the value a field called Today which uses the simple NOW() formula.  I did it this way bc afaik you can't use calculations INSIDE an automation.  So instead of basing the new start and end dates on the completion date which doesn't get calculated until the automation is triggered... I used the today field which is always available!  Now it works like a gem!  Thanks for your helpful questions - they led to the solution!!!

Reply


Terms & Conditions