Date, formatting and Arithmetic

[Sanjay Rajan]

Hello, 

Can any expert please advise how to go about writing this rule?

For background, I've 4 custom fields:

Planned Start date: Date format

Planned End Date: Date format

Number of days required: Number format

Number of days on leave: Number format

here are example values

Planned Start date: 6 May 2024

Number of days required: 6 ( this is actually 6 working days)

Number of days on leave: 1

The rule I want to write in plain english is:

When 'planned start date' AND 'number of days required' And 'Number of days on leave' is filled out/changed, set Planned End Date = Plannned Start Date + Number of days required + Number of days on leave.

Output should be Planned End Date: 15 May 2024 ( 7 working days from Planned Start Date)

Problem is when I use set custom field, I cant see the date custom fields.

And when I use set date custom field, the option has now which is will mess up the logic. 

Please advise. Thanks. 

[Mutya A · Accepted solution]

Hi Sanjay,

Thank you for explaining your use case in more detail. I have these fields setup in my example:

1. PlannedStartDate - date custom field
2. DaysOnLeave - number custom field
3. DaysRequired - number custom field
4. PlannedEndDate - date custom field

I was able to setup a trigger that looks for changes in the DaysOnLeave custom field, as long as you have the PlannedStartDate and DaysRequired custom fields complete.

Once this triggers, it sends a sequence of actions to simulate this formula: 

1. PlannedEndDate = PlannnedStartDate 
2. PlannedEndDate + DaysRequired
3. PlannedEndDate + DaysOnLeave

For example:

1. PlannedEndDate = 6 May 2024
2. PlannedEndDate = 6 May 2024 + 6 working days
3. PlannedEndDate = 14 May 2024 + 1 working days

PlannedEndDate = 15 May 2024

This is how it looks:

To setup the last 2 actions, you can select this option below:

For reference: https://support.atlassian.com/trello/docs/arithmetic-and-formatting-in-date-variables

I hope this helps, but let us know if you have any questions or need clarifications!
----------------
If this post helps with your query, then please consider the Accept answer or Like button to help the other users find it more quickly.

[Sanjay Rajan]

Thank you, Mutya A. This is super helpful. 

[Sanjay Rajan]

Hi Mutya, 

Thanks for providing screenshot along with the explanation. 

Howvever, I'm having trouble implementing the above advice.

My first problem is that when I use the "Set Custom Field ____ to  ___(New Value)___" template under Fields within the Action, the drop down for the first blank (which is for custom fields) does not show me dates custom fields. Number, text and list custom fields are shown for me to make a choice. Did you also see the same from your side?

The workaround here is to manually enter the custom field name which works out to:

Set Custom Field ____PlannedEndDate___ to  ___{{%PlannnedStartDate}}___

The above works as the rule gets saved.

Now when I go to enter the second action of moving the Planned End date I am getting an error "Cant Parse Command". Please see attached screenshot. Can you please help troubleshoot this? Field types are exactly the same as discussed above.

[Sanjay Rajan]

[Mutya A]

Hi Sanjay,

Can you check if the parse command error goes away when you use a custom field without spaces. You must edit the Custom Field name first and remove the spaces before this works:

by {{%NumberOfHolidays}} working days

Let me know how you go with this. 

[Sanjay Rajan]

That's awesome. Problem solved. Thanks again, Mutya. 

[Sanjay Rajan]

One another question Mutya: Now that we are able to populate both those date fields of one particular card, is there any way to write a rule which would "copy value in custom field 'PlannedEndDate' from card number 1 ( which we just talked about above) when set" into say the customfield 'PlannedStartDate' of card number 2? Please advise. Thanks.

[Mutya A]

Yes that is possible, please have a read through this documentation as they have examples that uses custom fields: 
https://support.atlassian.com/trello/docs/finding-or-looking-up-cards-butler-advanced-topic/ 

You can also search the Trello Community as there are lots of custom field questions that have been answered before: https://community.atlassian.com/t5/Trello/ct-p/trello 

[Sanjay Rajan]

