Reverse VLOOKUP with INDEX + MATCH

VLOOKUP() in Excel is one of the most powerful and famous formula. This function is very easy to use to lookup a value from a table or range. You just need to refer a key value to the column located at the most left column of the range to get result value from another column in the same row. I'll not discuss more on VLOOKUP function in this blog, you can easily Google it or watch from Youtube.

One of the requirement to use VLOOKUP, which is also limitation of VLOOKUP, is the key value must be located at the most left column in the range, but that is not always how is out data structured. What happen when the key value located at the right of the result value?

Options:
1. Move the Result or Key value column 
Move the Result value column to the right of Key value, or move Key value column to the left of Result value, then use VLOOKUP.
This may work easily, but sometimes when you work with many columns and many user, move column is not really a desired option.

2. Copy the Key or Result value
Copy Key or Result value to have Key value located before of Result value, then use VLOOKUP. Same with option 1, sometimes option to move column in big worksheet and work many user is not really a good option.

3. INDEX + MATCH function
The INDEX function returns a value in a table based on the intersection of a row and column position within that table. The first row in the table is row 1 and the first column in the table is column 1.
The MATCH function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.

With combination of INDEX + MATCH functions, we can get something similar with VLOOKUP, but the Key not must be located before Result column, see this sample:

What is the formula in I2? =INDEX(A:A,MATCH(H2,B:B,0),1)

A:A = result / target value
B:B = key
H2 = data for row 2
I2 = target result

Salesforce URLFOR()

URLFOR() function in Salesforce is not widely used as it is only available in custom buttons, links, s-controls, and Visualforce pages.

Syntax: {!URLFOR(target, id, [inputs], [no override])}
target: URL or action, s-control, or static resource merge variable
id: a reference to the record (depends on the “target”)
inputs: optional parameters with format: [param1="A", param2="B"]
no override: optional boolean flag - default false. Set to true to display a standard Salesforce page, regardless of whether you have defined an override for it elsewhere.

 The most common URLFOR() is used by developers in Visualforce pages to refer to a resource in Static resources, such as images or scripts.

Visualforce
<apex:image url="{!$Resource.ImagePba}" width="50" height="50" />
This ImagePba in the sample above is the Static Resource name, not the original image file name.

Optionally, you can add URLFOR()
<apex:image url="{!URLFOR($Resource.ImagePba)}" width="50" height="50" />

But when you use the resource in an archive file (such as a .zip or .jar file) in a static resource, URLFOR() is a must as a second parameter.
<apex:image url="{!URLFOR($Resource.Images,'pba.png')}" width="75" height="75" />
<apex:image url="{!URLFOR($Resource.ImagesFolder,'image/campaign1.png')}" width="100" height="100" />
<apex:includeScript value="{!URLFOR($Resource.LibraryJS, '/base/subdir/file.js')}"/>


Custom Button or Link
Admin can make use of URLFOR() function with $Action global variable in custom Button or Link. The $Action global variable provides methods such as View, Clone, Edit, and Delete. Some objects support other additional actions, see all valid actions here.

URLFOR() determines your base URL, and the $Action determines what view of a record to go to (the view page, edit page, etc.). For example, to edit the Account from the Contact page, add the custom button or link in Contact: {!URLFOR($Action.Account.View, Account.Id)}.

More samples
New Account -- {!URLFOR($Action.Account.New)}
View Account -- {!URLFOR($Action.Account.View, Account.Id)}
Edit Account -- {!URLFOR($Action.Account.Edit, Account.Id)}
Clone Contact -- {!URLFOR($Action.Contact.Clone, Contact.Id)}
Account Tab -- {!URLFOR($Action.Account.Tab, $ObjectType.Account)}

Reference:

• Formula Operators and Functions I–Z
• Referencing a Static Resource in Visualforce Markup
• Valid Values for the $Action Global Variable
• Constructing Effective Custom URL Buttons and Links

Salesforce: Auto Lock Record

Use case: make opportunity become read-only when opportunity reach stage Closed Won.

Options: there are multiple solutions for this, from simple to advance with code:

1. Record Type & Page Layout
This would be one of the most famous solution without code, but this will make the system more difficult to maintain as the object will have additional record type set and additional page layouts. In short, when the opportunity reach Closed Won, with Workflow, change the record type to new record type and assign the new record type with page layout with read-only fields.

