JodieM.com.au

I’m a bit of a way on the path to becoming a Salesforce Consultant (not quite certified yet, but that’s just because I have to sit for the exam). I love Salesforce and what you can do with it, but then sometimes you just come unstuck over the smallest of details and think that it is the worst piece of software in the world.

I’m a database designer first and foremost, so I think in normalisation terms - I knew how to design databases before I even knew what normalisation was (“Oh, really, it has a name?”), and it irks me to de-normalise but I do understand that it is sometimes necessary for business reasons (yes, I’m not talking about data warehousing or noSQL data models either).

So when you come across something as simple as being able to report on every aspect of the data in your database, and find that it just can’t be done in Salesforce (and yet, it can be done quite easily in Access 2007+), then you need to re-think your design. (And while I’m in a ranty mood, I just can not believe that Salesforce have not even acknowledged this to be an issue let alone provided some options or even done anything to fix it!).

So, I’m coming to the conclusion that it just takes a bit of re-thinking about how you are going to achieve the requirements of your client in Salesforce and here are some tips I’ve thought about along the way:

• Challenge the Requirement – yep, I said it – requirements are NOT set in stone. Sometimes people may think their requirements are absolutely necessary, but if you can discuss it, throw some ideas around and maybe think of another way around it, then there might just be another way to achieve what they want.
• Look in the App Exchange – first up, see if someone else has had this problem and has done 90% of the work for you already. Even if it is paid app, it is probably going to be more robust than anything you can make and be more cost effective.
• Create Procedures rather than Solutions - this is sort of the cheapskates way around things – can’t afford that fantastic app that does all the things that you want? OK, then if we have to replace that with 5 steps, that are documented well, that the user has to do each time they need to do this, is that OK? Sometimes the answer may be yes, and sometimes you can use that thinking to justify the ROI of the App purchase price per month.
• Create a Formula Field - Formula fields in Salesforce are very powerful and can be used in many ways. Did you know that you can’t do a Global search for the text inside a lookup field (eg the Account Name on a Case)? No? Well it sucks that you can’t! So create a text formula field that references the Account.Name that is linked on the Case, make it not visible to the users, but it can still be used for search. I can also use a formula field for reporting – eg link the Account’s State field into the Case via a formula, so I can quickly summarise Cases by State.
• Create a Workflow Rule – Similar to formula fields, create a text field that is updated by a Field Update workflow to put data where you want it to be. Eg, you can’t do field level auditing on long text fields (it will tell you when the field changed and who changed it but not what it was changed from or to). So create a Audit field and populate it each time the long text field is changed. I have documented a solution on this blog.
• Create a Button or a Link – A lot can be accomplished with a button or a link. Yes, you could write a trigger to create a new record in Object X, but then you have to code it, write test classes for  it and maintain it. If it’s not that critical to the process, that it requires all that coding, then just create a button with a URL to prepopulate the fields on Object X. I even put things in the pre-population data that will cause an error so that the users need to stop and think about what they are creating this record for, and what else do they need to update. Other great links you can create are a link to Google Maps with a search query or a link to look up the ABR website based on the Account Name or ABN
• Add a Visualforce Page to a Standard Form – Creating Visualforce pages is easy and is not a big maintenance headache like coding is. Where I used this to great success is on a child detail record form – I wanted to show some of the field values from the header record (this is one of those infuriating things that should be easy out of the box, but it’s not). Create a simple visualforce page containing just those few fields, and style it to look like the regular detail page, and then add that visualforce page as a section in the standard form. You could even embed details from an external website into a Visualforce page (eg an image of the product the client has purchased from your web site).
• De-normalise – Yes, when it comes to the crunch, throw all your instincts out and de-normalise. I’m going to use this idea to get around the issue with reporting on multi select picklists.  I have a picklist with 50 values in it, all that need to be reported on. Based on the historic data most Cases only choose 2 or 3 of the available options, so I will create 3 fields, each based on a lookup to the list of options and then report on ‘Field1=XYZ or Field2=XYZ or Field3=XYZ’ to show all Cases that have chosen that option (using URLs to pre-populate the report parameters really helps with this too).
• Use External Systems – You know what? Salesforce is not perfect and may not be the one and only one system that you use. If I do want to do the reporting the way it has previously been done, I know that Access can do it, so I can just export the data to Access when that report needs to be done, and run the report in Access. Yes, it’s a bit more fiddly but there are some great export tools to make it easier.
• Change the Process – Yes, I know, quite unthinkable to have to change your processes to work with software – but this is not bespoke software designed especially for you. You will get 95% of the benefit out of using Salesforce so it’s OK to challenge the other 5% and possibly change your processes. It would be a shame to walk away from a Salesforce implementation because it just can’t do a few minor things in your procedures.
• “Suck it and See” – You might come up with a solution that is not 100% the way you want it to work but maybe it will be OK. Flag it as something to come back to in 6 or 12 months to see if it is still a major headache for the client or it is just something they have learned to live with or have even forgotten about. Sometimes a requirement that is thought to be a “must have” at the beginning could have come about due to a poorly designed old system or a lack of understanding of how Salesforce works – and by the time Salesforce is bedded into the organisation that requirement may not be as important as it was before. Just don’t forget to re-visit it and ask the question in 6 months time.

