Take the 2-minute tour ×
Salesforce Stack Exchange is a question and answer site for Salesforce administrators, implementation experts, developers and anybody in-between. It's 100% free, no registration required.

I have created a "mileage expense" formula field which calculated the "miles" field by the mileage reimbursement rate - $.56 - hard coded into the formula.

Mileage reimbursement rates change at least annually based on the federal allowance for business travel.

My problem is two-fold:

  1. Can I create a "constant" field to which I can refer in the formula for the "mileage expense" field? (instead of hard coding the value into the formula)

  2. Can the formula be structured to "look up" the mileage rate "constant" and then leave the field's calculated value unchanged when the "constant" is changed? In other words, I do not want the calculated field value for 2014 mileage expense to be recalculated when the "constant" is changed for the 2015 tax year?

If that is not possible, I guess I would have to create a lookup matrix with the mileage rates based on date range, then calculate the field based on the rate in effect at the time of the travel. (And how would I do that?)

Thanks.

share|improve this question

3 Answers 3

You need to use a hierarchy custom setting to hold this constant. You can only access hierarchy custom settings in formulas not List type custom settings. This will allow you to update the mileage rate constant in the custom setting and not have to adjust your formula at all should the Mileage rate change.

You can refer to your custom setting in your formula like so...

$Setup.Your_Custom_setting__c.Your_Mileage_Rate__c * Miles__c

Since you want a constant for a specific year then you can create multiple custom fields on the custom setting for each time period. You can create currency custom fields just as you could on a custom object. If you wanted to do it by year, you could do something like this

CASE(
     YEAR(DATEVALUE(CreatedDate)),
     2013, $Setup.Your_Custom_Setting__c.Your_2013_Mileage_Rate__c * Miles__c,
     2014, $Setup.Your_Custom_Setting__c.Your_2014_Mileage_Rate__c * Miles__c,
     20XX, $Setup.Your_Custom_Setting__c.Your_20XX_Mileage_Rate__c * Miles__c,
     0.00
)

This assumes that you have a custom currency field on your custom setting for each time period you want to use. This also assumes you want to use the CreatedDate of the record to determine which mileage value to use, but you can use any DateTime or Date value if you would like.

Take a look more at custom hettings in some of these links

https://help.salesforce.com/HTViewHelpDoc?id=cs_accessing.htm&language=en_US

https://help.salesforce.com/HTViewHelpDoc?id=cs_about.htm&language=en_US

http://blog.jeffdouglas.com/2010/02/08/using-hierarchy-custom-settings-in-salesforce-com/

share|improve this answer
    
Curious as to why the down vote without an explanation??? –  sfdc_ninja Sep 16 at 13:57

If you want the mileage rate to be constant on a record even if it changes after the record was created; you never go back and adjust the rate after the fact; and your Salesforce edition supports workflows, you can use a workflow field update instead of a formula field.

Formula field values are always calculated at runtime, which means that you would need to track the mileage from a given date/year (as suggested in sfdc_ninja's solution) and return the correct value for that year. Because only hierarchy custom settings are supported in formulas, you would need to create a field on your custom setting for each year, and you would need to then add the custom setting check to your formula each time you added a new field.

A field update will update a regular standard or custom field only when the criteria are met to run the workflow rule. If instead you create a "Mileage Reimbursement" field (type currency), you can make it read only and then create a workflow rule that runs only when the record is created. You would still need a hierarchical custom setting with a "Reimbursement Rate" field, but you wouldn't need to have historical values -- just update that one field when it changes. Your formula for the workflow field update would still be Miles__c * $Setup.Your_Custom_Setting__c.Reimbursement_Rate__c, but never needs to change.

This wouldn't work if you have people entering records for dates that occurred in the past (e.g. submitting their expenses two weeks late, during which the rate changed) but neither will setting it by year if the rate changes on arbitrary dates, sometimes more than once a year -- you would need to have start and end date ranges for each rate, and your formula would exceed compile size limits pretty quickly.

share|improve this answer

My recommendation is to create a custom setting like the above answer or create a custom object to contain business values. The latter would work by creating records for specific years and have the users lookup to the record of the year needed. Once the lookup is indicated, then the values from that record will pass into the formula fields you've built on your current object. This approach means you don't have to create a ton of fields; simply create a new record with a new value. Plus you can offload the work onto the Finance team or whoever should maintain this data and reduce IT dependency.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.