Tuesday, February 24, 2015

Salesforce: Default Picklist Value

When user creates a new record in Salesforce, such as Contact, we can set the default value on a picklist field, for example: Active default to True.

Step by Step:
  1. Navigate to Setup | Customize | Contacts | Fields
  2. Look for the picklist and click it
  3. Click the Edit link for the field needs to be set as default
  4. Select "Make this value the default for the master picklist", if any other value selected as default prior, it will be auto deselect.
  5. Click Save button

Recently we have a case, a group of users have the default value, and they do not want that value selected by default. Okay, that is easy, just follow the above steps to deselect the default value. But, there is no default selected value for that picklist field, confuse...?

Finally, we found there is Record Type for that object, those User Profiles only have access to one of the Record Type, that's why when user create new Contact, it does not prompt them to select a record type.

Click Edit link

If the Object has Record Type, Salesforce will use the default value defined in Record Type, not the default value selected in the object level.


Saturday, February 21, 2015

Salesforce: Send Birthday Email

We just implement Salesforce NFP (not for profit) for our church to store members database and attendances. Thanks to Salesforce for providing this awesome system for free.

We send birthday email to members who birthday on that date manually, this activities is just too troublesome as someone need to check who is birthday today and sent it manually, it time consuming and prone to human error and many members birthday are just skipped.

Since we are using Salesforce, can we just automate it? As info, we are using Person Account to store all members. Because there is no "scheduler" to execute something in Salesforce based on a date. I am thinking 2 solutions below out of my head when I heard about this requirement:

1. Workflow Rule + Field Update
With a simple Google search, I found this article "Creating an Automatic Birthday Email". Let us digest what is the logic behind using solution:
  1. Create an email template in Salesforce
  2. Create a checkbox field to flag this Contact Opt in for birthday email
  3. Create another checkbox field to flag contact has not been send email
  4. Create a date field to tell the system when to send birthday email using "Next Birthday" field
  5. Create a workflow on Contact (or Account for Person Account) for contact with opt-in email birthday and birthday is not blank
    • Create time trigger with action to send birthday email after X hours. For Person Account, since need workflow will only trigger for Account, while Email address on Contact, you need to have another workflow to copy Email address to a custom email field in Account
    • Create another time trigger after above trigger executed, this trigger to update checkbox field that email has been sent.
  6. Create 2nd workflow with criteria: opt-in for birthday email and email has been sent (this will be executed after 2nd time trigger in step 5)
    • Immediate action to update "Next birthday" date to next year
    • Create time trigger with action to update flag that birthday has not send
  7. Create 3rd workflow to check if Contact is flag for user who opt-out email birthday
  8. Create 4th workflow to check if Contact birthdate is changed
  9. Make sure to activate all Workflow rules
  10. Mass populate all contact "Next Birthday"
  11. Make sure email Deliverability to All Email

Note: for Person Account, workflow rule is trigger from Account, not from Contact, make sure the workflow rule object is Account.

Here is the steps from the article.

1 - Create an email template for the Birthday Email.

2 - Create a checkbox field:

1 - We'll call this field "Receive Birthday Emails" for the purposes of this article

2 - Default should be "Checked"

3 - Add it to all applicable page layouts so that all can see it (this will allow contacts to opt-out of the email)

3 - Create another checkbox field:​

1 - We'll call this field "Reset Birthday Email System"

2 - Default should be "Checked"

3 - Keep out of ALL page layouts; this should only be worked on by our workflow rules we'll create.

4 - Read only for everyone
4 - Create a Date field:

1 - We'll call this field "Next Birthday"

2 - Leave "Default Value" blank

3 - Like "Reset Birthday Email System", we'll keep this out of ALL page layouts and keep it read only for everyone.

5 -- Create a Workflow Rule:

1 - ​We'll call it "Send Birthday Email".  This will be the rule that will send the actual email, and then triggers (1 day later) the Birthday Reset process.

2 - For the Object to create the Workflow on, select "Contact."  This will be the same for all workflow rules in this article.

3 - The Evaluation Criteria should be "created, and any time it's edited to subsequently meet criteria."

4 - In the first line of Rule Criteria, input: "Receive Birthday Emails" 
equals "True"

5 - In the second line of Rule Criteria, input: "Reset Birthday Email System" 
equals "False"

