Friday, January 12, 2018

Salesforce: Update Records criteria in Process Builder

Until Spring '18 release, in Update Record action, if we have multiple conditions, we can't customize the logic for the condition with AND, OR. Feel free to vote my idea Process Builder - filter logic in Updating Records action.

no option to define logic

While in Define Criteria, the option to customize logic is available since long time back.

If we drill down further, for the condition in Record Update, by default the relation for each condition is AND, however, if we define more than 1 condition using the same field, the relation of the conditions for the same field would be OR, here an example:

From above sample, we define Account Source twice, and Support Level twice, the logic for this can be translated as (1 OR 2) AND (3 OR 4).

Here quote from Salesforce documentation Update Records from a Process.
When you define multiple filters, the filter logic usually defaults to AND. However, if multiple filters have the same field selected and use the equals operator, the filters are combined with OR. For example, your filters check whether a case’s Type equals Problem (1), Type equals Feature Request (2), and Escalated equals true (3). At runtime, those filters are combined to (1 OR 2) AND 3.

ReferenceUpdate Records from a Process

Saturday, January 6, 2018

List file name not in English characters

This blog doesn't really relate to Salesforce, but I come across this issue when need to prepare files name for Salesforce data loader.

This blog only applicable when using Windows machine. If all the files name is in English, there is no issue that you can just use "dir" from the command prompt. But, my issue when the files name is in Korean and Japanese. Manually to copy and paste file name one by one consume too many times if there is a lot of files.

Once I thought that need to install kind of free app for directory printer, but can we just use out of the box thing available from windows? After doing some research, I'm able to achieve this without any extra tool, so I think just to blog it and share with the public.

There is a command called chcp at Windows command prompt. Chcp command displays the number of the active console code page, or changes the console's active console code page. Used without parameters, chcp displays the number of the active console code page.

Depends on your windows language and region setting the result may be different, here is mine, 437 mean United States.

If you type "dir" from the folder with Japanese or Korean or Chinese, they will show as ? for each character.

Because Japanese, Korean, or Chinese support by UTF-8, we can change the console to 65001, by type chcp 65001.

But, if you type "dir" after chcp 65001, you will still see the same ? as earlier in the screen. But, if you copy and paste to Notepad, they will be in their original characters.

