Chris Hudson

Tag: salesforce formulas

  • Campaign Member First Associated Date on Records

    If you had an issue finding the member first associated field on the campaign member, you are not alone. Here, the campaign member first associated date field is an available field column in the Campaign History related list of a Contact or Lead record:
    Salesforce Campaign Member First Associated Date Field

    However, if you were to try and reference this field in a formula, workflow rule, etc, it would not be not available in the options. For example, please see the below – this is a screenshot showing the picklist in the formula helper on the Campaign Member object:

    Available fields on the salesforce campaign member
    There is no field on the Campaign Member object with the label “Member First Associated”. Where did it go?

    How can this be referenced in the related list, but not reference-able anywhere else? As it turns out, this is because the Column header label in the related list is actually referencing another field.

    As it turns out, the Member First Associated Field is really just the Created Date of the campaign member!

    It makes sense when you think about it. A Campaign Member is first associated when the record has been created on the Contact or Lead record, as referenced through the related list. Let’s see how we can use it in a formula:

    Using the Campaign Member First Associated Date field in a Formula

    If you wanted to reference the Member First Associated in a field of a formula, it is very easy to do. Note: The “Created Date” field is a DATETIME field, and you may need to convert the DateTime field to a Date value if you are using it with another Date field like in the below formula.

    Using the Campaign Member Created Date field as a DateTime value to Date Value

    In the above, we have a simple IF statement seeing if the Effective Date field on the Account related to the contact is greater or equal to the creation date of the campaign member of the related Contact. If the effective date is greater or equal to the campaign member first associated date, we’ll have the formula say No, otherwise it’s Yes. What this formula is seeing is if the Contact was a member of a campaign on before or after the account became a customer. As a reminder, we use DATEVALUE() instead of DATE() because it will correctly convert it to a date field.

    I hope this article helped clear out some confusion of the Campaign Member First Associated field! Would love to hear your thoughts in the comments below, if this helped you out or not!

  • Two Ways to Populate a Date Field Based on a Checkbox in Salesforce

    There are a few ways that you are able to implement timestamping a date field based off of checkbox in Salesforce. This article will go into two different ways to solve this requirement, and in between will discuss the trade-offs between using each potential method.

    Automation of timestamping has a variety of uses. This process can be used to track different stages of an opportunity’s lifecycle, figure out when a field was last updated, setting a future task, and many more use-cases.

    From a timestamp, you are able to report on the exact date a closed opportunity was created, qualified, and when a user sent out the quotation. You can also use the timestamp to set future follow up date on the field. Lastly, you can figure out the last time the field was updated, and follow back up on those Accounts/Contacts, or Opportunities that may have become stagnant.

    How to Timestamp a Date Field in Salesforce

    The two ways to timestamp a date field in Salesforce are:

    1. Creating a formula field that updates to now or today.
    2. Using a workflow rule and a field update.

    In the first section, we’ll go into how to use a formula field to manage this requirement.

    Method #1: Creating a Formula Field to Timestamp a Date

    The first method of timestamping a date field is to make a formula field that will return a date. This is assuming you’ve already created the Checkbox field. Let’s create our new field, and set the field type to “formula”:

    Step one of creating a new Salesforce Formula Field

    We’ll want the formula to return the output as a “Date”. Don’t forget to add a field label/name!Step Two of creating a new Salesforce Formula Field

    In this field, you’ll want to set it up with the following formula:

    IF( checkbox_field__c = TRUE, NOW(), NULL)

    Where the checkbox_field__c is the name of the checkbox field you have.

    Step Two of creating a new Salesforce Formula Field to timestamp a date field

    What the above formula does is see if the Checkbox we created is checked (or True in the above), and it will make the formula return the date as date “Now”. This method is great as it is relatively straightforward formula to timestamp the date, and unchecking the Checkbox Field will remove the date.

    What if you wanted to keep the date until removed by a System Admininistrator or other user? That is the one caveat the formula field has – if  the checkbox is checked, the formula will give (and keep) whenever NOW was. On the other hand, if the checkbox moves to unchecked, it will remove the date field in the formula. In the next method, using a field update will keep the date until removed by a user.

    In the next section, we’ll go into how to use a workflow rule and field update to manage our requirement of timestamping a date field.

    Method 2: Using a Workflow Rule & Field Update to Timestamp a Date

    Using a workflow rule & field update that can timestamp a date field is a little bit more work to implement, but does not have the draw back of the formula method. Removing the checkbox will cause the formula to not return ‘true’, and can erase valuable historical data down the line.

    First, we’ll just create a ‘date’ field. Again, we’re assuming that the Checkbox field is already created:

    Next, we’ll want to create a workflow rule. We start this by selecting the object that we want the rule to apply to. In our case, it is the Account object.

    The next step is to give it a name, and decide the evaluation criteria.

    Part of the rule criteria process is seeing that the checkbox we created, “My Checkbox” turns true:

    What a workflow rule does is that it takes a look at conditions, and if true, provides actions. These actions can be sending emails, updating fields, creating tasks, or creating an outbound message. In our case, we’ll want to create a new field update. What this will do is tell the workflow rule to update the field that we will define.

    Here we tell the new Field update to timestamp the Workflow Date field with the formula, which is just “Now”:

    Once that has been created, let’s activate the workflow rule:

     

    Now that the workflow rule & field update have been built and activated,  let’s just verify that it works by checking the box on the Account page:

    Congrats, it works!

    Differences and Use Cases:

    Each of the above methods are two distinct ways that can have varying use cases that are different in each circumstance. There is actually a third and final way to solve this requirement, and that follows the KISS Principle. Keep It Simple. After learning about the requirement from your end user, sometimes all that is needed is one person or a team of people to take ownership of the dates. We solve this by simply creating a date field, and locking it down to specific User Profiles or lock it down completely, and provide access through permission sets.

    Which method do you use to timestamp date fields? Let me know in the comments below!