Accessing "inaccessible" Activity fields

posted Mar 2, 2011, 9:29 PM by Ezra Kenigsberg   [ updated Apr 27, 2011, 6:33 PM ]

#DataTipOfTheDay 2011-03-02

Q: "How do I use Due Date, Start Date, and End Date in Activity formula fields?"

A: For whatever reasons, those Date/Time fields aren't accessible in formulas. (Vote for it here: http://tinyurl.com/ActivityDate.) Here's the workaround:
  1. Create the following custom fields on Activity. I recommend (a) making each field read-only for all Profiles, and (b) not adding these fields to any Page Layouts.
    • Activity Date - Date/Time
    • End Date/Time - Date/Time
    • Start Date/Time - Date/Time
    • Type (as Text) - Text (255)*
  2. Add this Event Trigger, this Task Trigger, and this Test Class to your org.
  3. To populate existing records' custom fields: use Data Loader to
    • extract all Event IDs to a CSV, then run an update using that same file, and
    • extract all Task IDs to a CSV, then run an update using that same file.
That's it! You can now use those custom fields in formula fields.

* "Wait", you might be asking yourself, "where did this 'Type (as Text)' field come from?" Well, I noticed that the Activity Type field isn't available in Custom Report Types (see http://tinyurl.com/ActivityType). So I threw that one in there as a bonus.
Comments