Why

I’m terrible at mileage – I start every week with the best intentions, and then two months later I’m backtracking through my calendar trying to figure out where I was every day – not ideal.  This is a classic example of something that can be easily tracked in Salesforce, and entered via mobile while you are waiting for an elevator.

We are going to go through this exercise a few times, because I want to show you how to start with something simple to solve a business problem, and then develop it over time into a solution that is much more robust.

I’m using Mileage as my example, but this same thing could hold true for other items like:

  • Tracking your exercise if your routes are standardized
  • Tracking how many donuts you eat a week and subsequent calories if you are into that kind of thing
  • I’ll add any more that people think of and message me about

Step 1: Getting the information in there

Mileage1

Here is my very basic mileage page.

Location: Picklist that is maintained in the background.  5 entries currently exist, no default.

Date: Any date can be chosen, but I’ve set the default to TODAY().

Round Trip?: Default is checked, but I can uncheck if for some reason I only went one way

KMs: Formula field based on the picklist that populates a value

CASE(Location__c,
“Waterloo”, 200,
“Stoney Creek”, 100,
“Brampton”, 60,
“Downtown Office”, 40,
“Burlington”, 100,
0
)

Custom Mileage: Just in case I’m not going to one of the places in the picklist

Custom Mileage Details: The taxman will still want to know where I went, best to enter it here

System Information Fields: All you really need to know here is that I made the Name an Auto-number because I don’t care about it outside of having a unique record identifier.

Calculation

Assuming I do everything right, and don’t mess things up, the KMs for Trip will calculate perfectly every time based on the following formula:

IF(
ISPICKVAL(Location__c, “”),
Custom_Mileage__c ,
IF(
Round_Trip__c = TRUE,
KMs__c ,
KMs__c / 2
)
)
/*If no location has been set, use the custom mileage, otherwise use the mileage from the location field assuming it was a round trip. If it was one way, divide by 2 – does not apply to custom mileage*/

Note that you can put comments in formulas in Salesforce.  Very useful for the more complicated ones that could take you awhile to unwind if you had to come back to them.  In this case it’s pretty simple.

Now this all assumes that I don’t screw up.  If I enter anything in custom mileage, the location is completely ignored.  So what I needed was a few failsafes for when I’m not paying attention.

Failsafe 1: If a custom mileage, say where I went

This is for the taxman, they will want to know.  Simple validation rule

NOT(ISBLANK( Custom_Mileage__c ))
&&
ISBLANK( Custom_Mileage_Notes__c )

CustomError

Failsafe 2: Cannot have both a location listed, and a custom mileage

AND(
NOT(ISPICKVAL( Location__c , “”)),
NOT(ISBLANK(Custom_Mileage__c))
)

Custom Mile22

 

So those will help keep me in line and make sure I don’t screw up.

Note that I could have combined these into a single validation rule but that would have stuck me with only a single error message.  If the reasons for failure cannot be explained with the same message, the better practice is to split the validation rules so that you can clearly articulate to the end user why you are failing what they are trying to do.

Mobile

Without having to make a single change for mobile, here’s how I would be entering the exact same thing on my phone:

MileageSF1

All the same fields are there.  If I found that from my phone I was only ever entering “Standard” locations because I would have to go back and check mileage for Custom locations, I can adjust that through publisher actions and we’ll address that use case in another post.

Summary

So I’m calling this iteration 1.  It works, I can use it, but it has limitations.  There is nothing fancy, just a few formulas, and a few checks and balances.

1. Scaleability – what if I have lots of sites to go to?  I don’t want to keep adjusting picklist values AND the corresponding formula.  It could get unwieldy very quickly.

2. Over time I may stop going to places but since the field is a formula I have to be very careful about removing values.  I don’t have a great audit trail.

3. I haven’t figured out how to report on it yet.

Over the next few posts we’ll look at different ways to improve this, discuss the pros and cons of different solutions, and hopefully end up on something that can be considered both optimal for us, and worthy of providing as a business solution.

Share Button