Skip to main content

[en] Tracking historical changes to an amount field

[en] Properly tracking historical changes to your amount field is a key part to getting the most out of FunnelCake. It's what enables you to understand how pipeline has changed over time in reports like Pipeline Flow, or see how an opportunity's value has changed over its lifecycle.

[en] There are typically three ways that a Salesforce instance is configured that can impact how amount history is tracked:

  1. [en] Using the standard Amount field, with or without multiple currencies.

  2. [en] Using a custom amount field with one currency.

  3. [en] Using a custom amount field with multiple currencies.

[en] The sections below look at how to properly set up history tracking for each of these scenarios.

[en] Using the standard amount field, with or without multiple currencies

[en] Salesforce provides history tracking on the standard amount field out of the box. Their history tracking on the standard amount field also includes the ability to know the currency type (ex: USD, CAD, EUR) at the time that the history was recorded. Because the historical value and currency are known, FunnelCake can deduce the historical amount and display it in reporting as your organization's currency.

[en] If you're using the standard amount field, FunnelCake reporting around historical amounts will work out of the box without any extra setup.

[en] Using a custom amount field with one currency

注記

[en] If you don't use multiple currencies today but might in the future, and want to ensure your history tracking is future proof, please see the next section.

[en] If you're using a custom amount field for reporting in FunnelCake, and your organization does not use multiple currencies on opportunities, then you'll need to consider the below.

[en] Salesforce doesn't track historical changes on custom fields by default. You'll need to turn on field history for your custom amount field. To turn on field history:

  • [en] Go to Setup → Object Manager → Opportunity → Fields & Relationships → Set History Tracking.

[en] Once field history is on, the amount value for your custom field will be stored as a history record. The record will represent the value that the opportunity had in the custom amount field at the time that it was changed.

[en] This history record keeps a reference to the value of the field but does not keep a reference to the currency type that the opportunity had at the time that it was changed.

注記

[en] This is different from the standard Salesforce amount field, which does keep a reference to both the value and currency type of the opportunity!

[en] If you're only using a single currency, there aren't any impacts of not knowing the currency type. For example, if your organization's currency is CAD, FunnelCake will treat all of the historical records as a CAD value.

[en] However, if you're using multiple currencies there are impacts to not knowing the currency type. Please see the next section for more.

[en] Using a custom amount field with multiple currencies

[en] If you're using a custom amount field in FunnelCake, and your organization uses multiple currencies, then you'll need to look at creating a new custom field specifically for FunnelCake that will track changes to the amount field.

[en] Why do I need another field beyond my current custom amount field?

[en] When you turn on history tracking for a custom currency field, Salesforce will record changes to the value of that currency field but will not keep a reference to the currency type of that value. This is different than Salesforce's own standard amount field, which keeps a reference to both value and currency type.

[en] For example

[en] If opportunity Alpha was worth $10,000 USD and changed to $15,000 USD, its history record would be written as:

         Historical value: 10,000


          New value: 15,000

[en] If opportunity Bravo was worth $10,000 CAD and changed to $15,000 CAD, its history record would be written as:

         Historical value: 10,000



          New value: 15,000

[en] When you look to report on these changes in FunnelCake (which uses your organization's currency — let's say that's USD), FunnelCake is unable to tell that opportunity Bravo's values are in CAD and need to be converted to USD before being displayed in reporting. It's unable to tell because the opportunity's history record does not include the currency type.

[en] In order to fix this, history records for your organization's custom amount field need to be standardized to your organization's reporting currency before being recorded. In the example above, this means that opportunity Bravo's historical value of 10,000 would be recorded as 7,263.90 for display as $7,263.90 USD (at the current CAD/USD exchange rate)

[en] How to set this up

[en] For history records to be recorded in a standardized currency that reflects your organization's currency, we're going to need to leverage a new custom field and Salesforce workflows.

  1. [en] Create a new custom number field.

    1. [en] Go to Setup → Object Manager → Opportunity → Fields & Relationships > New.

    2. [en] Select Number for the Data Type, and click Next.

      注記

      [en] We're using a number field and not a currency field because we don't want an opportunity's currency type to impact the amount value that will go in this field.

    3. [en] Enter a Field Label. The Field Label is what you will see in FunnelCake wherever the amount value is listed.

    4. [en] Set the length to 16 and decimal places to 2. This will let us treat the number as an amount with two decimal places for cents.

    5. [en] The field does not need to be visible or added to any page layouts for anyone. It will be used in the background.

  2. [en] Turn on Opportunity Field History for this new field.

    1. [en] Go to Setup → Object Manager → Opportunity → Fields & Relationships → Set History Tracking.

  3. [en] Create a Workflow Action that will convert the original custom amount field into the new custom number field, with its value formatted to your organization's currency.

    1. [en] Go to Setup → Process Automation → Workflow Actions → Field Updates.

    2. [en] Click New Field Update.

    3. [en] Enter a Name and Description.

      [en] For example:

      [en] Name: Convert Opp Currency to Org Currency.

      [en] Description: Converts the value in our custom amount field from the opportunity's currency into the organization's currency, and saves it in a new field. This is used by FunnelCake for tracking history on our custom amount field.

    4. [en] Set the Object to Opportunity.

    5. [en] Set the Field to Update to Opportunity and [name of your new custom number field].

      注記

      [en] If you used the name in the example, that's the field called "Amount generated by Workflow for FunnelCake history tracking"

    6. [en] Under Specify New Field Value and Number Options, select Use a formula to set the new value and enter the following formula:

      [en] Custom_Amount_Field__C / CURRENCYRATE(TEXT(CurrencyIsoCode))

      [en] where Custom_Amount_Field__C is the original custom currency field you have that FunnelCake cannot track multi-currency history on.

      [en] To breakdown what this formula is doing:

      [en] Get the value of the opportunity's custom currency field (Custom_Amount_Field__C).

      [en] Divide that by the exchange rate. Where CURRENCYRATE is the function for converting a currency code, and TEXT(CurrencyIsoCode) is the text value of the opportunity's currency code (ex: CAD or USD).

    7. [en] Click Save.

  4. [en] Create a Workflow Rule for the Workflow Action you just created.

    1. [en] Go to Setup → Process Automation → Workflow Rules.

    2. [en] Click New Rule.

    3. [en] Select Opportunity and click Next.

    4. [en] Set a Rule Name (For example: "Opportunity was created or Edited").

    5. [en] Under Evaluation Criteria, select created, and every time it's edited.

    6. [en] Under Rule Criteria select run this rule if the formula evaluated to true.

    7. [en] In the text box enter true, and click Check Syntax to make sure no errors were found.

    8. [en] Click Save & Next.

    9. [en] Under Immediate Workflow Actions click Add Workflow Action and select Select Existing Action.

    10. [en] Under Search, select Field Update.

    11. [en] In the Available Actions box click the action you made in step 3, and then click the Add ▶ button to move it into Selected Actions.

    12. [en] Click Save.

    13. [en] Click Done.

    14. [en] You should now be on the page for your new Workflow Rule. Click Activate.

[en] You've created a workflow that will stamp your new customer number field with the custom amount value that's formatted in your organization's currency. And because you turned on history tracking for your custom number field, FunnelCake will be able to report on historical changes to the amount field and display them in your organization's currency.

[en] The last step is reaching out to your customer success manager with the name of your custom number field so that we can configure your FunnelCake to use it.