2. Record Ownership
This will work by change the record owner to a system user in highest role hierarchy. This will work well when the OWD sharing setting is Public Read-Only. But, often this solution will not work well, because the original record owner changed and it is important for reporting, although you can create custom user field to store it.

3. Validation Rule
By using function PRIORVALUE() and ISCHANGED() to detect any changes happened in Closed Won record. User will get error when save opportunity has been marked as Closed Won previously.

4. Trigger
Since Winter '16, Salesforce introduce lock() and unlock() methods in the System.Approval namespace.  Admin need to enable this feature, from Setup | Create | Workflow & Approvals | Process Automation Settings. Then, select Enable record locking and unlocking in Apex.

Example:

 // Query the opportunities to lock  
 Opportunity[] opty = [SELECT Id from Opportunity WHERE Name LIKE 'Acme%'];  
 // Lock the opportunities  
 Approval.LockResult[] lrList = Approval.lock(opty, false);  
 // Iterate through each returned result  
 for(Approval.LockResult lr : lrList) {  
   if (lr.isSuccess()) {  
     // Operation was successful, so get the ID of the record that was processed  
     System.debug('Successfully locked opportunity with ID: ' + lr.getId());  
   }  
   else {  
     // Operation failed, so get all errors          
     for(Database.Error err : lr.getErrors()) {  
       System.debug('The following error has occurred.');            
       System.debug(err.getStatusCode() + ': ' + err.getMessage());  
       System.debug('Opportunity fields that affected this error: ' + err.getFields());  
     }  
   }  
 }  

5. Process Builder and Approval Process
In previous blog, we shared about users able to edit locked record and who will see Unlock Record button. As Process Builder able to call Approval Process, we'll make use of this combination to auto submit for approval when opportunity reach Closed Won. The approval process here would be auto approve, therefore it will leave a trace in the approval process.

a). Create Approval Process

b). Create Process Builder

Drawback for option (5): opportunity approval history will show action for approval submitted and approved, this is not ideal if you use opportunity with other approval process.

Reference:

• Set Approval Process Locks and Unlocks with Apex Code
• Auto-Lock Records with Process Builder

Adding Certs and Badges to Your Salesforce Community Profile

Don't let your hard earned Trailhead badges and Salesforce Certifications buried in your desk, make sure showing off all of them on your Salesforce Community profile!

1. Login to Success Community
Login to Success Community from https://success.salesforce.com. Once login, click your avatar and select "My Profile".

2. Edit Profile
Click "Edit" button then "Profile".

Scroll down to "Certifications & Badges" section and enable both "Show Salesforce certifications on my profile" and "Show Salesforce Trailhead Badges on my profile". Make sure to enter your account and verify it.

Click "Save Changes" and done. You will need to enter verification code sent your email.

3. Check Your Profile

Navigate back to your profile - https://success.salesforce.com/profile then click "Certification and Badges" - https://success.salesforce.com/ProfileCertificationsAndBadges, you should see your hard earned Trailhead Badges and Salesforce Certifications showing off now.

4. See other People Certs & Badges
Every user in Success Community will have unique User Id, which is start with 005, example: 00530000003TTvZAAW. When you click someone profile from Success Community, notice the URL, example: https://success.salesforce.com/_ui/core/userprofile/UserProfilePage?u=00530000003TTvZAAW&tab=sfdc.ProfilePlatformFeed

Get the User Id and paste it to following URL: https://success.salesforce.com/ProfileCertificationsAndBadges?u=00530000003TTvZAAW remember to change the Id to that particular User Id.

This is not convenience to manually copy and paste, ideally there should be a link from the user Chatter Feed to User Profile, so vote for this idea.

Another option is by search that people name from search text box and select People.

Search result as below, click the name then Certifications & Badges" link at left menu.

Excel Filter: Tips and Shortcut

As Salesforce admin, sometimes we need to prepare data before load into Salesforce correctly. Recently, I need to clean and prepare some pretty big amount of raw data. Microsoft Excel apparently is one the easiest and best available tool to clean and prepare the data before loading into Salesforce.

In this blog, I would like to share some tips learned from the exercise.

