Solved

Workflow Automation Recipe help for moving opportunities to a different pipeline stage based on a custom field date


Hello everyone!

I need some help creating a workflow recipe for one of our pipelines. We have a pipeline that tracks all of our current customers that have purchased software. Each customer is required to renew their annual support every 12 months so this pipeline has the following stages:

  • Current
  • 6 months from Expiring
  • 90 days from Expiring
  • 30 days from Expiring
  • Expired

Each opportunity also has a custom date field called Paid Date. When a customer is 180 days past the Paid Date, the opportunity should be in the 6 months from Expiring Stage (and so on with the other stages).

What I want to do is create a workflow that checks the Paid Date once a day and moves the opportunity to the correct stage based on how many days it’s been since the Paid Date. So that would mean:

  • Any Opportunity in the Pipeline where the Paid Date is less than 179 days prior to the current date would be in the Current Stage of the Pipeline
  • Any Opportunity in the Pipeline where the Paid Date is between 180 and 269 days prior to the current date would be in the 6 months from Expiring Stage
  • Any Opportunity in the Pipeline where the Paid Date is between 270 and 329 days prior to the current date would be in the 90 Days from Expiring Stage
  • Any Opportunity in the Pipeline where the Paid Date is between 330 and 365 days prior to the current date would be in the 30 Days from Expiring Stage
  • Any Opportunity in the Pipeline where the Paid Date is greater than 365 days prior to the current date would be in the Expired Stage

I know I will need to use functions to accomplish this but I cant quite seem to figure it out. Can anyone help me? 

icon

Best answer by Michelle from Copper 3 May 2022, 19:57

View original

10 replies

Userlevel 7
Badge +7

Hi @matt.dickinson, thanks for posting! And also thank you for all the details - that’s super helpful.

From a high level, it sounds like you want the system to automatically move Opportunities based on how long it’s been since Paid Date.

When we need to continuously monitor a value, we use the Check Once a Day function of the workflow automation tool. However, Check Once a Day does not work with custom fields such as the one you have for Paid Date. It can only evaluate default fields as part of the trigger.

So, this makes me wonder… how are you using the default Close Date on those Opportunities? I’m wondering if we can work backwards from your Close Date instead of the Paid Date.

That question is the big blocker for this workflow automation, so let me know and I’ll be happy to help you from there :) 

Hi @Michelle from Copper !

 

Thanks for the response. If we could get the automation working using Close Date I think we would be willing to switch to that. Assuming we use Close Date, how would I build the Automation rules to do what I am looking for?

Userlevel 7
Badge +7

@matt.dickinson Ok great, in that case let’s dive in!

So, we’ll need to build one workflow automation rule for each “bucket” or pipeline stage. I’ll run you through a couple of examples and that should give you enough detail to do the rest.

In these examples we’re assuming Close Date is exactly the same as Paid Date.

 

Opening the Workflow Automation creator

(just in case you haven’t done this before, or if someone else stumbles upon this example)

  1. Make sure you’re an admin in Copper
  2. Go to Settings > Automation > Workflow Automation > Add Workflow

You’ll see boxes for Name, Trigger, and Action. Trigger and Action are the important bits we’ll cover here. We’ll start with the simplest one:

 

If an Opportunity’s Close Date is more than 365 days in the past, move it to the Expired stage.

 

Trigger

We want our Trigger to say: every day, check if the Close Date is more than 365 before today. This is what that would look like:

Pay attention to these settings:

  • Opportunity: this tells the system which record type to monitor (Opportunities).
  • Check Once a day: this tells the system to monitor the Close Date once per day.
  • Close date - Before - Use a FormulaDATEADD(CURRENTDATE(),-365) : This formula subtracts 365 days from today. Then, it compares it to the Opp’s Close Date. If the Close Date is before [ today minus 365 days ] then we know it’s been more than a year, so the workflow automation moves onto the Action (moving it into the Expired stage)

Note, I only included one Trigger criteria in this example (Close Date). But, I recommend you add more to reduce the number of Opportunities the system is check every day. So for example, you can add a Trigger Condition for which Pipeline the Opp needs to be in, or which Status it should have (Open/won/Lost/Abandoned).

 

Action

Here we define which Pipeline + Stage the Opp should land in if it meets the Trigger criteria.

Pretty straightforward!

 

Once you’ve got this set up, save and turn on your workflow automation

 

Ok, let’s try a slightly more complicated one:

 

If an Opportunity’s Close Date is between 330 and 365 days in the past, move it to the Expiring Stage.

So, this one uses the same idea except we are going to have two Trigger Criteria.

 

Trigger

We’re going to split the statement “between 330 and 365 days in the past” into two pieces:

  1. Close Date is more than 330 days before today, AND
  2. Close Date is less than than 365 dates before today.

This is what that Trigger looks like:

Notice how the Filter Conditions specifies AND rather than OR. This means an Opportunity has to meet both criteria.

 

Action

 

You can use these example as a starting point for each of your workflow automation rules.

 

Let me know if you have any questions. Oh and we have a weekly workflow automation Q&A session - feel free to join if you prefer live help. :)

Userlevel 7
Badge +7

@matt.dickinson let me know if you’ve had a chance to set this up! In the meantime, I’m going to set my previous reply as “Best Answer” so that others can find it easily.

Hi @Michelle from Copper !

 

This was exactly what I needed! I haven’t gotten a chance to implement it just yet but I am sure it will do the trick. Thank you so much for the help and if I need anything else I’ll be sure to come back to forums!

Userlevel 7
Badge +7

@matt.dickinson sounds good! I’ll be here if you have any questions about it later on 👍

Based upon your response that “Check Once a Day” doesnt work for custom fields, does “When a Specific Field Is Updated on an Opportunity” work with custom fields?  I am not able to move my Opportunities from one stage to another based upon a custom field check box being checked.  Is that the reason it wont move or potentially something else?

Userlevel 7
Badge +7

Hi @MPowell, thanks for posting! “When a Specific Field Is Updated on an Opportunity” does work with custom fields. I’m happy to help you with the workflow automation. Can you let me know:

  1. Do you already have a workflow automation rule that you’ve saved? If so, can you let me know what you named it? I can look up the logic you set for it.
  2. Can you give me more details on how exactly the stage should change when the checkbox is checked? Is it like, check this box to move this opportunity over to the Contract Finalized stage?

Hi Michelle,


Thank you for responding so quickly!!

 

Automation is “Move to Order Shipped”, I also have “Released”  both of these are designed to move the opportunity to another stage.

 

I have a custom field check box that I am trying to use to move an Opportunity from one stage to another.  I have the trigger field set to “When a specific field is updated on an Opportunity”, Filter conditions set to “Any of the conditions are met”, Action set to Update, Opportunity, Field #1 Pipeline, change to, Standard, my specific pipeline name. Field #2 Stage, change to, Standard, my specific stage name.

 

Does this level of automation not work for custom fields or am I missing the mark somewhere?

Userlevel 7
Badge +7

Thanks @MPowell! Let’s continue the conversation in our other thread.

Reply