Another way is to store the result into a text file dir /b >result.txt 
/b mean only to get the file name without any other data.
>result.txt mean all dir result will store in a new file name called result.txt (you can change to any name preferred.


Thursday, January 4, 2018

SimplySfdc in 2017

We just close 2017 and welcoming 2018. I would like to share some interesting statistics in 2017 for SimplySfdc, a blog dedicated to all Salesforce users and community around the world, and check out here for the statistic in 2016 too. Happy New Year 2018!

In 2017, much less blog is written (less than 55%), but total pageview and session only lower for around 25%.

Page 2017 2016 2015 change*
Total New Page 34 76 80 -55.26%
Total Pageviews 115,744 161,516 148,130 -28.34%
Total Sessions 97,310 130,724 123,964 -25.56%
Pages / Session 1.19 1.24 1.19 -4.03%
*compare 2017 with 2016

Similar with previous years, organic search always contributes the largest portion of traffic source, but direct traffic growth around 11%, this is a good sign that has become more trusted and followed by more people. A small increase in the referral as the traffic source, but the contribution from the social channel is much lower in 2017.

Channel Source 2017 2016 2015
1. Organic Search 69.68% 78.79% 82.98%
2. Direct 23.67% 12.68% 10.58%
3. Referral 3.81% 3.34% 4.33%
4. Social  2.83% 5.19% 2.12%

Google, as we know, is the most common search engine for everyone, the same applies for blog, it contributes more than 96% of traffic in 2017, although it decreases more than 1% compared to 2016. Both Bing and Yahoo stay at the 2nd and 3rd spot, traffic from Bing increase 0.63% compared to 2016, would this mean anything in the search engine war globally?.

Top Search Engine 2017 2016 2015
1. Google 96.23% 97.29% 96.47%
2. Bing 2.53% 1.90% 2.02%
3. Yahoo 0.73% 0.70% 1.40%
4. Other 0.51% 0.11% 0.11%

Top social media contribution to the traffic source for is not much change compared to 2016. LinkedIn raised to #1 with contribution almost 50%, while Twitter contribution almost the same with 2016. Both Stack Exchange and Google+ drop almost 4%, but Facebook gains 3% increase. Reddit appearance as #3 in 2016 is most probably not a real traffic. Total of top five social media contributes more than 97% of total social media as the traffic source.

Top Social Media Source 2017 2016 2015
1. LinkedIn 47.03% 21.77% 20.54%
2. Twitter 27.72% 29.07% 15.40%
3. Stack Exchange  7.65% 11.39% 21.04%
4. Google+ 7.62% 11.43% 8.00%
5. Facebook 7.50% 4.45% 19.36%

There is no change of the top 6 countries of visitors, US visitors contribute around 49% of the total visitor, India contributes a little bit higher for more than 20%, UK visitors decrease over the last 3 years. Russia sudden jumps in 2016 is the fake visitor, now they appeared in #10, which is the same as in 2015. Australia, Canada, and French stays at their position, with the small percentage increase for each of them. Top six countries represent almost 81% of the blog visitors.

Top Visitor Country 2017 2016 2015
1. United States 48.80% 49.04% 45.26%
2. India 20.90% 20.41% 20.96%
3. United Kingdom  4.40% 4.88% 5.96%
4. Australia 2.64% 2.17% 2.91%
5. Canada 2.23% 2.12% 2.74%
6. France 1.97% 1.87% 1.94%

From cities perspective, all 6 cities in 2016 stay the same compare to 2016. Hyderabad, San Francisco, and London visitor drop over last 3 years, while Pune visitors increase for the last 3 years. Top 6 cities represent almost 20% of the total visitors.

Top Visitor City 2017 2016 2015
1. Bengaluru 5.75% 5.74% 5.93%
2. Hyderabad 3.31% 3.51% 3.61%
3. San Francisco  2.99% 3.39% 3.51%
4. New York 2.76% 2.98% 2.56%
5. London 2.38% 2.57% 3.20%
6. Pune 2.10% 2.00% 1.88%

This statistic includes access from desktop and mobile. Total top 5 web browsers contribute more than 99% of the visitors. There are no changes in top 5 web browsers compare to 2016. Chrome becomes more popular web browser among visitors. Firefox still at #2, but with a much lower percentage. Some decrease in the percentage of Safari users. Internet Explorer users still there, but lower over the years. Edge included in Windows 10 gain small increase, but do not move it to the higher spot yet.

Top Visitor Web Browser 2017 2016 2015
1. Chrome 82.98% 78.88% 74.46%
2. Firefox 5.92% 8.37% 12.60%
3. Safari 5.33% 6.80% 5.31%
4. Internet Explorer 4.01% 4.46% 6.59%
5. Edge 1.08% 0.64% 0.16%

In terms of operating system, there is no change in the top 5 web browsers. Windows users increase by 1%, MacOS stay at the same at 17%, a big drop of iOS users, but still at spot #3. Android users gain increase over last 3 years.
Total access from mobile and tablet only 6.77% of the total of visitors. This makes sense because most of the visitors access this blog when they have difficulty in configuring Salesforce using desktop/laptop. iOS users down from 71% in 2016 to 59% in 2017, the rest would be Android users.

Top Visitor Operating System 2017 2016 2015
1. Windows 74.85% 73.78% 77.21%
2. Macintosh 17.11% 17.04% 16.33%
3. iOS 3.84% 5.26% 3.37%
4. Android 2.63% 2.18% 1.60%
5. Linux 1.08% 1.23% 1.24%

This year, we see 1920x1080 resolution take over 1366x768 as top screen resolutions, 1920x1080 found in high-end laptop and bigger screen LCD monitor. The rest of screen resolution are all slow down, with exception of 1280x720 now raise to spot #5, it used to be #10 in 2015 and #9 in 2016, this resolution mostly found at the middle-end tablet or mobile phone. 1280x1024 which is at #5 in 2016 and 2015, now drop to #9, which is fount at older LCD monitor.

Top Visitor Screen Resolution 2017 2016 2015
1. 1920x1080 29.06% 22.46% 20.58%
2. 1366x768 22.58% 26.81% 27.01%
3. 1440x900 8.24% 8.94% 9.65%
4. 1600x900 6.11% 8.22% 9.53%
5. 1280x720 4.88% 2.21% 1.20%

Top 5 Popular Page
For popular pages, 3 of 5 top pages from 2016 still stay in 2017. There is one post created in late 2015 How to log a Case to Salesforce support (#3~2016) is no longer popular, as Salesforce change easier way for the user to log a support case. Another blog Salesforce: Custom Button to pre-populate Fields (#5~2016; #3~2015; #2~2014) also no longer popular, as user start switching to Lightning.
A new blog post in early 2017 gain popularity to #2, while Data Loader post from Sep 2013, now gain popularity and take spot #5.

1. Salesforce: How to export Attachments? (#4~2016; #4~2015; #5~2014)
3. Salesforce: ISBLANK() or ISNULL() (#1~2016; #2~2015; #3~2014)
5. Salesforce: Data Loader Time Zone (#8~2016; #13~2015) 

Top 5 Landing Page
1. Salesforce: How to export Attachments?

Top 5 Referral Site
1. (new)
2. Salesforce Ben ~ Ultimate Salesforce Blogs List (#3~2016)
3. Salesforce Ben ~ November Round Up of the Top Blog Posts! (new)
4. CloudMyBiz ~ Removing the “noreply” Address from Salesforce Email (new)
5. Stack Exchange ~ Chatter feed data migration (#2~2016)

Sub Blog
I also have 3 sub-blog, where the traffic is not part of the statistic listed above:


Monday, December 25, 2017

Salesforce: Schedule Action in Process Builder

You may read and see somewhere that Process Builder far superior to Workflow, but for some reason, you don't see the option to schedule action, while everyone says that you can schedule action using Process Builder.

There are even ideas in IdeaExchange asking for more details of the schedule jobs from process builder:
Scheduled actions from Process Builder should appear under Time-Based Workflow
Process Builder: Waiting Flow Interviews: Provide Better Information

But, where is the Schedule Action? Is this something need to be enabled by Salesforce support? Or my Salesforce edition does not support Process Builder? Process Builder available from Professional Edition and above, read here Lightning Process Builder.

only immediate action and no scheduled action option

scheduled action available to be configured

What is the causes in the 1st screenshot we don't see scheduled actions option? To have schedule actions, your process has to meet at least 1 of 2 criteria below:
  • Start the process only when a record is created (1). Select this option when you choose an object for your process, or
  • Start the process when a record is created or edited (2). In addition, select the advanced option to execute actions only when specified changes are made (3) when you add criteria to your process.

I have a blog written last year related to "Do you want to execute the actions only when specified changes are made to the record?", so here is the other usage of that checkbox, using ISNEW() and ISCHANGED() will not enabled scheduled action option.


Sunday, December 24, 2017

Salesforce Report: Bar Chart with Cumulative Line Chart

In Salesforce reporting (include Lightning), you can use a field only once, if you have admin right, you can create formulas field that returns the same value with the original one, so can use it too in the report.

We have a request to have a chart with opportunity amount in both monthly total and cumulative YTD. Salesforce report has the cumulative option for a line chart, but unfortunately, as mentioned in the first paragraph, we can't use a field more than once in a chart, while to create a formula field for this scenario of dynamic values seems like not possible. The easier option is to export the data and easily built it in Excel, but it would not be stored in the platform.

simple bar chart only shows the total monthly amount

change to line bar chart and enable the Cumulative option

add the new measurement, but only can select different field, in this case, I use Count, plot as line chart and as the second axis.

create new formula field Amount clone, but because the main chart is a bar chart, the Cumulative option is not available for the additional measurement.

Now, let's see how is the data looks like in the report for the last chart:

So, if we can get the cumulative value in the last column, instead of the same value, then we can achieve the report requested.
For row 1, it would be just that row itself.
For row 2, it would be row 2 amount + row 1 amount.
For row 3, it would be row 3 amount + row 2 amount + row 1 amount.

Remember, there is a function called PREVGROUPVAL() in the Custom Summary Formulas for the report. We can use this function for this need. This function calculates values relative to a peer grouping. If there’s no previous grouping, the function returns a null value.

This function returns the value of a specified previous grouping. A “previous” grouping is one that comes before the current grouping in the report. Choose the grouping level and increment. The increment is the number of columns or rows before the current summary. The default is 1; the maximum is 12. You can only use this function in custom summary formulas for reports.

This is the function pattern
PREVGROUPVAL(summary_field, grouping_level [, increment])
From here, see that you can adjust the incremental of previous grouping.

Adding Custom Summary Formula with PREVGROUPVAL() function allow us to get this table

Then we can easily add this chart to the report:

Step by step:
1. From your summary or matrix report, create Custom Summary Formula, in this example, I called it Cumulative Amount.

2. In "Where will this formula be displayed?", select "Grouping 1:".

3. Copy and paste following formula:

You can adjust the formula as necessary, change AMOUNT:SUM with RowCount if you need to show the number of records instead of Opportunity Amount.
Note: you can use this solution if your report has the maximum grouping of 13, because the maximum incremental for PREVGROUPVAL() is 12.

4. Add Vertical Bar Chart with Y-axis is the main amount, then add measure using Custom Summary Formula created in step 1-3, select Plot as Line Chart.

You also can enable "Plot on Second Axis" to get second Y-axis values for the line chart values.


Tuesday, December 19, 2017

Salesforce: How to block Workbench?

Workbench is an unofficial, yet powerful tool for Salesforce admin and developer to perform almost anything related to Salesforce support. In many cases, end user supposed have no access to this tool, although it still honors user permissions assigned in Salesforce. However, can we block the normal users from accessing Workbench?

Yes, in the previous blog, we discussed how to turn off Salesforce1. Using the same method, we can block the normal user from accessing Salesforce from Workbench.

1. Connected Apps
Navigate to Setup | Apps | Connected Apps | Manage Connected Apps, click Edit link on Workbench.
Change the 'Permitted Users' value under 'OAuth policies' to 'Admin approved users are pre-authorized', and click Save.

2. To Give Access
Now, you want specific users able to access Workbench. There are 2 options:

a. Using Profile
All users in the profile will be able to access Workbench.
In the Workbench page under Connected Apps, scroll down to Profiles, click Manage Profiles button. Select all profiles that need to access Workbench, then click Save button.

b. Using Permission Set
Using Permission Set is more flexible, only users assigned with that permission set will be able to access Workbench.
But before configure in Connected Apps, you need to create a permission set without any permissions, then assign users to that Permission Set. Next, in the Workbench page under Connected Apps, scroll down to Permission Sets, click Manage Permission Sets button. Select all permission sets that need to access Workbench, then click Save button.

When a user without permission to access Workbench, the user will see an error message when try to login to Workbench OAUTH_APP_ACCESS_DENIED: user is not admin approved to access this app.