TIPS
- Ctrl+Shift+L – toogle enable and disable filter, make sure cursor in the range of value
- Alt+A+C – clear ALL filters

- Place cursor at header, Alt+Down arrow to show drop down menu

- Place cursor at body, Alt+Down arrow to show drop down of available values (except number)
* the last one will work even no filter added

In drop down Filter menu (Alt+Down arrow)
- E key – type search
- C key – clear filter in current cursor column
- F+E key – select blank value
- F+N key – select non-blank value
- Up and Down arrow – move cursor, Space bar key to select, and Enter to perform action
- When cursor in range of values, Home – move to top value
- When cursor in range of values, End – move to bottom value
- Alt+Down Arrow+S – sort A to Z
- Alt+Down Arrow+O – sort Z to A
- Alt+Down Arrow+T – sort by Color sub menu
- Alt+Down Arrow+I – filter by Color sub menu
- Alt+Down Arrow+F – text or Date Filter sub menu

DO
1. Double click at bottom right of a cell will copy value to visible rows only

Double click bottom right corner to auto-fill value of C2 to visible rows below it (C4 and C6). When we clear the filter, only C4 and C6 is filled, while C3 and C5 is skipped. Value of C2 can be static or formula.

You can apply this to multiple columns too.

2. Similar to point 1, copy paste will to copy value to visible rows only

- Select range C2-D2 -- copy
- Select range C4-D6 -- paste
- Result: only value in C4, C6, D4, D6 will be copied, while row 3 & 5 skipped

** the same result if you select range C2-D6 for fill with a color, row 3 & 5 will not be colored


3. Copy paste will copy only from visible rows value 
- This is not applicable for manually hidden rows
- Select cells / range to copy, example: copy as below screenshot

Paste to new area, I put my cursor to cell A8 -- only visible cells are copied.

4. Deal with blank row
If you need to deal with blank row in filter, make sure to highlight/select the area (in sample below, select area A1-D10 or the whole A-D column), before hit Ctrl+Shift+L, otherwise filter will not include area below empty row (row 7 and below).

DON'T
1. Copy more than 1 row from source into target with filtered rows

Don't copy more than 1 row from source into target with filtered rows, this cause value in target hidden filtered rows will be overwritten, sample: copy 3 rows of "B" from source (B11-B13)

Paste it to target which is filtered rows, for this example: paste into cell C2

Instead of copy value "B" into C2, C4, C6 -- "B" will be copied into C2, C3, C4.

Summary, copy from multiple rows will skipped filtered target.

This action will only work well, if there is no skipped rows in the applied filter, example: Jawa in sample above is at continuous rows e.g. 2,3,4.


Excel Table
By using the Table features, you can manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.
- Ctrl+L – create Excel Table
- To delete Excel Table table without losing the data:
   - Select Convert to Range from DESIGN tab menu, or
   - Right-click on the table and click Convert to Range under Table menu
- Filtering controls are added to the table headers automatically
- Place cursor anywhere in table, Alt+Shift+Down arrow – show the drop down menu
- You can have filter for more than one range of data on a sheet

Salesforce: Workflow Action Failed to Trigger Flow

You build awesome Process Builder or Flow and it works as tested. Few days later, your user start complaining see ugly error as sample below. What is this mean? Is this mean Salesforce buggy, or you have successfully hack it?

Workflow Action Failed to Trigger Flow
The record couldn’t be saved because it failed to trigger a flow. A flow trigger failed to execute the flow with version ID 30128000000AgZ7. Contact your administrator for help.

Click here to return to the previous page. 

or something like this:

Workflow Action Failed to Trigger Flow
The record couldn’t be saved because it failed to trigger a flow. A flow trigger failed to execute the flow with version ID 30190000000XmQs. Flow error messages: &lt;b&gt;An unhandled fault has occurred in this flow&lt;/b&gt;&lt;br&gt;An unhandled fault has occurred while processing the flow. Please contact your system administrator for more information. Contact your administrator for help. 

Click here to return to the previous page.

The one below happened when PB try to call Flow and it fail, it may be caused by the Flow is Inactive, while the one earlier is related to update record fail.

Actually, what here mean is, something is broken with Process Builder or Flow built into your org., it can be fail to update record because of validation rules, error in Flow, or etc.

