+ Start a Discussion
Cody Bustamante 9Cody Bustamante 9 

Need a formula that updates a field when someone moves from one lead status to the next...

When a lead moves from one status "Open" to "Marketing-Qualified" I want a rule to auto-populate "MQL Last Activity" and "MQL Last Activity Campaign" fields based on whatever their last activity and last activity campaign was.

The status we have are:
Open
MQL
SIL
Law Firm Nurturing
SAL
SQL
Opportunity
But the only fields I need to be updated are
MQL
SAL
SQL
Opportunity
One thing I also need to ensure happens is if someone skips a status (i.e. MQL straight to SQL) that the current last activity is also updated for SAL. I need this for every combination that can happen when a status is skipped. 

How might I do this? I was told to use Visual Workflow, but I have only completed the Trailhead and that's as much as I know, can someone help me figure this one out? 
Best Answer chosen by Cody Bustamante 9
Gaurav HandooGaurav Handoo
Hi Cody

Based on our discussion, your requirements were following:

Hope the below screenshot of flow works for you:
User-added image

Following were the steps followed for Flow:
  1. Decision box to check if the values to be populated are available or not (Input will be provided from Process Builder).
    1. If not, default values (filled by default) were assigned to the variable being used for capturing required values. Prospect Source and Prospect Source campaign filled by default, while Last Activity and Last Activity Campaign during the lead status movement.
  2. Next Decision Box checks whether lead lead got converted or not (Input will be provided by Process Builder). If it got converted, whether the Opportunity was generated or not.
  3. In case Lead didn't get converted, another decision box to confirm status movement, by confirming the current status and previous status (both will be provided by Process Builder). Based on the values provided, we can assume, that if one of the status (from the ideal situation) is missed, its corresponding fields wouldn't have been filled
    1. Assign your variable (For Last Activity and Last Activity Campaign) to corresponding fields of Lead's Sobject Variable. You would need an Sobject for lead created before this.
    2. Assign the Lead Id (input from Process Builder) to the Sobject's Id Field. Is done in the same assignment element as above.
  4. In case Lead got converted with Opportunity Created, another decision box to confirm which fields (corresponding to your status fields) had data filled (Pass all fields to the flow through Process Builder).
    1. Assign your variable (For Last Activity and Last Activity Campaign) to corresponding fields of Lead's Sobject Variable. You would need an Sobject for lead created before this.
    2. Assign the Lead Id (input from Process Builder) to the Sobject's Id Field. Is done in the same assignment element as above.
  5. Assign the Sobject to Sobject Collection
  6. Run a Fast Update.
 
    The Process Builder in this case, needs to be very precise about conditions, because you have other Lead Status values which we don't want to track. Following are the steps to reflect in your Process Builder:
    1. Criteria:
      1. First Criteria:
        1. Converted EQUALS BOOLEAN False
        2. Status (select your corresponding field) IS CHANGED BOOLEAN True
        3. Status (select your corresponding field) EQUALS SAL, SQL. You would need to add them in OR Condition.
      2. Second Criteria:
        1. Converted EQUALS BOOLEAN True
        2. ConvertedOpportunityId ISNULL BOOLEAN False
    2. For actions, pass all the values to all the variables. Variables should be named as per the field values being passed, to avoid confusion in terms of assignment.

    Hope this works for you.

    Cheers!!

    Gaurav

    All Answers

    NagendraNagendra (Salesforce Developers) 
    Hi Cody,

    You won't be able to do that with a formula. Process Builder also won't work because you can't iterate over a series of child records and determine what data to roll back up to the parent. You could do this with Flow if you are familiar with Flow or you could work with a developer to build this functionality. If you go the dev route make sure they build you some kind of UI so you can modify the logic going forward as needed. 

    Thanks,
    Nagendra
    Gaurav HandooGaurav Handoo
    Hi Cody

    I believe your solution might require some work and would be extensive, could you connect with me over my email viz gaurav.handoo@gmail.com, so that I can guide you better.

    Cheers!!

    Gaurav
    Gaurav HandooGaurav Handoo
    Hi Cody

    Based on our discussion, your requirements were following:

    Hope the below screenshot of flow works for you:
    User-added image

    Following were the steps followed for Flow:
    1. Decision box to check if the values to be populated are available or not (Input will be provided from Process Builder).
      1. If not, default values (filled by default) were assigned to the variable being used for capturing required values. Prospect Source and Prospect Source campaign filled by default, while Last Activity and Last Activity Campaign during the lead status movement.
    2. Next Decision Box checks whether lead lead got converted or not (Input will be provided by Process Builder). If it got converted, whether the Opportunity was generated or not.
    3. In case Lead didn't get converted, another decision box to confirm status movement, by confirming the current status and previous status (both will be provided by Process Builder). Based on the values provided, we can assume, that if one of the status (from the ideal situation) is missed, its corresponding fields wouldn't have been filled
      1. Assign your variable (For Last Activity and Last Activity Campaign) to corresponding fields of Lead's Sobject Variable. You would need an Sobject for lead created before this.
      2. Assign the Lead Id (input from Process Builder) to the Sobject's Id Field. Is done in the same assignment element as above.
    4. In case Lead got converted with Opportunity Created, another decision box to confirm which fields (corresponding to your status fields) had data filled (Pass all fields to the flow through Process Builder).
      1. Assign your variable (For Last Activity and Last Activity Campaign) to corresponding fields of Lead's Sobject Variable. You would need an Sobject for lead created before this.
      2. Assign the Lead Id (input from Process Builder) to the Sobject's Id Field. Is done in the same assignment element as above.
    5. Assign the Sobject to Sobject Collection
    6. Run a Fast Update.
     
      The Process Builder in this case, needs to be very precise about conditions, because you have other Lead Status values which we don't want to track. Following are the steps to reflect in your Process Builder:
      1. Criteria:
        1. First Criteria:
          1. Converted EQUALS BOOLEAN False
          2. Status (select your corresponding field) IS CHANGED BOOLEAN True
          3. Status (select your corresponding field) EQUALS SAL, SQL. You would need to add them in OR Condition.
        2. Second Criteria:
          1. Converted EQUALS BOOLEAN True
          2. ConvertedOpportunityId ISNULL BOOLEAN False
      2. For actions, pass all the values to all the variables. Variables should be named as per the field values being passed, to avoid confusion in terms of assignment.

      Hope this works for you.

      Cheers!!

      Gaurav
      This was selected as the best answer
      Cody Bustamante 9Cody Bustamante 9
      Wow, fantastic Gaurav! Thank you!