6 - In the third line of the Rule Criteria, input: "Birthday"
 not equal to "" (leave the third column completely blank)

6 - Click Next.

6 - Add a Time Trigger and Action:

1 - Click Add Time Trigger.

2 - Set the trigger for 6 Hours after Contact: Next Birthday and click Save. (Note: if you need the email before the birthdate, select amount of time before Next Birthday instead.)

3 - Click Add Workflow Action | New Email Alert

4 - Type in a Description.  For Unique Name, we'll call it "Birthday Email".

5 - Select the Email Template you created in Step 1.

6 - In the 
Search field within the Recipient Type section, select Email Field

7 - Select "Email Field: Email" and click Add.​

This will not work when the workflow is created on Account object, which is required when Person Account is enabled. Although there is a standard email field on Account when person accounts are enabled, this field cannot be used in email alerts.

               As a work around, we can create a custom Email field on Account object and make it hidden for all profiles except system admin. To update the Person Account Email field’s value in it, we can create another workflow with Field update action

7 - Add Another Time Trigger and Action:

1 - Click Add Time Trigger.

2 - Set the trigger for 30 Hours after Contact: Next Birthday and click Save

3 - Click Add Workflow Action | New Field Update

4 - Let's call it "Check Reset Birthday Email System Box"

5 - Be sure to check the box labeled 
Re-evaluate Workflow Rules after Field Change

6 - The Field to update is what we called "Reset Birthday Email System"

7 - Under "Checkbox Options", select "True"
8 - Create a Second Workflow Rule:

​1 - We'll call it "Reset Birthday Email".  This will be the rule that sets "Next Birthday" for the next birthday after the current date, then triggers the "Send Birthday Email" Workflow rule.

2 - The Evaluation Criteria should be "created, and any time it's edited to subsequently meet criteria."

3 - In the first line of Rule Criteria, input: "Receive Birthday Emails" equals "True"

4 - In the second line of Rule Criteria, input: "Reset Birthday Email System" equals "True"

5 - Click

9 - Add a Field Update:

1 - Under Immediate Actions, click Add Workflow Action | New Field Update

2 - For the Name, let's call it "Reset Next Birthday".  This will actually set the Next Birthday field with the new value that represents the next birthday.

3 - For 
Field, select Next Birthday.

4 - Leave the 
Re-evaluate Workflow Rules after Field Change checkbox unchecked.

5 - Select 
Use a formula to set the new value, and Show Formula Editor
For Account/Contact:
IF(DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)) <= TODAY(), DATE(YEAR(TODAY())+1,MONTH(Birthdate),DAY(Birthdate)), DATE(YEAR(TODAY()),MONTH(Birthdate),DAY(Birthdate)))

For Person Accounts:
IF(DATE(YEAR(TODAY()),MONTH(PersonBirthdate),DAY(PersonBirthdate)) <= TODAY(), DATE(YEAR(TODAY())+1,MONTH(PersonBirthdate),DAY(PersonBirthdate)), DATE(YEAR(TODAY()),MONTH(PersonBirthdate),DAY(PersonBirthdate)))
10 - Add a Time Trigger:​Click Add Time Trigger.

1 - Set the trigger for 1 hour after Rule Trigger Date.  This is to ensure that this action happens after changing the Next Birthday field.

2 - Add a Field Update.  Select the Field as 
Reset Birthday Email System.

3 - Check the 
Re-evaluate Workflow Rules after Field Change box.

4 - Select Checked is 

5 - Click Save. 
11 - Create a Third Workflow Rule

1 - Let's call this workflow rule "Birthday Email Opt-Out".  This will check the Reset checkbox when a user discontinues the email for a contact, so that if the contact wants the email again (sometime later), the Next Birthday field is properly reset before giving out an email on the wrong date.

2 - This workflow rule will be Evaluated Every time a record is Created or Edited (the 2nd option)

3 - Rule Criteria: "Receive Birthday Emails" equals "False"

4 - Click 
Add Workflow Action | Select Existing Action

5 - Under "Search:", select "Field Update"

6 - Select 
Field Update: Check Reset Birthday Email System Box and click Add, then click Save
​12 - Create a Fourth (and final) Workflow Rule

1 - Let's call it "Birthday Change".  This rule takes care of the unlikely event that a contact's Birthdate field changes - we trigger our second Rule, so as to reset Next Birthday to reflect the new date.