In our blog earlier, we mentioned that prefix 301 is InteractionDefinitionVersion, but we can't really easily see that record content by putting into URL, or by query with SOQL. So, how to trace that 301 error is referring to which Process Builder or Flow?

The user who create the Process Builder will get email from FlowApplication, with subject "Error Occurred During Flow "flow name", this email will mentioned the flow name, but no Version Id information as in the screenshot above. If the user create that Process Builder not in office, how you can trace Version Id prefix 301 is related to what?

There are 2 options to find this:

1. Flow
Use Flow Designer to check if that Id is refer to which Process Builder or Flow -- remember that Process Builder is using Flow as it engine. Follow this Url https://na3.salesforce.com/designer/designer.apexp#Id=30190000000XmQs, change na3 to your salesforce instance and you should see the Flow Name which is Lead Share for this sample.

2. Workbench
Login to workbench, navigate to menu Info | Metadata Types & Components, select Flow from drop down and click Expand All. You should find that 301 Version Id from the components and get the Flow name, which is "Lead Share"

This is the Process Builder as result of our finding:

Reference: Validation Rule in Workflow and Process Builder

Salesforce: Auto Add User to Multiple Chatter Groups

Requirements:
Universal Container would like to automate new Salesforce user created auto join certain Chatter groups based on user detail. Example: Finance staff should auto added to Finance group and as the user is based in Asia Pacific, she also need to be added to APAC groups.

Solution:
Create a custom object "Chatter Group" as mapping for criteria with Group Id. Here are the fields:

• Name : auto number
• Group Name : text
• Group Id : text
• Active : checkbox
• Profile : picklist (multi-select)
• Team : picklist (multi-select)
• Sub-Team: picklist (multi-select)

Team and Sub-Team is custom fields available in User object.

Example: if the user with Profile = Finance, he/she will be auto added to Finance chatter group. If the user with Team = APAC, he/she will be auto added to APAC chatter group, no matter if the user has been added to other groups earlier.

Technical:
You may build Process Builder + Flow as this blog sample, but I do feel it is too complicate go with that route because of  Mixed DML error. Using trigger and apex class would be much simpler, you download snapshot of the code from here.

In essence, you just need to insert new record to this object 'CollaborationGroupMember' and populate following 2 fields:
- CollaborationGroupId = Chatter Group Id
- MemberId = User Id


Things that you need consider to be covered in the code:
- When user created
- When user change profile or other criteria, the code should auto add user to new groups
- When deactivated user become active again
- When user deactivated -- there is no need to care for this, Salesforce will auto remove users from all groups

SimplySfdc in 2016

We just close 2016 and welcoming 2017. I would like to share some interesting statistics for SimplySfdc blog on 2016, a blog dedicated to all Salesforce users and community around the world. Happy New Year 2017! Here is the blog post for the statistic in 2015.

In 2016, less blog is written (5% less), but the pageviews are increased more than 9%, and total session increase 5.45%. This means Salesforce community and the user is growing worldwide.

Page	2016	2015	change
Total New Page	76	80	-5.00%
Total Pageviews	161,516	148,130	9.04%
Total Sessions	130,724	123,964	5.45%
Pages / Session	1.24	1.19	4.20%

Again this year, we see an almost similar number of sessions come from the search engine (0.13% increase), simplysfdc.com become more known with almost 26% increase of direct hit to the website. Instead of growth from referral as in 2015, this year social media take off by contributing increase more than 158% sessions, while referral down by 18% compared to 2015.

