If my title is unclear, I’m building a Wine App for my own use to track the wineries my wife and I visit, the wine we buy, and the wine we drink. So far I’ve covered off my initial planning in Part 1, and the changes I had to make in Part 2 to adjust for some limitations. I want to get this thing off the ground so that I can get everything into a system, so what I’ve been working on is getting all the fields ready, and any other setup that I’ve had to work on. In this post I want to talk a little bit about creating fields for other reasons – ie not just for the page layout. I want people to understand that a lot of bringing value is showing the business what they don’t know, and fields only for reporting, or only for related lists are a great way to do this.
I often find that the easiest way to get objects up and running is to pick a single, relatively generic example to start building out the fields. Too often I see everyone get in a room, discuss how many 100s of fields are needed to satisfy all nuances for that company – because as you know every company is special – and then end up in a situation where none of the fields are used.
Current example? Imagine you get all the car companies in a room to discuss what fields are required for the “global car alliance” or whatever. This would have gone great in 1920 when everything was the same and cars came in black. Imagine today when everyone agrees that you need a field called “Exhaust”, and every time something like that comes up Elon Musk keeps putting up his hand asking when we are going to talk about electric sports cars. Ever had a lawyer in requirements gathering sessions? Yep, kind of like that.
So instead, pick a generic example and get things started that way. I’m going to use a winery near and dear to my heart called Kacaba which is where my wife and I got engaged a few years ago. We also used their wine at our wedding, and have a few vintage bottles tucked away down in our cellar for a future anniversary.
This is a record type on Accounts and I don’t want to bog it down with too much stuff. I want to know where it is, what wines it is known for, whether I can eat there, and whether we can bring our dog. So here is the layout:
It looks like it should fit my needs. Simple, to the point, and has the information I need. A few notes:
- Wines will be a related list as discussed in Part 1. So we’ll be getting to them.
- Notice that I did Known For as a set of picklists. So this is something that people often make the mistake of doing as a multi-select picklist. Do you know why I didn’t? Because they suck. Ever tried reporting on them? Validating them? Triggering Workflow off of them? Using them? Save yourself the trouble. If you still don’t believe me, paste this into google and read through some of the issues people have had: site:Success.salesforce.com, “Multi-select picklist” suck
This is probably the one that will change the most over time. I’m curious as to how much I’m going to track reviews and different aspects of the wine itself. I think that this will grow as my cellar grows, and I need more ways to segregate the wines to find what I want at any given point in time. So going back to business advice again – start small. You can always add more fields later. The more you spend talking about specific fields in a meeting, the less time you have to discuss important things like data model, architecture, workflows / validation rules, and data quality. Get in a big room, and all people want to talk about is fields that are important to them. Keep pushing most of the field discussions aside and focus on the bigger picture before it’s too late.
Do I care if Person A thinks a picklist needs 10 values and another person is arguing for 12 values? Pay me enough to sit there and I’ll care about their dog’s last teeth cleaning, but realistically they can sort that stuff out without me needing to be there.
A few things to note here:
- I added a Winery if not Account field on here. This is primarily for cases where we buy one-off bottles when in the store or overseas, and I have no real use for tracking the winery itself. In that case I can free-form text it. That being said, there are a few things that should be done at the same time
- A validation rule so that you can’t leave them both blank:
ISBLANK( Winery_if_not_Account__c )
- A formula field for reporting so that you aren’t dealing with two fields. These really aren’t used enough. People have trouble conceptualizing fields that aren’t on the page layout. In this case I just want one unified field that can be brought back in reports. All it’s doing is saying that if the Account is populated use that, otherwise you the free-form text.
ISBLANK( Winery__c ),
- *This type of field I actually did the first day on a recent engagement where they were about to write a trigger to accomplish the same thing. Know enough about formula fields and you may just save the company a few days of developer time. Pretty good little first day from my perspective
- A validation rule so that you can’t leave them both blank:
- Purchases and Reviews are child objects. Don’t underestimate the importance of getting your related lists right. The amount of times I’ve heard business users say they didn’t even know you could change them. An example that I’ve started using all the time now is the HYPERLINK formula. Looking at my image above, note that I have the auto-number “Wine Purchase Name” listed as a link, but I also created my own link that is much more user friendly. I was able to just call it purchase, and all I needed was the HYPERLINK formula and a purchase date field. My formula could not have been easier, and now I can get rid of the auto-number field altogether. These are a great way to create meaningful fields and values, and they aren’t necessarily thought of by the business. This is a Wine Purchase field that I’ll only ever use on the related list on the Wine object. I left the auto-number field in there for illustrative purposes only. Most people leave the auto-number on the related list (and annoy end users) because they don’t know how to replicate the Go to record functionality that goes along with it.
- HYPERLINK(Id, “Bought On: “& TEXT(Purchase_Date__c ))
- *Trust me, they are easy and make you look really smart. This is a good case where it doesn’t make sense for the user to type in a name, because it’s a junction (they already have two names to deal with), but an auto-number doesn’t mean much to them either.
This is my junction object that I spoke about in the first few parts of my Wine App. Sam wrote a great comment at the end of Part 2 explaining how he would handle the cellar and saying that it could be a lookup or even a picklist value, that I could get all my desired roll-up reporting out through regular reports which is something I agree with completely, I just can’t decide if I want to break away or leave it as is. At this point I don’t have to change it, because I actually think my Activity logging for bottles consumed makes more sense to use than another custom object, but I definitely see his point and may change it, even next week. But for now I’ll leave it as master-detail. Either way it doesn’t affect what I’m doing right now, and I do feel that the logical trail could be argued either way.
So this object is really about showing some of the hidden logic that I’ve built into this object. I’ve already covered how to use Declarative Rollup Summaries to track the Bottles Consumed, and along with that I also added in some more logic based fields:
Remaining Inventory: Formula of Quantity_Purchased__c – Total_Consumed__c
Years Since Purchase: Formula of YEAR(TODAY()) – YEAR( Purchase_Date__c )
I’m not going to write about fields on the Cellar because I haven’t fully decided yet whether to stay with MD or go with a Lookup as Sam recommended.
I’m also not going to write about reviews because I’m not sure how often I’ll use them yet, and how easily it will be to parse the data. For now I’ve just got a name and a Rich Text field.
The message here is don’t be scared to create fields and not put them on the page layout, but instead just have them for reporting. Another popular one is a “report filter” field that is something like this
YEAR(CloseDate) = 2014,
which is basically a quick way of creating a field that will only display 2014 Opportunity Amounts, instead of filtering the whole report. A knowledgable business user will say sure, but I can use fancy filters for the same thing, which is partially true, until you need to run a formula field to compare 2014 to 2015 revenues.
Try a few of these fields out, and think about ways they can improve your related lists and your reporting moving forward. They aren’t hard to set up and/or maintain, you just need to think of them.
One more piece of advice, don’t call them the same name as another field already there. Nothing worse than looking at a report type and seeing three fields called Account/
Next time I’m going to look at actually loading some data and trying out a few different loading options to try comparing a few options.