Hi Mutya! I had already gone through that webpage and also read up one a few other pages but that's not helpful. It does not apply to my use case. I also did search the community but couldnt' anything relevant. I know how to use cascade feature under action but the problem is how to access the trigger card's custom date field and then put that value into the found card's csutom date field. In plain english, found card's planned due date = trigger card's planned due date + 5 for example is what I'm looking to do. The priority example and other examples are mainly using cardname, carddescription, and cardduedate which is not applicable in my use case. 

[Mutya A]

Hi Sanjay,

You should be able to find a way to link those two cards together by attaching card 2 inside card 1. From there, you can use the "for each card linked in the attachments" action under the Cascade tab. This is a way of synchronizing cards together one-way: https://support.atlassian.com/trello/docs/synchronizing-cards/ 

If this helps: https://support.atlassian.com/trello/docs/finding-or-looking-up-cards-butler-advanced-topic/ 

So when the custom field PlannedEndDate is set on card 1, it will find card 2 in the attachments and then set the card 2's PlannedStartDate to the value of the PlannedEndDate by using the automation variable {customfieldvalue} . To reiterate from Custom Field Arithmetic:

The value of a Custom Field used in a rule’s trigger can be referenced later in the rule’s actions using {customfieldvalue} 

Here is the rule:
 

Hope this helps but let me know if I'm missing any context. 
----------------
If this post helps with your query, then please consider the Accept answer or Like button to help the other users find it more quickly.

[Sanjay Rajan]

Thanks Mutya! I did read about the links.

After reading the links, I did include card 2 as attachment in card 1. I did set up the automation rule as explained above but it failed. I also checked the automation log as the rule failed to run for the linked attachment.

As soon as entered those 3 custom fields in card1, rule 1 ran successfully and Trello did populate the PlannedEndDate in card 1. I then waited and expected Trello to populate PlannedEndDate in the attached card 2 as well but that did not happen. I checked the automation log as well which read as follows:

Wednesday, May 15, 2024 7:53 PM

Running automation on board XXX

Couldn't read a date in "".

The automation finished running.

Total operations: 2

I dont follow why it couldn't read the date as the date was entered in the custom field using the calendar provided and it was not manually entered by text which can at times be confusing becasue of different date formats. Below is the rule  [ Yes, I did include card  2 as attachment in card 1 ( which is the milestone card and this card 1 itself has a separate rule which we discussed above) and both cards live on the same board itself].

Appreciate your help fixing this. Thank you!

[Mutya A]

Have you tried using {customfieldvalue} instead of {{%PlannedStartDate}}? 

The value of a Custom Field used in a rule’s trigger can be referenced later in the rule’s actions using {customfieldvalue} 

[Sanjay Rajan]

Thanks for getting back. Though, sorry, I dont fully understand the explanation.

To clarify, as per this article, https://support.atlassian.com/trello/docs/working-with-custom-fields/ page, the method to refer to the value in action is by using {{%Fieldname}} which is what I'm doing. The custom field value is given by the user and so it's dynamic. I cant hard code a fixed value into that field if that's what you meant.

To recap if there is misunderstanding, I know how to use cascade feature under action but the problem is how to access the trigger card's custom date field and then put that value into the found card's csutom date field. In plain english, found card's planned due date = trigger card's planned due date + 5 for example is what I'm looking to do. 

Thanks again for your help.

[Mutya A]

Hi Sanjay, 

Apologies for the confusion. To clarify, you need to explicitly use this variable {customfieldvalue} as per my previous screenshot. You don't need to use a hard-coded value or even the {{%Fieldname}}. Just type in this "{customfieldvalue}". 

If you follow the screenshot below word for word, this will cascade to the cards attached in your card 1. 

I've tested it and it works for card 2 and card 3 if they are attached to card 1. Once you update PlannedEndDate in card 1, automation will then set card 2 and card 3's PlannedStartDate to the value of the PlannedEndDate set in card 1. 

I hope this helps, but let us know if there is anything else that is unclear. 
----------------
If this post helps with your query, then please consider the Accept answer or Like button to help the other users find it more quickly.

[Sanjay Rajan]

Thank you Mutya! You're absolutely awesome. At times, although documentation exists, interpretation isn't always clear such as in this case which results in failure. I appreciate your clear explanation and prompt advise. Thanks once again.

[Sanjay Rajan]

Kindly ignore this post. I found the solution to next question but I see no option to delete my post so I've removed the text. Apologies for any confusion.