2 - This workflow rule will be Evaluated Every time a record is Created or Edited (the 2nd option)

3 - Rule Criteria: Use a formula : 
For Account/Contact:
(MONTH(Birthdate) <> MONTH(Next_Birthday__c) || DAY(Birthdate) <> DAY(Next_Birthday__c)) && Receive_Birthday_Emails__c = True

For Person Accounts:
(MONTH( PersonBirthdate ) <> MONTH(Next_Birthday__pc) || DAY(PersonBirthdate) <> DAY(Next_Birthday__pc))
4 - Click Add Workflow Action | Select Existing Action

5 - Under "Search:", select "Field Update"

6 - Select 
Field Update: Check Reset Birthday Email System Box and click Add, then click Save

​13 - Activate all Workflow Rules

1 - Go to Setup > Create > Workflow & Approvals > Workflow Rules.  Click "Activate" next to each of the four new workflow rules.  This allows the workflow rules to actually run.

14 - Verification: before enabling for all contacts​ 

1 - NOTE:This step is not technically necessary, but recommended to ensure that all emails are scheduled properly.

2 - Create a test contact on an account.  Add an email field and keep the "Receive Birthday Emails" box checked.  Set the contact's birthday to Tomorrow.

3 - Monitor the Time Based Queue (see Monitoring the Workflow Queue
) You should see 1 Workflow in the queue for your contact, for the workflow "Reset Birthday Email", set for 1 hour from now.

4 - Wait approx. one hour.  After this rule executes, you should see 2 workflow rules in the queue, one scheduled at 6am on the Contact's birthday, and another scheduled 6am the next day, both for the rule "Send Birthday Email".

5 - If desired, you can do further verification the next day, that the email get sent, and then the next day at 6am, the Reset workflow should trigger and set Next Birthday to next year, and one hour later (7am), 2 more Workflow rules will be in the queue.

6 - When satisfied with verification, move on to Step 15.
15 - Enable All contacts to Receive Emails

1 - ​In order for any existing contacts to receive birthday emails, run a report that returns all contacts for which you'd like this feature enabled (note you may need to create a custom report type that includes just Contacts), and include these three fields: Contact ID, Receive Birthday Emails, and Reset Birthday Email System.

2 - Export the details of the report and open the document as a spreadsheet, and change ALL 0s to 1s and save as CSV.  Then import them back in using the Import Wizard as detailed in this article

3 - Due to the time-based queue limitations, the time it takes for the initial reset and scheduling of the birthday emails will vary depending on how many contacts are in your system.  The limit for time-based workflow action execution is 500 per hour, including any other time-based workflow action that may be in your system.

2. Apex Scheduler 

For developer familiar writing apex code, this should be pretty simple to write the apex class. But for those of you not familiar in writing apex code, there is an app in AppExchange develop by Salesforce Labs - Birthday eMailer, and read the customization guide here.

This app simply implementing apex scheduler:
  • Create an email template in Salesforce with Email Template name match with the Apex class
  • Create a checkbox field "Send Birthday Email” to flag Contact opt-in for Birthday email, set this field default to True
  • Set the scheduler to be executed daily:
    • Query contact object for contact with birthday today
    • Query EmailTemplate for the right Email template name
    • Send mass email
This solution is much simple than using Workflow rule, as in workflow we need to deal with many workflow rules and field update actions, so it will very confuse.

This solution also work for Person Account, because for each person account, Salesforce will create the Contact as well, so it work well when just query to Contact.

Here is the apex class modify from Birthday eMailer:

