Comments Off on Rollup Picklists to Master Object [no Apex required]

Rollup Picklists to Master Object [no Apex required]

Posted November 10th, 2009 in Tips & Tricks by John Coppedge

How can you translate text from a related list into a field on your master object?

image

Magic.  Pure and simple.  That, and formulas.  Lots of formulas.  We’ll call it Formula Magic.

This works by assigning each entry within the picklist to a range of digits, using a roll-up summary formula to compile the totals, and then using remainder calculations to determine type.

Here’s how to set it up:

Continue Reading »

Comments Off on Batch find and replace Salesforce fields using Excel [Tips & Tricks]

Batch find and replace Salesforce fields using Excel [Tips & Tricks]

Posted August 6th, 2009 in Tips & Tricks by John Coppedge

 

Problem

Recently I needed to migrate opportunities from our legacy system into Salesforce.  Being no stranger to the data loader, this is normally not a problem.  The tricky part was that the opportunity needed to be linked to an external ID field on the account instead of the account ID, which cannot be accomplished with the data loader.

Solution

1. Run a report that contains the external id field (Enrollment Owner ID) and Account ID (filtering out where enrollment IDs are not present).

image

Export the report.

2.  Load my Salesforce Find and Replace Template (Excel 2007 required – may work w/2003 & compatibility pack).

Paste in the results of the above report into the left two columns, starting in the yellow section (in this case, the order of the fields needed to be reversed).

Then paste in the External IDs that you are looking to match in the clear column on the right.  Select the Salesforce ID formula (E5) and double click the bottom right plus sign to extend the formula throughout the entire worksheet.

Column E will now output the corresponding ID from column B, when fields D and A match.  The matching algorithm will only find one match, so it is best to have a duplicate free list of external IDs.

image

You may need to convert the fields to numeric or text values depending on your external ID.  For this function to work correctly for me, I had to convert these to numbers:

image

3.  Take the matched Salesforce IDs paste them into a new column in your import file.

image

A quick paste into notepad, select all, cut, and into the worksheet it goes (to remove excel formatting), nicely matched.

image

Credit to Mr Excel for the formula magic.

 

Update

Before I got the chance to finish this post, I needed to have this template match a data field as well.  I used this additional field and a formula to add a picklist selection to a multipicklist field with existing data (without this formula, the data loader would override the existing selections).

image

In case you’re curious, the formula in H4 is =CONCATENATE(G4,"; Weekly Mailing").

 

Click here to download the template.

Disclaimer: use at your own risk.  Be very careful playing with the data loader.  Spot check your data and make sure to do a test run of a few records before proceeding with a large data set.

Comments Off on Customization

Customization

Posted October 9th, 2008 in Certified Administrator by John Coppedge

Describe a Profile

Profiles are primarily used to control the permissions for a user- see below.

Explain what a Profile controls

  • Tabs shown (Default On, Default Off, Hidden)
  • IP restrictions (Enterprise+)
  • Object-level security
  • Field-level security
  • Login hours
  • Apex/VF pages accessible
  • Console layout
  • Selectable applications (sales, call center, etc.)
  • Administrative & General User permissions (modify all data, run apex, approve contracts, etc.)

List the Standard Profiles

  • Contract Manager
  • Marketing User
  • System Administrator
  • Solutions Manager
  • Standard User
  • Read Only

Evaluate when to create a Custom Profile

You must create a custom profile if you want to change any of the following on a standard profile:

  • Administrative permissions (modify all data, view and setup configuration, etc.)
  • General User permissions (manage self service portal, manage cases, etc.)
  • Object-level permissions

Standard profiles and custom profiles are essentially the same, except that the above 3 areas of configuration cannot be changed on a standard profile.

Explain the differences between a Custom Field and a Standard Field

Standard fields are created by Salesforce and packaged with the object.  Custom objects are those that you create.

Custom fields and objects always have "__c" appended to the API name.

Side note:  Custom related lists to have "__r" appended to the API name.

List the different types of Custom Fields

Here we go:

  • Auto Number (there are some options here, but this is essentially a counter that grows as records are created)
  • Checkbox
  • Currency
  • Date
  • Date/Time
  • Email
  • Formula (covered later)
  • Hierarchical (creates a lookup relationship from the user object to the user object and does not reference role- this could be used to create a field for a user’s supervisor)
  • Lookup Relationship
  • Master-Detail Relationship
  • Number
  • Percent
  • Phone
  • Picklist
  • Picklist (Multiselect)
  • Roll-Up Summary
  • Text
  • Text (Encrypted) – Available by request only.
  • Text Area
  • Text Area (Long)
  • URL

Set up a Custom Field

(you’re sunk if you haven’t done this a few dozen times already)

Map Custom Lead Fields

  1. Create the custom field on the lead
  2. Create the custom field on the object that you wish to transfer the information to
  3. Map the field (Customize –> Leads –> Fields, Map Lead Fields)

Let’s say I have a field on my lead called "Page Source" (I have mutliple web-to-lead forms running; this page tracks which page the lead was submitted from).  I would create the picklist field "Page Source" on the lead object, and then on the opportunity object, then head back to the lead page and map it over.

Re-label a Standard Object

Customize –> Tab Names and Labels –> Rename Tabs and Labels

Available in Pro+

List objects for which you can enable Field History Tracking

  • Accounts
  • Cases
  • Contacts
  • Contracts
  • Leads
  • Opportunities
  • Solutions
  • Custom Objects

Notably AWOL: campaigns, assets, ideas, documents, opportunity products, price books, products

Define the dependencies when using a Dependent picklist

Straight-forward.  You select the options you want listed and those you do not by double clicking each of them.

