Tip: How to Fire an Updated Trigger on Existing Salesforce Records

Hey Salesforce Admins,

Here's a tip to help you if you've got a recently updated or newly created trigger (or workflow rule) that you want to fire on existing records.

Create a custom 'Admin Filter' text field on the object that you are running the trigger on. You don't need to add it to any page layouts or make it visible to users. (I chose this method because I didn't want to update any existing fields that are already in use in order to force the trigger to fire.)

Then, use the Data Loader (or Mass Update Anything) to update that field on all of your records.

Ta-da! The update will fire the trigger on all records that match whatever criteria you've specified.

Now, I'm off to do this in my own org.

How to Extract Salesforce Record Ids From a Hyperlink

I recently found myself viewing a Salesforce report with fields that I wanted to update. But, I needed the id to do that. Unfortunately, the report was a standard report (in this case: HTLM Email Status), so my customization options were limited.

So, I copied and pasted the report data into Excel.

I needed a way to extract all the hyperlinks from the text so I could get to the record IDs in order to update. I followed the directions here to create a macro that extracted the hyperlinks.

Here's how it all came together: 

1. Paste hyperlinked text into Excel. Add a column for Extracted URL.

2. Navigate to Visual Basic Editor.

3. Create a new Module.

4. Paste the following code into the module. (Get the code here.)

5. Run the code.

6. After the hyperlink has been extracted, pull out the record id with a formula.

7. Expand the formula for all rows.

And that's it!

So, if you ever find yourself struggling to update records when you don't have the id accessible, but do have the hyperlink, now you'll know what to do.

Finding the Ultimate Parent in an Account Hierarchy with a Simple Formula

Traversing the account hierarchy via the Parent Account field can be challenging.

I was doing some research into including 'Ultimate Parent' in reports - so that you can see all Opportunities for any account in the hierarchy, for example.

I knew I had read something about it... and after spending a few hours searching, I discovered that not only had I read something, I had actually already implemented it. Ha.

I don't remember where I got this formula, so if it looks familiar to you, please let me know. It's a pretty awesome way to use standard functionality and can go through a hierarchy of up to 10 levels.

How to Do It:

1. Create a text formula field named 'Ultimate Parent Name' on the Account object.
2. Paste the following into the formula:

IF( LEN(Parent.Name)<1 ,HYPERLINK("/"&Id, Name,"_parent"), IF( LEN( Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Id,Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Id,Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Id,Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), IF( LEN( Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name )<1 ,HYPERLINK("/"&Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Id,Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Parent.Name,"_parent"), "Ultimate Parent Beyond 10 Levels"))))))))))

What it looks like:

I can now use this field in reports for grouping or filtering if I want to see all Opportunities belonging to any account in this particular hierarchy. Cool!

Note: We've already implemented the customizable inline account hierarchy (that's what you're seeing in the picture, with the formula field below it), which displays the hierarchy as a visualforce page on the Account page layout. Super handy!

Admin Journal: Crafting a Unique Id for Updating when Record Id is Missing (@salesforce)

Dear Admin Journal:

Sometimes, users do something silly... like exporting a report to Excel, making changes that they'd like me to import and forgetting something pretty important: the record id.

It's not impossible to update records without the record id. It's just not handy... and it can be error-prone.

Over the years, I've figured out various ways to get around the missing record id, usually involving several queries and some rather complex vlookups.

But, here's a pretty simple method that I used today when the csv I received from a user had only 7 columns + one column for reassigning record owner.

1. I queried Salesforce to pull out the same 7 columns + record id with LexiLoader.
   
2. In my user's spreadsheet, I created a new column. I concatenated 3 fields that I knew would have at least 2 unique values, so it looked kind of like this:
   
   AccountName&LEFT(BillingAddresss,5)&BillingCity.
   
   Side note: I used the LEFT() function because my user had copied the data from a report in their browser and pasted it into Excel. The Billing Address had split into two rows in the spreadsheet. So, it wouldn't match my query.
   
3. In my query spreadsheet, I created 2 new columns. In one of them, I concatenated the same 3 fields to use as a unique id. In the other column, I created a vlookup to match my manually created unique id and return the column that had been updated in my user's spreadsheet.
   
4. Before updating using LexiLoader, I could remove the columns that I'm not using (Owner Name, Forged Unique ID, etc), or I could just leave them out of the field mapping.

It sounds kind of complex. But trust me, it's simple. 

Here's a rather lame screenshot (Skitch doesn't support blurring? What!?):

So, when I'm stuck with a spreadsheet without the record id and the user is waiting for me to update their data, I calmly remind myself of this.