1:  /**************************************************************************/  
2:  //The Birthday Emailer  
3:  //  
4:  //This class can be scheduled to run nightly to send emails to Contacts   
5:  //or Person Accounts on their birthday  
6:  //   
7:  //Emails will only be sent to contacts that meet the following conditions:  
8:  // *the Birthday or Anniversary field is filled out  
9:  // *the Email Opt Out is unchecked  
10:  // *the "send birthday email" checkbox is checked  
11:  // *the Email is not blank  
12:  //  
13:  //Feel free to edit this code, or change the email templates.  
14:  //  
15:  /***************************************************************************/  
18:  global class BirthdayCronJob implements Schedulable{    
20:      //mandatory function called by the Apex Scheduler  
21:      global void execute(SchedulableContext SC) {  
22:        sendmail(); // our main function that does the email sending  
23:      }//end execute()  
25:      //Get email addresses of people with birthdays and who are to receive emails  
26:      public List<Id> getBirthdayEmailAddresses(Integer Month, Integer Day)   
27:      {   
28:        List<Id> mailToIds = new List<Id>();  
30:        //find a list of contacts with birthdays today  
31:        Contact[] c = [SELECT Id, email, Birthdate, Send_Birthday_Email__c, HasOptedOutOfEmail  
32:                FROM Contact   
33:                WHERE email <> '' AND   
34:                DAY_IN_MONTH(Birthdate) = : Day   
35:                AND CALENDAR_MONTH(Birthdate) = : Month    
36:                ];  
38:        //add the list of contacts to a list      
39:        for(Contact recipient : c) {  
41:            //If( contacts who's birthday is today can receive emails AND has the "Send Birthday Email" checked)  
42:            //{ add to birthday email recipient list }  
43:            //   
45:            System.Debug('\n*******Found Birthday Recipient');  
47:            if (recipient.Send_Birthday_Email__c == true && recipient.HasOptedOutOfEmail == false)  
48:            {  
49:              mailToIds.add(recipient.Id);  // add to email contact array  
50:              System.Debug('\n*******Recipient: '+;  
51:              //and add to Chatter Array  
53:            } else {  
54:              //Just add to chatter array  
55:              System.Debug('\n*******NO Recipient');  
56:            }  
58:        }  
60:        //return the list  
61:        return mailToIds;  
62:      }//end getBirthdayEmailAddresses()  
64:      public void sendMail()   
65:      {  
66:        //define variables       
67:        String debugAddress = '';  
68:        String BirthdayEmailTemplateName = 'Happy_Birthday';  
69:        String AnniversaryEmailTemplateName = 'Celebrating_your_Anniversary';        
70:        String debugMessage;  
71:        String[] toAddresses;  
73:        Integer DayOfEvent  =;  
74:        Integer MonthOfEvent =;  
77:        // build the Birthday list  
79:        //get the list of people with birthdays - this can justifiably come back empty, on a day when no contacts were born   
80:        List<Id> BirthdayIdsList = getBirthdayEmailAddresses(MonthOfEvent,DayOfEvent);  
82:        //Set the templates  
83:        EmailTemplate birthdayTemplate = [select Id,Name,Subject,body from EmailTemplate where DeveloperName = :BirthdayEmailTemplateName];  
85:        //EmailTemplate anniversaryTemplate = [select Id,Name,Subject,body from EmailTemplate where name like :temp+'%'];  
86:        //If we have a template, and we have a list of email addresses, then send the email  
87:        if(birthdayTemplate != null && BirthdayIdsList.isEmpty() == false)  
88:        {  
90:          Messaging.MassEmailMessage birthdayMail = new Messaging.MassEmailMessage();  
92:          birthdayMail.setTargetObjectIds(BirthdayIdsList);  
93:          birthdayMail.setTemplateId(birthdayTemplate.Id);  
94:          birthdayMail.setUseSignature(false);  
95:          birthdayMail.setSaveAsActivity(true);  
97:          // Send the email  
98:          try {  
99:            Messaging.sendEmail(new Messaging.MassEmailMessage[] { birthdayMail });  
100:          }catch(Exception e)  
101:          {  
102:            System.Debug(e);  
103:          }  
105:        }  
106:        else  
107:        {  
108:          System.Debug('BirthdayCronJob:sendMail(): Either an email template could not be found, or no Contact has a birthday today');  
109:        }//end if  
111:      }//end sendMail()   
113:  }//end class BirthdayCronJob  

If you modify the apex class in sandbox as needed, you will need Test Method to deploy the Apex Class:

1:  @isTest  
2:  class BirthdayCronJobTest {  
4:   static testmethod void test() {  
5:    //Test.startTest();  
8:    Integer testMonth =;  
9:    Integer testDay =;  
10:    BirthdayCronJob BCJ = new BirthdayCronJob();   
12:    //run the test once, without ensuring that a contact with today's birthday exists;  
13:    BCJ.sendMail();   
15:    Contact testContact = new Contact();  
16:    testContact.FirstName = 'Test';  
17:    testContact.LastName = 'Contact';  
18:    testContact.Email = '';  
19:    testContact.Birthdate      =;  
20:    testContact.Send_Birthday_Email__c  = true;  
21:    insert testContact;  
23:    //run the test again, now that a contact with today's birthday exists  
24:    BCJ.sendMail();  
26:    // Schedule the test job   
28:     String CRON_EXP = '0 0 0 3 9 ? 2022';  
29:    String jobId = System.schedule('testBasicScheduledApex', CRON_EXP, new BirthdayCronJob());  
31:    // Get the information from the CronTrigger API object   
33:    CronTrigger ct = [SELECT id, CronExpression, TimesTriggered, NextFireTime  
34:         FROM CronTrigger   
35:         WHERE id = :jobId];  
37:    // Verify the expressions are the same   
38:    System.assertEquals(CRON_EXP, ct.CronExpression);  
40:    // Verify the job has not run   
41:    System.assertEquals(0, ct.TimesTriggered);  
43:    // Verify the next time the job will run   
45:    System.assertEquals('2022-09-03 00:00:00', String.valueOf(ct.NextFireTime));  
46:    //System.assertNotEquals('testScheduledApexFromTestMethodUpdated',  
47:    // [SELECT id, name FROM account WHERE id =].name);  
49:    //Test.stopTest();  
51:    //System.assertEquals('testScheduledApexFromTestMethodUpdated',  
52:    // [SELECT id, name FROM account WHERE id =].name);  
54:    //Clean up  
55:    delete testContact;  
57:   }  
58:  }  


Friday, February 20, 2015

Add or remove Actions in Salesforce mobile app

Since version 7, Salesforce1 mobile app has replaced + icon to open Publisher Actions page with Action Bars and Action Menu. Before version 7, when user tap + icon, it will open the new page with multiple icons and paging.

In version 7, this is completely changed with Actions Bar.

Depending on which menu or record page user is viewing, the user will see different actions available in the action bar.

From Feed (or Chatter in the newer app version) and Today page, the user will see a set of actions (this may include Standard Chatter actions and Mobile Smart actions) defined in Global Publisher Layout, you can have multiple layouts and assigned to different profiles.

However, from a record page, the user will see a mix of actions and buttons added to object page layout:
  • Standard Chatter actions: such as Post, File, Link, Poll, Question, and Thanks.
  • Default actions: depend on the objects.
  • Mobile Smart actions: a set of preconfigured actions, just like default actions. They are available for account, case, contact, lead, opportunity, and custom object layout, including in Global Publisher layout.
  • Custom actions: actions that you create and customize, such as Create a Record, Update Record actions.
  • Productivity actions: depends on the objects. The actions include Call, Send Text, View Website, and Email.
  • Standard button: such as Change Owner, Change Record Type, Edit, Delete (action will be shown only if the user has the permission or the object fit the criteria, such as object with Record Type).
  • Custom button: buttons created with Display Type = Detail Page Button
If you have more than 5 icons, tap ... icon to open Action Menu which contains the full set of actions available for that page or record.

Global Actions
Appear in Feed (or Chatter in the newer app version) and Today page, by default Actions Bar will have icons of Post, File, New Event, New Task, and so on. How we can add/delete/re-order the icons?
  1. Navigate to SetupCreateGlobal ActionsPublisher Layouts
  2. You can have many layouts assign to different Profile. Select a Global Publisher Layout by click Edit link
  3. You will see available Actions available. You can add, remove, and re-order by drag and drop the icon in the Global Publisher

Create custom object record using Action
By default, Salesforce provides more than 10 default Global Actions, including create a record for standard objects such as New Account, New Case, and etc., also Email and Log a Case.

If you have custom objects, you will not see New action for those objects. You need to create Global Actions for each of them. Let us go through a sample:
  1. Navigate to SetupCreateGlobal Actions | Actions
  2. Click New Actions button
  3. Assume the object name is Service, enter Action Information as the screenshot below
  4. You will notice New Service now available in the available Actions
  5. Edit Global Publisher Layouts and drag New Service into Actions in the Publisher
create global action

add action to global publisher layout

new action added to global publisher layout and appear in Salesforce app

Object-Specific Actions
There are five types of object-specific actions:
  • Object-specific actions create records that are automatically associated with related records. For example, you add an object-specific action on the Account object that creates contacts. If a user creates a contact with that action on the detail page for the Acme account, that new contact is automatically associated with Acme.
  • Object-specific update actions make it easy for users to edit records. You define fields to be available when user update the record.
  • Object-specific Log a Call actions let users enter notes about calls, meetings, or other interactions that are related to a specific record.
  • Object-specific custom actions are Visualforce pages or canvas apps that let users interact with or create records that have a relationship to an object record.
  • Send email actions, available only in cases, give users access to a simplified version of the Case Feed Email action on Salesforce1
The default Object-Specific Actions available are depends on the object itself, check out this matrix.

You can configure object-specific actions from the object page layout, example for Account:
  1. Navigate to SetupCustomize | AccountsPage Layouts
  2. Look for Salesforce Mobile and Lightning Experience Actions, by default it will be inherited from the global publisher layout, but you can overwrite by click link override the predefined actions
  3. Mobile Smart Actions will be added by default, see reference below for more information. 

If the object has Lookup relationship with another object, example: Area custom object has a lookup relationship to Region custom object. You can create a Global Action to create Area record and add this global action to Global Publisher Layout. When the user executes the action from mobile or from Lightning Experience, Region will be blank, so the user needs to enter the Region manually.

For the same Area object, if we create new Area action from Region object, and add this object-specific action to Region layout, Region will be auto-populated in new Area layout.

Note: you can add Global Action to object page layout.

Now, let's see how this work for objects with Master-Detail relationship.
1. You can't create Global Action for a custom object which is a child in the Master-Detail relationship.
2. Only object specific action can be created for this kind of object, the action will be created from the parent object.

Let's see a sample on creating object specific action and adding the Action to the object layout, in this sample we will create Service (child) from Opportunity (parent).
  • Navigate to Setup | Customize | OpportunitiesButtons, Links, and Actions
  • Click New Action button and enter Action Information
  • You can define fields visible when the user clicks the Action.
  • Navigate to Opportunities Page Layout, find Action created and drag to Salesforce Mobile and Lightning Experience Actions

This is how it looks when the user selects Action menu:

Opportunity Name will be automatically populated:

Last update: 20-Mar-2018


Tuesday, February 17, 2015

Salesforce Community and User Group

This blog is dedicated to all Salesforce admins and Salesforce business owners.

After a successful Salesforce roll-out in your organization, you need to make sure the system is used by all users regularly to support the business, but as our business is changed over time, the same need to reflect in Salesforce, from the simple things like: adding new value to a picklist, add a custom field, create new reports, and keep going to more advance stuff like custom report type, record type, workflow and automation and et cetera....

How to get help? One of the easiest if your company have budget is to outsource to a consulting company. But, if no budget then how? Would you like to advance your Salesforce skill set and your career from a user, to admin and be a solution architect?

One of the best place to learn and advance your career is Salesforce Community. If you ever come to Success Community and post question in Answers, you may come across have Jeff May, Steve Molis, Deepak Anand answer your question in minutes after you post your question. I myself have answered for more than 7200 question in Answers over years. Why?

Even you know everything about Salesforce implemented in your organization, you remember all the picklist values in Account, Contact and Opportunities, but when come to changes or enhancements out of your existing configuration, are you able to give and configure the best solution? Remember that your organization may just use 20-30% of Salesforce capabilities, another 70-80% is for you to explore, learn and use it for free since you have paid Salesforce licences.

Salesforce community is about helping each other, when you post a question, you get your question answered for free within minutes, which is cool! When you answer someone question, you actually learn something and empowered for yourself, which you may use it someday when your company needed.

The pinnacle of your involvement with community, Salesforce may recognize you as MVP, where it is an acknowledgement of your commitment to collaborate, cooperate and evangelism of Salesforce platform to ALL users, so it is not an entitlement. After a long journey with Community and Salesforce platform, I was selected be one the MVP last year.

If you are located in Singapore, come to our quarterly User Group meetup, register yourself to our Community - If you are not in Singapore, look for Salesforce user group in your area -

With the spirit of Chinese New Year, I would like to wish everyone healthy and prosperity, spend your precious moments with your love ones in this new year!

Happy Chinese New Year everyone!!! Fu Xing Gao Zhao - "lucky star shining in the ascendant".

Saturday, February 7, 2015

Salesforce Report with Filter on Child object

In objects with parent-child relationship, we can run report with provided record type or create Custom Report Type. But when we would like to get all parent with and without child records, we need to create Custom Report Type and select "with or without related records from ...".

Since the fields from child objects are available, we can use it as Filter as well. But, issue arise when we would like to filter based on field in child object.

Use Case: We would like to show all Opportunities with Invoice Date from 1 Jan 2014 onwards for North region. Opportunity is the parent object and Invoice is a custom object with lookup to Opportunity, which is child object for this scenario.

Before add filter, let us see the whole data:

Notice we have 2 opportunities here without Invoices and 1 opportunity with 2 invoices. Now, let's add filter Invoice Date greater or equal "1/1/2014". This is the result

We do not expect the last 4 opportunities in the report because they are no longer fit the criteria, we just would like Opportunity with invoice date 1 Jan 2014 and onwards.

Let's analyse, since we are using report type, "with or without related records":
  • Safire Z and Maxima 2A do not have invoice, but since out report type is with or without related records, both opportunities here is fall into WITHOUT related records, the filter Invoice Date greater or equal "1/1/2014" is not effect.
  • Edge Emergency Generator and Edge SLA 2 have invoice, but the date do not fit criteria set, so the invoice is filter out, but the opportunity itself not, so it treat the same as above Opportunity without invoice.

Let's add cross-filter said just show Opportunities with Invoices.

Using cross-filter, we able to filter out Safire Z and Maxima 2A because they do not have invoice. But, Edge Emergency Generator and Edge SLA 2 will stay because have invoice, although it do not fit criteria of Invoice Date filter.

Instead of using custom report type "with or without related records", you need to use report type where Opportunities "with at least one related" Invoices.

Wednesday, February 4, 2015

Salesforce QR Code

QR code is getting popular as now most people carrying smart phone, we can install QR code reader for free. We often see QR code in many places, from print advertisement, shop ad., event registration, ticket, and etc. In QR Code, we can store many information inside the square code and it is readable easily by QR code reader.

However, as of now Spring '15 release, Salesforce still do not have with feature with QR code out of the box, there is an idea in IdeaExchange for QR code, but the point is still very low, currently only 30 points, it need to get 2500 points to get into Product Team Review stage, so vote it here now.

But, we can use 3rd party support to get QR code generated in Salesforce for multiple purposes. In this blog, we will use Google API to generate QR code, which is free.

You just need to create a simple formula field with return type = Text.

1. Search in Google
Use case: to search Account Name using Google without have to type the Account Name.
IMAGE(''+ Name, 'Scan the QR code to search in Google')

QR code generated will merge Account Name as parameter into Google search URL. User with QR Code reader can just scan the code and get Google search for the Account Name. You can use the same functionality to open other static website, such as Account website.

2. Add Contact to mobile phone
Since QR code is available in mobile phone, it is handy to add Contact to mobile phone.

Sample Google API with generate QR Code to add contact to mobile phone Johan;ADR:100 Orchard Road, Singapore, 200100;TEL:+6565881234;;

Let us try to understand each parameter:
  • chs : image size -- chs=<width>x<height>
  • cht : qr -- specifies a QR code
  • chl : <data>, we use MECARD in here
You can think of MeCard as a light version of vCard. Essentially, a single line of text contains all the meta data and appropriate information for populating an address book. Info such as name, address, telephone, email, URL and more. 

The meCard format is far less popular than the vCard and the MeCard can only be used in QR-codes, and on mobile devices. The meCard specification was created by Docomo, Japan’s largest mobile service provider. According to the Domoco website, the meCard fields listed below are supported on the greatest number of mobile devices:
  • Name
  • Telephone 
  • eMail 
  • Birthday 
  • Address
Back to sample above, we can easily merge Salesforce Contact fields into above URL and add into IMAGE() formula:

IMAGE('' +  FirstName + ' ' + LastName  +';ADR:'+ MailingStreet +',' + MailingCity + ',' +  MailingState  + ',' + MailingCountry + ',' + MailingPostalCode + ';TEL:'+  MobilePhone  +';EMAIL:' +   Email  + ';', 'Scan the QR code to create contact in your mobile')

As of now, Google Chart Tools: Infographics has been deprecated, but you can use the same formula with other QR code generator, such as: Visualead or

Page-level ad