You can have many dependant fields, but only one controlling field.  For instance, you can have the field "Type" control options for the field "SLA" and "Status"; however "Status" cannot have options that change based on "SLA" and "Status".

Picklist dependencies work for Picklist and Picklist (Multiselect) fields only.

Create and modify a Dependant Picklist

Describe the capabilities of Custom Lookup fields

  • Create a related list on the other object (for instance, opportunities listed on the account page- the name of the related list is controlled by the lookup field, the fields displayed in the list is controlled by the page layout)
  • Enables cross-object formulas (I can create a formula on an opportunity that references a field on the account)

Create and modify a Lookup Field

Describe the capabilities of Formula Fields

Formula fields have an immeasurable number of uses.  See the article "Useful Advanced Formula Fields" for some great examples.

The key is to understand that a formula field can capture and manipulate data.  It can pull data from:

  • Any field on the record the formula is created
  • Any field on a record that is linked via lookup relationship (a maximum of 5 lookups)- for instance you can create a formula on the opp object and reference account’s data

The key limitation of formula fields is that they cannot reference data in related lists. For instance, you cannot reference any information about the contact roles on the opportunity in a formula because contact roles is a related list, not a lookup (I’ve been burned by this!).

Create and modify a formula field

Describe Roll-up Summary Fields

The key benefit of a roll-up summary is that it performs math on data in related lists. The key limitation is that roll-up summaries are only available for data in a master-detail relationship.

Types of rollup:

  • COUNT (record count of related list)
  • SUM (addition of field within related list-number/currency/percent only)
  • MIN (lowest value within related list-number/currency/percent/date/date-time only)
  • MAX (highest value within related list-number/currency/percent/date/date-time only)

You can only set filter criteria for records to enter into the summary.

Example:  Create a field on account that displays the total of all opportunities won for the account:  Create the roll-up summary SUM of opportunity.amount and set the filter criteria to stage=won.

Create and modify a Roll-up Summary Field

Try my example.

Describe the elements within a Page Layout

Page layouts are comprised of sections; each section contains either fields, custom links, or related lists.  Key facts:

  • The displayed fields within a related list are controlled by the page layout; the name of the related list is determined by the lookup/master-detail relationship on the other object (see customize a related list below).
  • Page layouts can make an object required or read only.  Combine page layouts and field-level security: take the lowest possible permission.  For instance, a "hidden" (field-level permission) will never display regardless of page layout.  Likewise, a field marked "Always require a value in this field to save a record" will always be required on the
    page layout.
  • Page layouts are assigned by profile and by record type.

Create and modify a Page Layout

Create, customize, and print a List view

When you click on Accounts and see "My Accounts" next to view- that’s a list view.  Click new to create a new one.  Quick facts:

  • Every object that is associated with a tab will have at least one list view.  No tab, no list view.
  • You can assign filter criteria to your list view (just like a report).
  • You can assign list views to: yourself only, groups, and roles.
  • To print a list view click the printable view button on the top right of the page.  Printable list-views can be disabled org wide (user interface settings).

Customize a Related List

Exmple:  Custom object "Sample Object" lookup relationship to account.  Rename the related list from the object doing the looking- in this case edit the lookup to account.  Notice the label in the bottom right "Those Objects".

lookup - field config

The fields displayed on the related list are controlled by the page layout.  Here is the account page layout (that should read "Fields displayed in the related list"):

acct page layout

Describe how to use Record Types

Quick facts:

  • Enterpise+ only.
  • The record type "Master" is always present for every object, but is not listed under the record types list.  It contains all picklist/process options.  It can be assigned as a record type for a profile so long is it the only assigned record type for that profile.
  • Each record type is assigned to 1 page layout type per profile.  This means if you have two custom record types for account and 5 profiles, you will have 15 page assignments (5×2 for each custom, and 5 for Master).
  • Selectable record types are assigned per profile.
  • Field-level security is configured separately for each record type.

The overall process for creating a record:

  1. What record types are associated with the current profile?
  2. If more than 1 record type, prompt user for record type selection.  If just 1, select that record type without prompting.
  3. Based upon record type and profile, assign appropriate page layout.
  4. Based upon record type, assign the appropriate process and picklist values.

A process is a selection of picklist values specific to an object:

  • Lead Process:  Lead Status (open, closed, etc.)
  • Sales Process (opportunity):  Stage (won, lost, percentage)
  • Support Process (case):  Case Status (open, closed, etc.)
  • Solutions Process:  Solution Status (draft, deployed, etc.)

Exampe:  My sales staff sells an opportunity that represents a contract.  My customer service department can then upsell on this contract.  I would create two sales processes, two different record types, and two different page layouts: "Contract" and "CSR Upsell".  I would create a lookup relationship from opportunity to opportunity, and only require/display this only for the CSR profile.  I would also configure the roles of the organization so that they could not modify each others’ opportunities.

Create and modify a Record Type

I would suggest trying to configure my exmaple above.  If you do send me screenshot so I can update this doc 🙂

List the objects that require a business process

Do you remember?

  • Lead
  • Opportunity
  • Case
  • Solution

Explain how Field-Level Security affects page layouts and visibility

Apply whatever security is most restrictive between the page layout and field level security of the profile.

Examples:

  1. Field level: Hidden
    Page Layout:  Listed normally
    Result:  Not displayed (treated as if it were not listed on the page layout)
  2. Field level: Read Only
    Page Layout:  Listed normally
    Result:  Read Only
  3. Field level:  Edit
    Page Layout: Read Only
    Result: Read Only

Set up Field-Level Security

Administrative Setup –> Security Controls –> Field Accessibility.  This will allow you to view by fields, profiles, or record types.

Administrative Setup –> Manage Users –> Profiles.  This will allow you to configure field-level security per profile.