In other news, I switched to a MacBook Pro last week. While I really like my Mac (I hesitate to say 'love'... but I'm sure someday I will), I've found that quite a few of the tools that I relied on are not available. I'm missing the Excel Connector, but I'm making it work with LexiLoader (a version of the Apex DataLoader for OSX) and Excel (although OpenOffice or Google Docs would work fine too).

Now, I'm off to finish updating.

Love,

Jenna, The Admin

Jenna Baze is a rather geeky girl with average social skills and an affinity for: Google Apps, Salesforce.com, Pinterest, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com | Twitter: @RatherGeeky | Facebook: Rather Geeky Tips

{Update} Val Rule to Prevent ‘Dear John L.’ has a Glitch – Fixed! #DataQuality @Salesforce

A few months ago, I shared a validation rule for Contacts to prevent sending emails with “Dear John L.”

My data quality check ensured that users didn’t enter a middle name in the first name field.

But, when I was doing some updates with the Excel Connector yesterday, I realized that my validation rule had a glitch.

It was correctly preventing middle names in the first name field, but it was also throwing the same error when they entered a name that happened to be abbreviated (such as “A. R.”). Or in this case, I was getting the error when I attempted to update the records en masse.

So, I’ve updated my rule to allow abbreviated first names by adding a function to my formula to cause it to fire only when the length of the field is greater than 5 characters.

This accounts for names where a space exists between the acronym or vice versa.

Here is the original error condition formula:

And here’s the updated one:

If you have a more effective way to accomplish this, please do share in the comments!

Related Posts

• A Simple Rule to Prevent ‘Dear John L.’ in Emails - #DataQuality, @Salesforce

Jenna Baze is a rather geeky girl with average social skills and an affinity for: Google Apps, Salesforce.com, Pinterest, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips

A Quick Fix to Prevent Getting P.O.’d - #DataQuality @Salesforce

As a Salesforce admin, I am fairly attentive to data quality.

It seems that inconsistencies and typos abound in address-related information.

So, I have a few automated tricks up my sleeve to keep our data clean.

The Problem

According to the USPS address standards, the proper abbreviation for post office box is:

PO BOX ####

Many users when entering these types of addresses, type “P.O.” This is unnecessary.

To help enforce consistent standards, I have set up a workflow rule that does the following:

• (Account: Billing Street CONTAINS P.O.,P. O.,p.o.) OR (Account: Shipping Street CONTAINS P.O.,P. O.,p.o.)

I have two field updates connected with the rule. They are:

1. SUBSTITUTE( BillingStreet , "P.O.", "PO")
2. SUBSTITUTE( ShippingStreet , "P.O.", "PO")

I have created another rule to process Contact addresses in the same way.

The nice thing about using a workflow rule with a field update is that the user doesn’t have to take any action. The data is tidied up without either of us having to lift a finger.

Yay.

Update (as of 4.29.2011)

@eliz_beth had a question about how to include if the street address has “A.P.O” in it as well. I haven’t run across this in my org, but I think the following modification would work.

Step One: Update the Workflow Rule criteria. Including “A.P.O” is a bit redundant, since it would find it using “P.O.” as well. But, I figured I would include it anyway.

Step Two: Update each of your field updates (for BillingStreet and ShippingStreet) to include the following logic.

Since the IF statement first evaluates whether the BillingStreet contains “A.P.O”, it should perform correctly by replacing “A.P.O” with “APO”. If the string does not contain “A.P.O.”, only then will it proceed to the next criteria. If it contains “P.O”, it will replace it with “PO”. This prevents it from updating “A.P.O” to “A.PO”.

I haven’t tested this, but I’m pretty sure it will work as expected. :) (famous last words)

A Simple Rule to Prevent ‘Dear John L.’ in Emails - #DataQuality, @Salesforce

I have a thing about naming conventions and consistent data.

I often see Lead and Contact records where the First Name is something like “John L.”, even though I have added a Middle Initial field.

I personally would prefer not including a middle name at all, but it is something that is widely accepted in our industry. So, I’m doing my best to keep my users happy.

Here’s the simple rule that should effectively nip this in the bud.

It works the same for Leads and Contacts.

Yay for data quality!

Update

You can also prevent users from entering “John T” (without a period) by changing the error condition formula to this:

OR(
   CONTAINS(FirstName, "."),
   CONTAINS(RIGHT(FirstName,2)," ")
)

This formula runs if either:

1. There’s a period in the First Name field, or
2. The second character from the end of the First Name field is a space.

Thanks @hak_a_tak for the suggestion!

Admin Journal: Preventing My @Salesforce Users from Including Time-Specific Information in Description Fields with a #Simple Validation Rule

I’ve noticed while doing data cleaning that some users add date-specific information to Description fields on Accounts, Leads or Contacts.

Why This is Bad

No one may notice ‘must follow up by 12/11/2007 or earth will explode’ in the Description of a record such as an Account. That could be bad news.

An Easy Fix (Sorta)

To encourage users to log this information in the most efficient way, I an create a validation rule that looks for keywords in the Description field of a specific object, such as Account.

I can customize the rule based on the typical phrases that I know my users enter, such as ‘follow up on’.

I like to include a detailed error message so that the user will have a good idea of what their next step should be.

Error: You have included a 'Follow up' task in the Description field, where it may be overlooked. Please remove that reference from the Description field and add it instead as a related task.

I gave it a quick test and it worked.

I’m sure there are plenty of other ways to accomplish the same thing, but this simple rule is one way to gently herd my users in the right direction.

 

Additional Resources

• Haven’t heard of Salesforce? Find out about it here.

 

Jenna Baze is a rather geeky girl with average social skills and an affinity for: all things Google, Remember the Milk, Salesforce.com, Inbox Zero, Etsy, natural light photography and finding creative ways to avoid doing dishes (which includes updating a handful of blogs).
Email: rathergeeky@gmail.com
Twitter: @RatherGeeky
Facebook: Rather Geeky Tips