Have you ever noticed the difference between the Member First Associated field on the Contact’s Campaign History Related list is a Date/Time field, but if you were to reference the Campaign Member’s First Associated Field on a report, it is just a date field? We want to be able to compare in a report which campaign member was first associated (read: Created) if they are on the same dates. This question came up today, and thought I’d quickly share how we solved this issue. (Thanks for the article idea, Michelle!). This post will show an easy way to run as report of the Campaign Member First Associated field as a Date/Time Field.
Before we begin, here is an example of the Campaign Member First Associated field on the Campaign History Related List that is a Date/Time field:
And here is the same Campaign Member First Associated Date field on a report that is only showing as a Date field:
This is very odd! Let’s quickly fix this by creating a new field. We’re going to look up the date/timestamp of the creation of this Campaign Member being associated with the Contact.
Note: This post is a follow up to the Campaign Member First Associated Timestamp article, where we go over that the Campaign Member First Associated Date is actually the “Created Date” of the Campaign Member associated with a Contact. If you’re confused why that is, check the article out.
How To Run a Report of the Campaign Member First Associated Field with Date/Time
In the above article, we know that the Campaign Member First Associated field is actually the ‘Created Date’ of the Campaign Member record associated to the Contact. The problem is that the Created Date is also not easily reference-able on a Campaign Member report for some reason. I don’t know why this is the case. To be able to run a report of the Date/Time field of the Campaign Member First Associated date, we need to create a formula field referencing the created date and displaying that as a date/time field.
Creating a Date/Time Formula Field for Campaign Member First Associated
First, let’s create the field. Select “New Field” from the Campaign Member object in Salesforce’s Setup. Let’s name it “Member First Assoc. Date”. Select the type to be Formula, and Date/Time as the output. The formula is simply the “Created Date” of the Campaign Member. In the Formula Editor, you can either look the field’s API up, or just type “CreatedDate”. Now, let’s check the syntax, and then save the field if there are no errors. On the next step, you can choose to add this to your Campaign Member Layout if you’d like. If you only want to have it available in reporting, there’s nothing you need to do to make it available. Last step is to save the field. You should now be greeted with a new field record that looks like the below screenshot:
Now, when you run a standard Campaign Member report, you will see that the Campaign Member First Assoc. Date is correctly pulling the Date/Time field. You can now run a report to compare different Campaign Members First Associated Date and view them as a Date/Time fields, seeing which Campaign Members were created first, even if created on the same day,:
I don’t know why this is one of the weird quirks of Salesforce, but believe it was a legacy feature. I hope this article helped you if you had the same confusion! As always, leave me a note in the comments below.