Channel Source	2016	2015	overall	by number of sessions
1. Organic Search (#1~2015)	78.79%	82.98%	-5.05%	0.13%
2. Direct (#2~2015)	12.68%	10.58%	19.85%	26.42%
3. Social (#4~2015)	5.19%	2.12%	144.81%	158.69%
4. Referral (#3~2015)	3.34%	4.33%	-22.86%	-18.71%

* overall mean percentage of the item compare of total in 2016
* by number of sessions mean percentage of the item compare to 2015 sessions


Google, as we know, serve as the top search engine for everyone, it contributes more than 97% which is increase almost 1%. Both Bing and Yahoo are decreased, but they still stay at the 2nd and 3rd spot, their combined percentage for only 2.6% for the search engine. This top 3 search engine contribute 99.89% of all total search engine, which does not change compared to 2015 statistic.

Top Search Engine	2016	2015	overall	by number of sessions
1. Google	97.29%	96.47%	0.85%	0.98%
2. Bing	1.90%	2.02%	-5.94%	-5.87%
3. Yahoo	0.70%	1.40%	-50.00%	-50.00%
total top 3 search engine	99.89%	99.89%

This year we see a lot of changes in term of social media as the referral. I usually share the new post to LinkedIn, Twitter, and Facebook. But I have no idea why Reddit suddenly spike to spot #3 and contributing almost 1,300 hits. All social media channel increase compare to last year, except Google+ which now I start to doubt that people still use Google+. Overall Stack Exchange is lower compared to other channels, but looking at the number of sessions, it still increases by 40%. Total top six social media contribute more than 97% of total social media as the referral site.

Top Social Media Source	2016	2015	overall	by number of sessions
1. Twitter (#4~2015)	29.07%	15.40%	88.79%	388.37%
2. LinkedIn (#2~2015)	21.77%	20.54%	6.00%	174.21%
3. Reddit (#~2015)	19.12%	0.57%	3,245.07%	8,553.33%
4. Facebook (#6~2015)	11.43%	8.00%	42.84%	269.52%
5. StackExchange (#1~2015)	11.39%	21.04%	-45.87%	40.04%
6. Google+ (#3~2015)	4.45%	19.36%	-77.02%	-40.55%
total top 6 social media	97.23%	97.79%*

* total 2015 consist of StackExchange, LinkedIn, Google+, Twitter, Blogger (13.45%), and Facebook. Reddit not count
** based on some conversations in internet, hit from Reddit maybe fake


There is no change for top 3 countries of visitors, US visitors increase to 49% of the total visitor, India stays the same contributing 20% of visitors, while UK visitors decrease by more than 13%. Russia suddenly jumps to spot #4 with almost 3% of total visitors, while visitors from Australia and Canada also decrease. French removed from the list as they're dropped into #7. Top six countries represent more than 81% of the blog visitors.

Top Visitor Country	2016	2015	overall	by number of total user
1. United States (#1~2015)	49.04%	45.26%	8.37%	14.28%
2. India (#2~2015)	20.41%	20.96%	-2.61%	2.70%
3. United Kingdom (#3~2015)	4.88%	5.96%	-18.14%	-13.68%
4. Russia (#9~2015)	2.96%	1.04%	185.67%	201.25%
5. Australia (#4~2015)	2.17%	2.91%	-25.43%	-21.36%
6. Canada (#5~2015)	2.12%	2.74%	-22.79%	-18.58%
total top 6 countries	81.58%	79.77%*

* total 2015 consist of US, India, UK, Australia, Canada, and France (1.94%), Russia not count
** based on some conversations in internet, hit from Russia maybe fake

From cities perspective, all 6 cities in 2016 stay the same compared to 2015, only New York take over London in spot #4. Top 3 cities visitor increase around 1.5-2.5%, New York has a big jump of 22%, while London drops to 15%. Top 6 cities still represent more than 20% of total visitors.

Top Visitor City	2016	2015	overall	by number of total user
1. Bangalore (#1~2015)	5.74%	5.93%	-3.15%	2.14%
2. Hyderabad (#2~2015)	3.51%	3.61%	-2.80%	2.51%
3. San Francisco (#3~2015)	3.39%	3.51%	-3.58%	1.68%
4. New York (#5~2015)	2.98%	2.56%	16.45%	22.80%
5. London (#4~2015)	2.57%	3.20%	-19.69%	-15.31%
6. Pune (#6~2015)	2.00%	1.88%	6.32%	12.12%
total top 6 cities	20.19%	20.69%

This statistic includes access from desktop and mobile. Total top 5 web browsers still contribute more than 99% of the visitors. There are few changes in top 5 web browsers, with Safari take over Internet Explorer and Edge appear as a new web browser. Edge included in Windows 10 gain spot #5 replacing Opera. Safari getting stronger with the growth of MacOS and iOS users, Chrome surely becomes more popular and contribute close to 79% of users, Firefox still in spot #2 but in overall lower more than 33%, and Internet Explorer down by more than 32%. Opera actually still stay with 0.46%, but Edge surpasses it in 2016.

Top Visitor Web Browser	2016	2015	overall
1. Chrome (#1~2015)	78.88%	74.46%	5.94%
2. Firefox (#2~2015)	8.37%	12.60%	-33.57%
3. Safari (#4~2015)	6.80%	5.31%	28.06%
4. Internet Explorer (#3~2015)	4.46%	6.59%	-32.32%
5. Edge (new)	0.64%	0.16%	300.00%
total top 5 web browsers	99.15%	99.42%*

* total 2015 consist of Chrome, Firefox, Internet Explorer, Safari, and Opera (0.46%), Edge not count

In terms of operating system, there is no change in top 5 web browsers. MacOS gain more than 4% increase, but Windows down by more than 4%. Access from mobile device increase by 56% for iOS and 36% for Android, compared to 2015 growth for almost 50% for both iOS and Android. But total mobile access still less than 7.5% of the total of visitors. This makes sense because most of the visitors access this blog when they have difficulty in configuring Salesforce. iOS users almost reach 71%, while total Android users more than 29%.

Top Visitor Operating System	2016	2015	overall
1. Windows	73.78%	77.21%	-4.44%
2. Macintosh	17.04%	16.33%	4.35%
3. iOS	5.26%	3.37%	56.08%
4. Android	2.18%	1.60%	36.25%
5. Linux	1.23%	1.24%	-0.81%
total top 5 operating system	99.49%	99.75%

There are no changes to the top 5 screen resolutions, 1366 x 768 still the standard for the normal laptop nowadays, it down less than 1% for our visitors in 2016. However 1920 x 1080 keep gaining more popularity with 9% increase in 2016, we see this resolution in high-end laptop and most of the LCD monitor. In contrary 1600 x 900 which is an older standard resolution for LCD monitor drop more than 13%, and 1280 x 1024 even older resolution drop by more than 20%.

Top Visitor Screen Resolution	2016	2015	overall
1366 x 768	26.81%	27.01%	-0.74%
1920 x 1080	22.46%	20.58%	9.14%
1440 x 900	8.94%	9.65%	-7.36%
1600 x 900	8.22%	9.53%	-13.75%
1280 x 1024	4.39%	5.52%	-20.47%
total top 5 screen resolution	70.82%	72.29%

Top 5 Popular Page in 2016
For popular pages, 4 of 5 pages from 2015 still stay in 2016, except 1 new post created in late 2015 make it to #3 - users are looking on how to log a case to Salesforce support. Salesforce SOQL Query (#1 in 2014 and #5 in 2015) is no longer so popular, it drops to #12.
1. Salesforce: ISBLANK() or ISNULL() (#2~2015; #3~2014)

2. Salesforce SOQL query LAST_WEEK ; LAST_N_DAYS:n ; N_DAYS_AGO:n (#1~2015; #6~2014)

3. How to log Case to Salesforce support (new blog posted on 26 Dec 2015)

4. Salesforce: How to export Attachments? (#4~2015; #5~2014)

5. Salesforce: Custom Button to pre-populate Fields (#3~2015; #2~2014)

Top 5 Referring Page 1. Query Chatter Feed Item & How the structure in Salesforce object (#2~2015) 2. Salesforce: Master-Detail relationship (#1~2015) 3. Salesforce: Change Record Owner (new) 4. Salesforce: Validation Rule in Workflow and Process Builder (new) 5. Record Type in Salesforce.com (#4~2015)

Top 5 Referral Site 1. Cloud Socius ~ Tips for passing the Salesforce certified administrator exam (#1~2015) 2. Stack Exchange ~ Chatter feed data migration (#2~2015) 3. Salesforce Ben ~ Ultimate Salesforce Blogs List (#3~2015) 4. Stack Exchange ~ Why is Transfer Record a system permission? 5. Stack Exchange ~ How to Auto populate account name Additional Sub Blog In 2016, I also start 2 new sub-blog: - http://app.simplysfdc.com to list down apps related to Salesforce ~ 4,959 pageviews - http://blog.simplysfdc.com to list down all active blogs related to Salesforce ~ 657 pageviews

Alexa's Rank
On 22 November 2016, simplysfdc.com domain hit all times high in Alexa's rank #457,326 as top #6 for global Salesforce personal blog.

Reference:

• SimplySfdc in 2015
• SimplySfdc in 2014