You may have noticed that none of these solutions involve coding. Yes coding is one way to tackle the problem, but hopefully it’s the last resort. It’s not that I don’t advocate coding as a solution, it is just expensive, time consuming and difficult to maintain. It is great if you are a large organisation and have an in-house team of developers who are going to maintain it and tweak it as your requirements change, but if that is the case, why are you using Salesforce?

Actually I think these ideas would work with any “platform” product that you evaluate for you or your client’s organisation, whether it be Microsoft CRM, SharePoint, Confluence or any ERP system. These systems will get you to a point – where you take it from there is up to you.

I might add some more things to this post in the comments as I think of them.

This is a quick post to share a tip that I found to create a field to track changes to another field. Sure you can do it with a trigger and some code, but this is a no-code work-around.

I found this idea on Salesforce Idea Exchange here and modified it as I needed to. The reason to use this idea is that the History Tracking feature only tells you that the field has changed, rather than the field value has changed from x to y.

If there are just a few key fields that you need to do full audit tracking on, then this may be an option. I have set this up on Cases for the custom Case Response field – the field that contains the information as to what was done to resolve this Case.

Steps:

• In Cases, Add a new long text formula field called Case Response History.
• Create a new Workflow Rule called Append Case Response History or similar to do a Field Update on the Case Response History field
• The formula for the Worflow is something along the lines of this:

"Last Modified: " + LastModifiedBy.FirstName & ' ' & LastModifiedBy.LastName  + TEXT(LastModifiedDate + 0.4167) + BR() + "------" + BR() +
PRIORVALUE( CaseResponse__c ) + BR() +
CaseResponseHistory__c

• You can play around with this formula, but I found this layout was good. The main components are that we are updating the Case Response History field with the previous value of the Case Response field, along with all the previous history that is already in the Case Response History field, then tagging it with the name and date of the update. I also have a line to divide each entry.
• The 0.4167 in the date formula converts UTC time to Sydney timezone. The default date format is a bit ugly, so you may want to do a lot more tweaking of this date field to change the format.
• The current value is in the Case Response field – this field only shows us what the previous value(s) were. The way I have set this up is that it shows the date that the current value was updated with the date/time and who updated it, in the history field. That way the next time the field is updated, that value will be listed with the date/time record of when it was updated. You need to test this out with a few updates to the field to see what I mean here (it makes sense to me, at least). If you don’t like this, then just modify the formula.
• Add the Case Response History field to the Cases Page Layout so you can see the audit history on the Case Record.

Notes:

• Beware: This is not entirely foolproof. The System Administrator or anyone with Modify All Data can edit the Audit field.
• This would probably be better as a hidden field that doesn’t take up space on the Cases form.