Sage Accpac Community
Accpac Support Community
Search in


Over the last 7 years, Rob has been asked by the SageCRM Community hundreds if not thousands of questions on how do you do this inside of SageCRM? Rob has noticed a very distinctive pattern develop over these last 6 years as the respective members of the SageCRM Community have joined and advanced their knowledge of SageCRM. The questions have gone from basic to advanced/techie/nerdy as each member’s knowledge and implementation experience has grown. The intent of this blog is to help you as a member of the SageCRM Community to move your SageCRM knowledge to the next level - what ever that level might be.
  • SageCRM - Component - LinkedIn Example - Lead Company

    Description:

    “LinkedIn integration to Leads adds LinkedIn button next to lead company and shows if that a company is on LinkedIn Site and their details”.  LinkedIn integration to the Lead Entity adds LinkedIn button next to lead company and shows if that person is on LinkedIn Site and their details.

    For step by step instructions with pictures please download the associated pdf file here.  This link is to a single file zip that contains the two files the instructions and the actual component.

    • "SageCRM Linkedin Example - Lead v1.0.pdf” = instructions
    • “LinkedInLeadsv1.zip” = the actual SageCRM Component

    How to Install:

    To install a component first you must download it from the location mentioned above.  For this example let us assume the downloaded file is been saved to your desktop.  Now login into SageCRM and navigate to Administration | Customization | Component Manager, as shown in the print screen below.  If this is the first time a component has been installed on this instance of SageCRM.

    Once this screen appears select the Browse button and navigate to the component that is saved to your desktop. 
     
    Select the file and then select Open, now you should see the just selected component listed on the screen.
     
    Once the file is selected now click Upload New Component paper clip next to the Browse button.  At this point you should see a panel called Available Components.
     
    At this point the Available Component that was just installed should be selected and the Install Component button should be clicked on to run the installation of the actual Component.
    Typically, the first screen presented will be a set of messages/warnings/disclaimers and/or parameters related to the specific component being installed.
     
    Once all the messages/warnings/parameters have been followed or selected click the “Install Component” button found on the right side of the screen.

    You should now see the steps of the component being installed.

    At this point several processes will be performed.  WAIT! Do NOT TOUCH or navigate away from this screen until you see a continue button and a notification of “Component Installed”.

    At this point, click the Continue button.

    Now that the component is installed it is ready to be used.  The Component Manager screen now shows the just installed component in the Component list below the Add Component Block/Panel.

    What the Component will do:

    If you navigate to a Lead record you will see a little “in” button/icon next to the Lead Company Name.  If a SageCRM user clicks on this icon they will be presented with the LinkedIn Information related to the Lead Company in context.

    Buttons Added:

    “in” button next to a Company Name on the Company Entry Screen.


    The End

    with no comments
  • SageCRM - LinkedIn Example - Company

    Description:

    LinkedIn integration to companies adds a LinkedIn button next to company and searches for all persons within that company on LinkedIn Site

    For step by step instructions with pictures please download the associated pdf file here.  Download the files from the actual BLOG article. This link has two files the instructions and the actual component.

    • “SageCRM linkedin Example - Company v3.doc” = instructions
    • “LinkedInCompaniesv2.zip” = the actual SageCRM Component

    Requirements:

    For this example to work:

    • You must have access to an internet connection.
    • You must have a login to the LinkedIn site and have selected the option for the LinkedIn site to remember your login.

    If you do not do these two things you will get an error message on the page.
     
    How to Install:

    To install a component first you must download it from the location mentioned above.  For this example let us assume the downloaded file is been saved to your desktop.  Now login into SageCRM and navigate to Administration | Customization | Component Manager, as shown in the print screen below.  If this is the first time a component has been installed on this instance of SageCRM.
     
    Once the screen appears select the Browse button and navigate to the component that is saved to your desktop. 
     
    Select the file and then select Open, now you should see the just selected component listed on the screen..
     
    Once the file is selected now click Upload New Component paper clip next to the Browse button.  You should see a panel called Available Components.
     
    At this point the Available Component that was just installed should be selected and the Install Component button should be clicked on to run the installation of the actual Component.
    Typically, the first screen presented will be a set of messages/warnings/disclaimers and/or parameters related to the specific component being installed.
     
    Once all the messages/warnings/parameters have been followed or selected click the “Install Component” button found on the right side of the screen.
    In the print screens below you can see the steps of the component being installed.  At this point several processes will be performed. 

    WAIT! Do NOT TOUCH or navigate away from this screen until you see a continue button and a notification of “Component Installed”.
     
    At this point, click the Continue button.
     
    Now that the component is installed it is ready to be used.  The Component Manager screen now shows the just installed component in the Component list below the Add Component Block/Panel.

    What the Component will do:

    If you navigate to a Company record you will see a little “in” button/icon next to the company name.  If a SageCRM user clicks on this icon they will be presented with the LinkedIn Information related to the company in context.  See the following print screen for an example using company “Mr. Ronald Black”.
     
    Buttons Added:

    “in” button next to a Company Name on the Company Entry Screen.

     

    The End.

    with no comments
  • How to configure a Blackberry Pearl 8100 to be used with SageCRM on-premise

    Summary:

    The purpose of this whitepaper is to provide instruction on how to configure a Blackberry Pearl 8100 to be used with SageCRM on-premise.

     For step by step instructions with pictures please download the associated pdf file here.

    Assumptions:

    There are a few settings that need to be verified and checked.  Please note that these suggestions are not officially supported by Sage Accpac or SageCRM Technical Support and have not gone through Q.A.  I have used these settings with SageCRM 6.2 and SageCRM 6.1.  I have NOT tested these settings on earlier versions of SageCRM. 

    Additionally, these settings have not been tested on any other device and SageCRM has not been certified or Q.A.’ed  to work with any other Blackberry device other than the Pearl 8100.  For any updated information please reference the official SageCRM product documentation “SageCRM Wireless Mobile Guide.pdf” which can be found on the SageCRM CD under the documentation folder.  However, you are welcome to try these settings on other Blackberry devices you may find varying levels of success with other Blackberry devices.

    Prerequisites:

    SageCRM 6.2 or 6.1 with Mobile access included with the license key and a Blackberry Pearl 8100 with internet access.

    Installation Instructions:

    Here is a summary of the steps required to be completed to test if your Blackberry Pearl 8100 will work with SageCRM.

    Step 1: What is required within the SageCRM license key?
    Step 2: What to configure inside of SageCRM?
    Step 3: What has to be configured on your BlackBerry Pearl 8100 to get the full functionality of SageCRM?
    Step 4: What is the correct URL to use within the BlackBerry Pearl 8100 to connect to SageCRM?
    Step 5: What other BlackBerry’s does this work on?

    Step 1: What is required within the SageCRM license key?

    1. You will need to verify that your license key includes Mobile (PDA/WAP).  To do this
      go to Administration | System | System Behavior.
    2. Look at the License Key block and verify that your licnese key includes Mobile(PDA/WAP access).

     Step 2: What to configure inside of SageCRM?

    1. Once the license key is verified and valid go into user setup and allow your users access to SageCRM via the option of PDA/WAP access.  Navigate to Administration | Users | Users | [selected_user_record].
    2. The PDA/WAP Access value must be set to True for the specific user.
    3. Then click on the Save button to commit the changes to the database.

    Step 3: What has to be configured on your BlackBerry Pearl 8100 to get the full functionality of SageCRM?

    1. From the home page of your BlackBerry device select the Browser option.
    2. Next select the Menu button, then select the Options, Figure 1. (see the PDF for images)
    3. Within Figure 2, match your Browser Configuration by selecting (checking) the highlighted options.

      Support JavaScirpt
      Allow JavaScript popups
      Support HTML Tables
      Use Foreground And Background Colors
      Use Background Images
      Support Style Sheets
    4. Within Figure 3, make sure the highlighted values are selected.

      Style sheets media type = Handheld
      Show Images = On WML & HTML Pages
      Emulation Mode = BlackBerry
      Content Mode = WML & HTML

    Step 4: What is the correct URL to use within the BlackBerry Pearl 8100 to connect to SageCRM?

    This is a sample url that you might have to connect too.

    https://northamerica.sagecrm.com/sagesoftware128lvujkh/eware.dll/do?

    https://[server_name]/[instance_name]/eware.dll/do?

    The server name in the above example is “northamerica.sagecrm.com”, replace this with your server name. Then in the above example the instance name is “sagesoftware128lvujkh”, replace this with your instance name.

    PLEASE NOTE you MUST type in the full URL for the browser to work with SageCRM correctly.  Which MEANS that the forward slash eware.dll forward slash do question mark (/eware.dll/do?) must be included in what you type into the browser of the Blackberry device.

    Step 5: What other BlackBerry’s does this work on? And does it work on them?

    Currently I have tried these settings on a BlackBerry 8700.  No, it does NOT work fully.  The page loads, the login works, however the drop downs do not work so you can not navigate through the SageCRM.  I have also tried this on the AT&T Curve 8310 and these settings seem to work.  I have been able to navigate around and use the drop downs.  I have not tried any OTHER BlackBerry’s have been tried.

    PLEASE REMEMBER that these settings are what I have found that makes these devices work correctly.  The additional devices that are mentioned above have not been tested or Q.A.’ed and as such are NOT supported by the Sage Accpac or SageCRM Technical Support Teams.  

    The End 

    with no comments
  • SageCRM Custom Grid Size under the Preference tab for a User

    Summary:

    The purpose of this whitepaper is to provide instruction on an alternate method for customizing the Grid Size available within the SageCRM | My CRM | Preference tab.  This topic came to me from Bill Hoffman via an article posted by Kristi Feng on Terrell & Terrell, CPAS, LLP website, http://www.bterrell.com/blog/sagecrm-grid-size/.  For details on how to manually add the translation value see Kristi’s article.  This article will discuss the method that can be used IF you didn’t know anything about how to use SageCRM’s Translations.

     For step by step instructions with pictures please download the associated pdf file here.

    Assumptions:

    During this discussion I will be presenting how to add additional custom grid size values under the My CRM | Preference tab.

    Prerequisites:

    SageCRM 6.1 or Newer. (This may work on earlier versions; I just have not tested it on anything older.  Also, this will work with SageCRM, SageCRM.com, SageCRM for Accpac ERP, and MAS EES.)

    Installation Instructions:
    Detail Steps:

    1. Log into SageCRM with Administrator rights.
    2. Navigate to Administration | Customization | Translations.
    3. Check the check box for Inline customization.
    4. Now navigate to Main Menu | My CRM | Preferences tab.
    5. Click on the Change button.
    6. Find the Grid Size field and click on the Star Underscore NEXT to the drop down box.
    7. Once you have the “Maintain Lookup Selections for UserGridSize” up on the screen, click on one of the existing values to see what the code is and the translation that are used for the other examples.  An example of the Translation is 25 and the code is also 25.
    8. So based on that example type into the “Add Translation:” field 50 and then type into the “Code:” field 50.
    9. Then click on the Add button.
    10. Then Click on the Save button.
    11. Navigate back to Administration | Customization | Translation Tab.
    12. Then uncheck the Inline Translation Mode.
    13. Navigate back to Main Menu | My CRM | Preferences Tab.
    14. Click on Change and change the Grid Size value to 50 and click Save.
    15. Then right click on the Main Menu | Finder | select Company, then select the finder button on the right side of the screen.  This will bring back every Company in the database.
    16. Your results grid will now contain 50 rows of companies per page.
    17. The End.
    with no comments
  • SageCRM - LinkedIn Example - Person

    Summary:
    The purpose of this article is to provide instruction on how to add in a link to LinkedIn onto the Person Summary Screen.

    For step by step instructions with pictures please download the associated pdf file here.

    Assumptions:
    During this discussion I will be presenting how to link the Person Entry Screen to LinkedIn.  

    Please note this code assumes that the user is always on-line, i.e. connected to the internet, errors will result if an attempt is made to use the following documented behavior without an internet connection.

    Additionally, this code assumes that the user has a current LinkedIn account and has configured their LinkedIn login to be remembered so that the user merely has to navigate to the site to have access to their LinkedIn profile.  If the user chooses to log-in to the LinkedIn site each time they visit LinkedIn then this sample code will require this login to occur every time.

    Prerequisites:
    SageCRM 6.2, this code has been written in a way that will allow it to function within SageCRM on-premise and/or SageCRM.com.  There are other ways of achieving the same result which require the use of Server Side code, in an effort to make this code work for all of our existing clients I modified the code shared with me to work client side only.

    Installation Instructions:
    1. In order to use this customization you must navigate to Administration | Customization | Person Entity | Screens Tab | select the Person Summary Screen.

    2. Once you are in the properties for the screen copy the below code and paste it into the Custom Content for Person Entry Screen.

    Beginning of Code

    <script>
    window.onload = function(){
    var persfirstname = document.getElementById('_Datapers_firstname').innerText;
    var perslastname = document.getElementById('_Datapers_lastname').innerText;
    var compname = document.getElementById('_Datacomp_name').innerText;
    var captpersfirstname =

    document.getElementById('_Captpers_firstname').innerText;
    var xlink = "http://www.linkedin.com/search?search=&lname="+perslastname+"&fname="+persfirstname+"&company="+ compname;
    var xURL =  "<a href=" + xlink + " target=_blank><img src=http://www.linkedin.com/img/icon/icon_company_insider_in_12x12.gif border=0 title='Find in LinkedIN'></a>";   
    document.getElementById('_Captpers_firstname').innerHTML += xURL;  
    }
    </script>

    End of Code

    3. Once the code is copied into the screen, click the save button to save the screen modifications.
     

    Testing Instructions:

    1. To test this customization navigate to the Main Menu and find or select a Person record.
    2. Once a person record is selected a LinkedIn icon should appear next to the label for the person first name.
    3. Click on the LinkedIn icon, a separate screen should appear launching and navigating the user to the Person.

    A special thanks to the following two chaps for contributing to the final solution.  Without their assistance this would not have been possible.

    Alistair Hill
    Consultant
    Plus Computer Solutions Ltd.
    e-mail: alistair@plus.ca
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Ken Schmitt
    Advanced CRM Certified Consultant
    London ON, Canada
    e-mail: ken@schmitts.ca

    The End

    with no comments
  • How to Add a e-mail address Part 3 - From the person entity to the NEW Company Entry Screen

     
    Summary:

    The purpose of this article is to provide instruction on how to add an e-mail address to the company entity.  There have been several articles written on this before.  I am going to attempt to combine these other articles and simplify the topic and focus on a very specific area of adding an email address to the company entity.  Jeff Richards has written an article on the DPP BLOG “Working with a persons address type” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2009/04/14/working-with-a-person-s-address-type.aspx and another article titled “Email and Phone Numbers in CRM” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2007/07/16/email-and-phone-numbers-in-crm.aspx.  These articles provide insight into the process we are about to go through.  So, this article will not discuss the why and what for’s the previously mentioned articles have addressed these areas.  This article will apply these two concepts to the email address block on the company entity.  This article is a part of a 3 part series, part one is “How to Add a e-mail address Part 1 - To the person entity” and finally part two is “How to Add a e-mail address Part 2 - To the company entity”.

    The logic presented here can also be used for additional email addresses as well.  If you have ever tried to add an email address to the company email address block you have found it impossible to do so.  This block is not editable and is basically hardcoded into SageCRM’s eware.dll.  So, if this block is hard coded how can another email address be added to the block?  Will a field can NOT be added to the email block.  But then we really don’t want to add a field we want to use the same behavior of the email address for the company and have another option available or Selection list value available for users to enter.  The email address block is simply presenting on the screen the options available for the company email address that are stored inside of the table for the email address and in the translations table.

    For step by step instructions with pictures please download the associated pdf file here.

    Assumptions:

    During this discussion I will be presenting how to add the New Email Address we added in Part 1 of this series to the NEW Company Entry Screen using SageCRM 6.2 WITHOUT adding an actual field to the SageCRM database.

    Prerequisites:

    SageCRM 6.2

    Installation Instructions:

    There are 3 steps to this process:

    Step 1: Define the pattern of the existing person email addresses on the New Company Entry screen.

    Step 2: Adding a new translation of “Google G-mail” on to the Personal Email Details Panel of the New Company Entry Screen

    Step 3: Verifying that the Person Email address type of “Google G-mail” appears on the New Company Entry screen.

    Step 1: Define the pattern of the existing person email addresses on the New Company Entry screen.

    1. Navigate to Administration | Customization | Translations. 

    2. Enable Inline Translation Mode.

    3. Navigate to Main Menu | New | Company. (If deduplication is enabled enter a made up company name and click the button to enter company details, we will not be saving the company record.)  We just need to view the company entry screen.

    4. Scroll until you find the Personal Phone Details and Personal Email Details Panels (i.e. Blocks) on the New Company Entry Screen. (They are past the Person panel.)

    Notice that on this screen there is only one Personal E-mail address available.  While on the Person’s Phone/E-mail panel there are Business, Private, and Google G-mail (the email address that was added in part one of this series).

    5. Back on the New Company Entry Screen select the star under score next to the label for the Business email address.  A pop-up screen will then be presented with the relevant information for the translation you just clicked on.

    6. Note that the following values:

    Caption Code = Business
    Caption Family = Link_PersEmail_Comp
    Caption Family Type = Links
    Caption Order = 0
    Us Translation = Business

    7. Close the Inline Translation window that popped up.

    8. Step 1 is now complete please proceed to Step 2.

     

    Step 2: Adding a new translation of “Google G-mail” on to the Personal Email Details Panel of the New Company Entry Screen. 

    1. Now we take this information and navigate back to Administration | Customization | Translations.

    2. Disable Inline Translation Mode.

    3. Within the Translation Finder on the same screen type in the Caption Family of “Link_PersEmai” and click the find button on the right.

    4. The results should show you at least 3 translations:

    Business
    Google G-mail
    Private
    Business (repeated)

    Do you notice a pattern here?  Notice that the Business email address shows up twice.  Why is that?  Will the answer is that the second Business email address is the address we just looked up for the New Company Entry Screen.  But the difference is that now we know the pattern of how to add OTHER person email address fields to the New Company Entry Screen. 

    5. So logic dictates that if we add ANOTHER translation following the same naming convention then we should be able to add another person email address to the New Company Entry screen.  

    6. Lets add the custom email address we created from part one “Google G-mail” to the New Company Entry Screen.
    Click on the New button next to the Translation finder.

    7. Once the screen appears type in the following values:

    Caption Code   = GoogleCompanyPerson
    Caption Family  = Link_PersEmail_Comp
    Caption Family Type = Links
    Caption Order  = 3
    US Translation  = Google G-mail

    8. Click the Save button once all the values are entered.

    9. Click the Continue button.

    10. Step 2 is now complete please proceed to Step 3.


    Step 3: Verifying that the Person Email address type of “Google G-mail” appears on the New Company Entry screen.

    At this point we need to verify that the translation was added to the custom_captions table and that it appears on the New Company Entry Screen.

    1. Before leaving Translations the finder should have refreshed after clicking the continue button.

    2. At this point 5 translations should appear with the Caption Family of “Link_PersEmai”.

    3. This verifies that the translation has been successfully added to the custom_captions table.

    4. The second part of this verification step is to check and make sure the new Person Email address type appears on the New Company Entry screen. Navigate to Main Menu | right click on New | Select Company.

    5. Scroll down to the Personal E-mail Details Panel.

    6. The “Google G-mail:” option should now just appear in the list of Email addresses.

    7. If you see field on the screen you are done.  If not go back through your steps and verify that you added the translation correctly and repeat the necessary steps to correct the error.

    with no comments
  • How to Add a e-mail address Part 2 - To the company entity

    Summary:

    The purpose of this article is to provide instruction on how to add an e-mail address to the company entity.  There have been several articles written on this before.  I am going to attempt to combine these other articles and simplify the topic and focus on a very specific area of adding an email address to the company entity.  Jeff Richards has written an article on the DPP BLOG “Working with a persons address type” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2009/04/14/working-with-a-person-s-address-type.aspx and another article titled “Email and Phone Numbers in CRM” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2007/07/16/email-and-phone-numbers-in-crm.aspx.  These articles provide insight into the process we are about to go through.  So, this article will not discuss the why and what for’s the previously mentioned articles have addressed these areas.  This article will apply these two concepts to the email address block on the company entity.  This article is a part of a 3 part series, part one is “How to Add a e-mail address Part 1 - To the person entity” and finally part three is “How to Add a e-mail address Part 3 - From the person entity to the NEW Company Entry Screen”

    The logic presented here can also be used for additional phone numbers as well.  If you have ever tried to add an email address to the company email address block you have found it impossible to do so.  This block is not editable and is basically hardcoded into SageCRM’s eware.dll.  So, if this block is hard coded how can another email address be added to the block?  Will a field can NOT be added to the email block.  But then we really don’t want to add a field we want to use the same behavior of the email address for the company and have another option available or Selection list value available for users to enter.  The email address block is simply presenting on the screen the options available for the company email address that are stored inside of the table for the email address and in the translations table.

    For step by step instructions with pictures please download the associated pdf file here.

    Assumptions:

    During this discussion I will be presenting how to add an e-mail address to the company entity using SageCRM 6.2.   WITHOUT adding an actual field to the SageCRM database.

    Prerequisites:

    SageCRM 6.2

    Installation Instructions:

    There are 3 steps to this process:

    Step 1: Define the pattern of the existing email addresses on the person entity.
     
    Step 2: Adding a new translation of “Google G-mail” for the Company Entity email address. 

    Step 3: Verifying “Google G-mail” appears on the Company Summary | Phone/E-mail Panel.

    Step 1: Define the pattern of the existing email addresses on the person entity. 

    1. Navigate to Administration | Customization | Translations.

    2. Enable Inline Translation Mode.

    3. Navigate to Main Menu | New | Company. (If deduplication is enabled enter a made up company name and click the button to enter company details, we will not be saving the company record.)  We just need to view the company entry screen.

    4. Scroll until you find the Phone and Email Panels (i.e. Blocks) on the Company Entry Screen.

    5. Select the star under score next to the label for the Business email address.  A pop-up screen will then be presented with the relevant information for the translation you just clicked on.

    6. Note that the following values:

    Caption Code = Business
    Caption Family = Link_CompEmai
    Caption Family Type = Links
    Caption Order = 1
    Us Translation = Business

    7. Close the Inline Translation window that popped up.

    8. Step 1 is now complete please proceed to Step 2.

    Step 2: Adding a new translation of “Google G-mail” for the Company Entity email address. 

    1. Now we take this information and navigate back to Administration | Customization | Translations.

    2. Disable Inline Translation Mode.

    3. Within the Translation Finder on the same screen type in the Caption Family of “Link_CompEmai” and click the find button on the right.

    4. The results should show you at least 3 translations:

    Business
    Sales:
    Support:

    Do you notice a pattern here?  Do these values look familiar to a screen we saw earlier?

    So we have discovered that all three of the email addresses on the company entry screen are actually related to the same caption family.

    5. So logic dictates that if we add ANOTHER translation following the same logic then we should be able to add another email address to this screen.
     
    6. Click on the New button next to the Translation finder.

    7. Once the screen appears type in the following values:

    Caption Code   = GoogleCompany
    Caption Family  = Link_CompEmai
    Caption Family Type = Links
    Caption Order  = 4
    US Translation  = Google G-mail:

    8. Click the Save button once all the values are entered.

    9. Click the Continue button.

    10. Step 2 is now complete please proceed to Step 3.

    Step 3: Verifying “Google G-mail” appears on the Company Summary | Phone/E-mail Panel.

    At this point we need to verify that the translation was added to the custom_captions table and that it appears on the Company Summary Tab | Phone/E-mail panel.

    1. Before leaving Translations the finder should have refreshed after clicking the continue button.

    2. At this point 4 translations should appear with the Caption Family of “Link_CompEmai”.
     
    3. This verifies that the translation has been successfully added to the custom_captions table.

    4. The second part of this verification step is to check and make sure the new Person Email address type appears on the screen. Navigate to Main | Menu and find and select a company entity.

    5. Click on the Phone/E-mail Tab.

    6. The “Google G-mail:” option should now just appear in the list of Email addresses.

    7. If the email address shows populate the value with an email address and click on Save. (Any value will do.)

    8. Navigate to the Company | Summary Tab.

    9. You should now see the “Google G-mail” value populated in the Phone/E-mail panel.

    10. If you see this value you are done.  If not go back through your steps and verify that you added the translation correctly and repeat the necessary steps to correct the error.

    with no comments
  • How to Add an e-mail address Part 1 - To the person entity

     Solution:

     Several steps need to be performed within SageCRM to complete this customization.  For step by step instructions with pictures please download the associated pdf file here.

     Summary:

    The purpose of this article is to provide instruction on how to add an e-mail address to the person entity.  There have been several articles written on this before.  I am going to attempt to combine these other articles and simplify the topic and focus on a very specific area of adding an email address to the person entity.  Jeff Richards has written an article on the DPP BLOG “Working with a persons address type” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2009/04/14/working-with-a-person-s-address-type.aspx and another article titled “Email and Phone Numbers in CRM” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2007/07/16/email-and-phone-numbers-in-crm.aspx.  These articles provide insight into the process we are about to go through.  So, this article will not discuss the why and what for’s the previously mentioned articles have addressed these areas.  This article will apply these two concepts to the email address block on the person entity.  This article is a part of a 3 part series, part two is “How to Add a e-mail address Part 2 - To the company entity” and finally part three is “How to Add a e-mail address Part 3 - From the person entity to the NEW Company Entry Screen”

    Assumptions:

    During this discussion we will be presenting how to add an e-mail address to the person entity.

    Prerequisites:

    SageCRM 6.2

    Installation Instructions:

    There are 3 steps to this process:

    • Step 1: Define the pattern of the existing email addresses on the person entity. 
    • Step 2: Adding a new translation of “Google G-mail” for the Person Entity email address. 
    • Step 3: Verifying “Google G-mail” appears on the Person Summary | Phone/E-mail Panel.

    To begin with we need to define what we will be changing.  Over the years I have been asked how to add an email address to the person summary tab, Phone/E-mail pane.  I have for many years told partners and customers this was not possible.  Will, I was wrong.  After noticing a pattern with which the person’s email addresses were named I realized that adding email addresses or phone numbers to this block was possible WITHOUT having to add a custom field to the person entity.

     
    Step 1: Define the pattern of the existing email addresses on the person entity. 

    We need to find the translation for the email addresses on the phone/e-mail panel.

    a. Navigate to Administration | Customization | Translations | check the box for Inline Translation Mode.
    b. Navigate to a person record.
    c. Click on the Phone/E-mail tab of the person record.
    d. Select the star underscore next to the private email address type. 
    e. This should then show the translation for the private label.
    f. Note the following values.

    • Caption Code = Private
    • Caption Family = Link_PersEmai
    • Caption Family Type = Links
    • Caption Order = 2
    • US Translation = Private

    g. Now navigate back to Administration | Customization | Translations.
    h. Un-check the box for Inline Translation Mode.
    i. Stay in the Translations area. 
    j. Let’s now search for the Caption Family value we found earlier, “Link_PersEmai”.  SageCRM should return at least 3 translations.
    k. So, what has this shown us?  This has shown us that we have found the correct caption family.  ADDITIONALLY, this has shown us a hint, “Link_PersEmail_Comp”, that relates to our Part 3 article “How to Add a e-mail address Part 3 - From the person entity to the NEW Company Entry Screen”.
    l. Now that we know the translation information required to add our new email address type we can proceed to step 2.

    Step 2: Adding a new translation of “Google G-mail” for the Person Entity email address. 
    This could be done for AIM, Yahoo, MSN or any other specific email account a customer may want to capture for a
    person entity.

    a. While you are still in Translation (Administration | Customization | Translations) click on the New button.
    b. Enter the following values in to the New Translation screen.
    a. Caption Code = Google
    b. Caption Family = Link_PersEmai
    c. Caption Family Type = Links
    d. Caption Order = 3
    e. US Translation = Google G-mail
    f. Click the Save button once all the values are completed.
    g. Click the Continue button.
    h. Step 2 is now complete please proceed to Step 3.

    Step 3: Verifying “Google G-mail” appears on the Person Summary | Phone/E-mail Panel
    At this point we need to verify that the translation was added to the custom_captions table and that it appears on the Person Summary Tab | Phone/E-mail panel.

    a. Before leaving Translations the finder should have refreshed after clicking the continue button. 
    b. At this point 4 translations should appear with the Caption Family of “Link_PersEmai”.  
    c. This verifies that the translation has been added to the custom_captions table successfully. 

    The second part of this verification step is to check and make sure the new Person Email address type appears on the screen.

    a. Navigate to Main | Menu and find a person entity.
    b. Click on the Phone/E-mail Tab.
    c. The “Google G-mail:” option should now just appear in the list of Email addresses.
    d. If the account shows populate the value with an email address and click on Save.
    e. Navigate to the Person | Summary Tab.
    f. You should now see the “Google G-mail” value populated in the Phone/E-mail panel.
    g. If you see this value you are done.  If not go back through your steps and verify that you added the translation correctly and repeat the necessary steps to correct the error.

    Recap
    We have been able to add an email address to the person entity with out having to use Code and with out adding a custom field to the database.

    FAQ?

    Q: Is this option available for data upload?
    A: Yes

    Q: Can we follow the same logic for Phone numbers?
    A: Yes, I will write a separate article illustrating the Phone number example as well.

    Q: What and how did this work?
    A:  See the articles written by Jeff Richards on the DPP BLOG for back ground. “Working with a persons address type” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2009/04/14/working-with-a-person-s-address-type.aspx and another article titled “Email and Phone Numbers in CRM” http://dpp.sagecrm.com/blogs/hints_tips_and_tricks/archive/2007/07/16/email-and-phone-numbers-in-crm.aspx

    The end

    with no comments
  • Troubleshooting tips for SageCRM Email Management using MAPI

    Question from Partner:

    We are trying to use the MAPI settings for SageCRM's Advanced Email Management.  However, the values we are using for the specific MAPI settings do not appear to be working.  The Exchange server is on the same box as the CRM Server.  The IUSR account has administrator rights as does the user ‘tester’ on the domain.  The IMAP4 service runs as a local service.  The mailbox checking service worked fine when we were using POP3 as the access method.  Switching to MAPI presents the problem attached whereby the service cannot find the message store.

    Email Management Settings


    Email manager log:

    2009-07-30 16:54:12.312> Email Service "Tester" Started
    2009-07-30 16:54:12.343> **************************RulesScript is...*********************
    2009-07-30 16:54:12.343>
    if (bCond)
    {
    AssignedUser = 1;
    AssignedChannel = 0;
    }
    2009-07-30 16:54:12.359> **************************End of RulesScript.*******************
    2009-07-30 16:54:12.359> **************************about to run job....**************************
    2009-07-30 16:54:12.359> Mailbox: Tester
    2009-07-30 16:54:12.359> About to connect to mail server.
    2009-07-30 16:54:12.453> Mailbox: Tester
    2009-07-30 16:54:12.453> MessageStore "Tester" not found
    2009-07-30 16:54:12.453> Mailbox: Tester
    2009-07-30 16:54:12.453> Connected to mail server.
    2009-07-30 16:54:12.453> **************************finished job run.**************************
    2009-07-30 16:54:12.453> Sleep Period = 180 seconds
    2009-07-30 16:54:22.140> Mailbox: Tester
    2009-07-30 16:54:22.140> Email Service "Tester" Stopped

    Solution:

    There are several items you can check to determine what the problem maybe:

    1)  The mailmanager process needs to run as a user that you can log in as (so you can create the MAPI profile specific to the NT profile)
    2) You need outlook installed to create the MAPI profile
    3) The MAPI profile is created in Control Panel | Mail (there are allegedly other ways but never got them to work)
    4) The mailbox is usually called something like "Mailbox - Username"

     Email Management MAPI user name

    Thanks to the Partner for the question: Stuart.
    Special Thanks to the resource that provided the answer for this question: Martin.

    with no comments
  • How to Add a divider or subsection into an existing Block within SageCRM

    Summary:

    The purpose of this paper is to provide instruction on how to add a divider or subsection into an existing Block within SageCRM and or SageCRM.com.  This article has also been compiled into a PDF file with images, to download this file click here.

    Assumptions:

    This example assumes that SageCRM on-premise is being used.  Where this will or will not work with SageCRM.com will be mentioned through out the document.

    Prerequisites:

    SageCRM 6.2

    Installation Instructions:

    Part 1 (This works for SageCRM and/or SageCRM.com):

    I add a divider into my person summary area all the time here are the instructions to make this happen.

    1. Add the field pers_blank as a text field

    a. In order to do this navigate to Administration | Customization | Person | Fields Tab
    b. Click the new field button.
    c. Select the field type as text.
    d. Type in the field name of pers_blank
    e. For the translation put JUST the tag of <hr>
    f. Save the field

    2. Once the field is added and while you are on the Field tab for the Person entity lets set the field security for this pers_blank field.

    a. In order to do this scroll until you find the field
    b. Select the field security icon at the end of the field properties list
    c. Once you are in the field security area set the field security as READ ONLY for everyone
    d. Note:  This does NOT apply on the finder so you will need to do this with the create script of the field if you add it to the finder

    3. Now add the field to the Entry Screen for the Person entity

    a. To do this select the Screens Tab for the Person entity
    b. Then select the Person Entry Screen properties button
    c. Once the existing fields on the screen appear on the left look to the right of the list
    d. Here you will click on the drop down list and select the pers_blank field
    e. Set the property of the field to 1 column and 5 rows
    f. Set the property of the field to be New line
    g. Click the add button to add the field to the screen.
    h. Then with the up and down arrows move the field to where you want it to divide the fields.
    i. IMPORTANT NOTE: Set the property of the NEXT field in the list of fields to New Line as well.
    j. Once all these changes have been done click the update button to update the change to the line
    k. Then click the save button to save all the changes made to the screen.

    4. The result of this is that you get a horizontal divider which is actually a field set to read only.

     
    Part 2 (This works for SageCRM on-premise ONLY, it does NOT work for SageCRM.com):

    The second part of this conversation revolves around the issue of SageCRM’s default behavior coming into play.  If I do the above example field pers_blank then you will get also a colon at the end of the line that was just created.  The question then becomes, how do you solve this issue?

    1. To make finding the field easier navigate back to the field properties and change the translation back to an actual field name such as “Blank line” instead of the <hr> tag we set it to originally or what ever you want to call it in the field property.

    2. Now navigate back to the Person Entry Screen and select the field we just renamed back to “Blank line”. 

    3. On the field properties add the following code to the create script box:

    Beginning of Code

    Caption = ‘<hr>’;

    End of Code


    And that should overwrite the caption and remove the “:”.

    In another example I have used the same logic on the Company Summary Screen. 

    The following is the code I applied to the Company summary screen:

    Beginning of Code

    Caption = '<hr><b><font color=green>Credit Card Information:</b><br><hr></tr><tr>'

    End of Code

     
    Which looks like something like this with the actual Credit Card Information

    Text being green and actually having two lines one above it and one below it.

    The end.

    with no comments
  • SageCRM Tips & Tricks - How to show a message within SageCRM when a customer is ON HOLD within Sage Accpac ERP

    Scenario:

    When a sales person logs into SageCRM they need to know when a customer is ON Hold within the Accounting System.

    Solution Assumptions:

    This solution assumes that both SageCRM and Sage Accpac ERP are using SQL Server.  This has been tested and or built using SageCRM 6.1 or 6.2 and Sage Accpac ERP 5.4 or 5.5.

    Solution:

    To make this possible several steps need to be performed within SageCRM.  For step by step instructions with pictures please download the associated pdf file here.

    Step 1:

    We need to connect directly to the Sage Accpac ERP Database.  Within SageCRM Navigate to “Administration | Advanced Customization | Tables and Databases” and select the “New Database Connection” button.

    Once the button is selected you will see a “Database Details” screen.  You will need to match the values in the print screen below to your actual production environment.

    Database Driver: Microsoft SQL Server
    Server Name: Your SQL Server Instance Name
    Database name: Your Sage Accpac ERP Database Name, mine is “SAMINC”.
    Database Description: The same as the Database Name above.
    User Name: Your SQL Server User ID, mine is “crmuser”.  However, you may be using “sa” or what ever account that has been set-up by IT to allow SageCRM to access the SQL Server.  (i.e. What was the SQL account you used when SageCRM was installed?)
    Database Password:  The above user’s Password. 

    Step 2:

    Now we need to connect to the table in the Database we just connected to.  In order to do this navigate to “Administration | Advanced Customization | Tables and Databases” and select the “New Table Connection” button.  Now you need to make your values match my values below:

    Table Name: ARCUS
    Table Caption: ARCUS
    Database: SAMINC (This is the database you created in Step 1 above.)

    After saving the above information you should see the Database Connection and the Table connection listed.

    Step 3:

    Now we need to place some code on the Company Entry Screen.  So navigate to Administration | Customization | Company | Screens Tab and select the customize button next to the “Company Entry Screen”. 

    At this point select the “Company: Company Name” field from the list within the “Desktop HTML Screen Contents” on the left. 

    Copy the code in the table below and paste it into the “CreateScript”

    After pasting the below code in the above CreateScript area click on the Update button. 

    Finally, click on the Save button.

    Beginning of Code

    var recAccpacCust = eWare.FindRecord("ARCUS", "IDCUST='" +

    Values('comp_idcust') + "'");
    var bOnHold = recAccpacCust('SWHOLD');
    if (bOnHold == 1)
    {
    Valid = false;
    ErrorStr ='Customer is on hold';
    }

    End of Code

    Now you should be able to set a Sage Accpac ERP Customer on HOLD.  And if that Customer has been synced over to SageCRM as a Company then when you navigate to that company you should see the red alert that the “Customer is on hold”.

    Special Thanks to Lisa and Stuart for the code that got me started.

    21st Century Systems Corporation
    Lisa Baker, CRM Sales & Services Manager
    C. Stuart McKelvie, MBA, CA, CMC, President

     

    with no comments
  • SageCRM Tips & Tricks - Building Labels with a Mail Merge Template Process for SageCRM

    Hi Everyone,

    Here are the step by step instructions I built to answer a partner and prospect question about being able to build labels within SageCRM.  The attached zip file contains a PDF file with instructions on how to build your own labels, and then two Word Documents that relate to the steps in the PDF file.  The Step 16 file is just before you update all the labels.  The Step 17 file is the final file after updating all the labels.  See the instructions for Step 16 and Step 17 in the PDF file.  Click here to download the zip file.

    The following is a re-cap of the attached PDF file. The images have been removed to simplify the posting.  For full details and related images at most steps see the attached zip file.

    Assumptions:

    SageCRM version: 6.1 (any edition 100 or 200)
    Office version: 2003

    Overview:

    There are three parts to this process:

    • Part 1: Gather Fields needed for Labels
    • Part 2: Build Word Label Template
    • Part 3: Process for using Label Template with SageCRM.com and SageCRM

    Part 1: Gather Fields needed for Labels:

    There are a series of steps that must be completed to build a template that can be used to print labels with data from SageCRM or SageCRM.com. 

    Here is an over of the steps required to build a document template to be used with SageCRM/SageCRM.com.

    1. Decide what template is required.  (I will be using an example of Avery labels 5160.)

    2. Decide which data will be used from within SageCRM. (I will use Person.)
    (i.e. our list will be based on which entity from within SageCRM.)

    a. Company?
    b. Person?
    c. Lead?

    3. Based on step 2, create a list you have two types to choose from, a target list or a dynamic group.  Marketing | Target Lists | New or Find | <entity> | New search button top right corner. (I will use a Dynamic Search.)

    Now we need to Create the template. 

    a. Find and Select an existing Person Record.
    b. Right click on New Select Document.

    4. Select Create New Local Template
     
    5. and then click on continue

    6. Now you will need to go to word, it should be opened automatically by CRM if it is not already open.  You will then see in word the following messages.  Click Yes.

    7. Click Yes.

    8. After the previous steps you should now see Microsoft Word open. 

    At this point we are done with Part 1.  What did the previous 9 steps achieve?  This populates the file that SageCRM uses to perform the mail merge. 

    So what is the name of the file that is populated?  The file is named eWareMerge0.dat.  It is
    populated via the ActiveX control that is installed as part of the document drop feature of SageCRM.

    Where is this file located?  C:\Documents and Settings\[current_logged_on_user]\Local Settings\Temp. 

    If you do not see the Local Settings folder then you have to turn on the ability to see hidden files and folders.  To do this open the program Windows Explorer, then select Tools | Folder Options.

    Now that you have Folder Options open, select the View tab.  And select the radio button option of “Show hidden files and folders”, as shown below.  Click Apply and OK.  Now you should be able to see the Local Settings folder described above.

    Part 2: Build Word Label Template:

    At this point we need to build the Label Template that you want to mail merge with SageCRM.

    1. Here is where you should be, within the document that was just created.

    2. From within the newly created document select Tools | Letters and Mailings | Mail Merge…

    Once the Mail Merge Wizard appears on the right select
    the “Previous: Starting document” hyperlink (Step 3 of 6). And Select Previous: Select document type (Step 2o f 6).

    3. Within the Mail Merge Wizard select the Labels option and click Next to continue,
    as shown below.
     
    4. Now select “Use the current document” and then click on “Next: Select recipients”, as shown below.

    5. Now select label options and choose the label template you will be using.

    6. Once you have selected your label type (I selected 5160) click on OK. 

    7. Then a warning will appear, click OK, see print screen below for warning message.

    Now the Mail Merge document should contain a table with rows and columns.

    8. Now click on Next: Select recipients.

    9. If you started this from within SageCRM you should see an option to use an existing list, and the mail merge file that SageCRM creates locally should already be referenced, you can skip to step next step.  If not then proceed to the optional steps listed within this one step.

    a. Optional Steps:

    i. Now click on the browse option.

    ii. Now browse to the actual location of where the eWareMerge0.dat file, C:\Documents and Settings\[current_logged_on_user]\Local Settings\Temp.  See Part 1 step 9 above for direction on how to access the hidden folder of “Local Settings”.

    iii. Once the folder is select you must choose to view “All Files (*.*)”, if you do not do this you will not see our file.

    iv. Now Select the file and click on Open.

    v. Click OK, as shown below.

    vi. Now we are DONE WITH THE additional OPTIONAL STEPS.

    10. If you skipped the optional steps above, you need to do this step, now select the option “Edit recipient list…”.  If you had to follow the optional steps above then skip this one step and proceed on.

    You will then see the Mail Merge Recipients screen, click OK.

    Now the Word document should look something like this (with “«Next Record»”) in each label.

    11. Now click on Next: Arrange your labels.

    At this point you should be on Step 4 of 6 in the wizard.  Within the Arrange your labels
    area select the More items… option.

    12. Now the mail merge fields available from the SageCRM file should be shown, see print screen.

    13. Now select all the fields you would like on your label.  You will need to manually add in the spaces between each field.  I typically select all my fields and then clean up the spacing and formatting afterwards.  This is what my first cell looks like before formatting.  This is what my first cell looks like after formatting (I set my values to Font Type = Arial, Size = 8):

    14. At this point I now need to apply my changes to ALL the Cells within the Avery Label 5160 template.  To do this click on the Mail Merge Wizard option of Update all Labels.  Important NOTE:  Notice how each cell after the first top left one has the <<Next Record>> option.  This is what makes the labels merge all the different values from the eWareMerge0.dat file into the individual cells.

    Please note the print screen below when the fields are highlighted they have grey boxes around them.  That means the fields are still recognized as merge field values.  In comparison if you look at the next print screen you see red lines under the merge fields, that means the fields were converted to text and are NO LONGER merge field values.  START OVER AND DO IT AGAIN.  YOU did something wrong.

    15. At this point save the Labels to a folder on your machine.  Take note of this location because you will need to point to this file from within SageCRM to use it.  (You can upload this file as a template, this is explained within SageCRM Mail Merge Chapter of the System Administrator Guide.pdf.

    16. PREVIEWING THE LABELS OPTIONAL. 

    Now click on Next: Preview your labels.

    17. Preview of my Label:

    18. Now click on Next: Complete the mail merge.

    19. Now on the last step click on Edit Individual Labels…

    Now you should have a new document with the Merge Label.  Close this out and save it if you want it.

    20. Now CLOSE save that file to your desktop.  And close out Word.

    Part 3: Process for using Label Template with SageCRM.com and SageCRM

    1. Based on step 3, select the list you created.  You have two types to choose from, a target list or a dynamic group.  Marketing | Target Lists | New or Find | <entity> | New search button top right corner (see print screen below). (I will use a Dynamic Search, Texas Territory) Then click on the find button.  The results should then appear at the bottom of the screen.

    2. Now click on the New Document to the right of the Search Results.

    3. Now select the option to mail merge with a local template.  And then click on continue.

    Select your document; I have selected mine in the print screen below. Then click on Open.

    4. And then the system generates a mail merge document with all the labels from the group selected.

    Now go back to SageCRM and complete the communication for the entire group.  Note:  The communication will take the template not the finished mail merge labels.  So, when you save the communication the Mail Merge results will remain open so you can do with them as you need, i.e. print, save, etc…

    5. Done.

     

    with no comments
  • VODs that are available for SageCRM and Sage Accpac ERP

    If you need help when customers and prospects when they ask for basic information about SageCRM and Sage Accpac ERP EES.  You should go out and take a look at the fantasic VODs that are available at www.sagewebcast.com.  To see the Sage Accpac ERP and SageCRM VOD's list of VODs select from the "Select a Sage Product" drop down and then click the go button.  To see just SageCRM VODs select SageCRM from the drop down list and then click the go button.  Don't miss out on some great information for partners, consultants, customers and prospects.  There are several VODs that address lots of different areas of Accpac ERP and SageCRM. 

    with no comments
  • Notes vs Communications for Customers Migrating to SageCRM

     Feedback from Community:

    During a recent Accpac Live Conference in Vancouver, BC we received a lot of feedback around the concept of SageCRM communications vs. SageCRM notes.  The main point that was being discussed was the fact that the users that were migrating to SageCRM found the way their old system use to use Notes is completely different than the way SageCRM's Notes work.  New users of SageCRM are use to using the Notes section of their old Contact Management system as the place to record their interactions with their Customers.  When the user goes to SageCRM Notes they see very limited functionality.

    Thank you for this information Travis.

    Suggestions for addressing this SageCRM User concern:

    The re-occurring theme of Notes vs. Communications within SageCRM is generally only an issue for users coming from ACT! by Sage or similar Contact Management solutions.  The point is valid but the point should be addressed by the implementers early in the implementation of SageCRM.  Or even better during the sales process it self.  I recommend that when the customer is coming from a Contact Management solution that the SageCRM Notes Tabs be removed from SageCRM.  Within SageCRM notes are defined simply as “FACTS” about a company, person or lead. 

    An example of a Note or fact: 

    The owner of Smith Unlimited Bill Smith likes chocolate cake on his birthday.  


    An example of a SageCRM communication is:

    Phone Out:  Spoke to Bill today and asked if he liked the Chocolate Cake that we sent to him for his birthday. 

    See the difference? 

    If you want to get technical the notes ARE NOT indexed, are NOT searchable, are NOT findable are NOT filterable, and generally are NOT reportable.  They are simple facts about Notes relating to a company, lead or person.  Within SageCRM Communications are the way to record interactions between a SageCRM User and the world for a Lead, Company, Person, Opportunity, Case, Solution and Marketing.  Communications ARE indexed, ARE searchable, ARE findable, are filterable, are generally reportable.  If a migration of data is performed make sure and use a migration tool that moves the NOTES from ACT! by Sage into Communications for SageCRM.  Inaport by Inaplex and David Evens offers this as part of their migration tool.  On the other hand the last time I checked Greytrix migrates ACT! by Sage notes to SageCRM Notes.  Please note this fact about Greytrix was true a couple years ago they may have addressed it by now but I do not know for sure.

    Again to recap this one point should be easily handled by an implementer by removing the Notes tab from the Company, Person, and Lead entities.  Then teach the new SageCRM users to use communications.  If it get’s really bad and the SageCRM users still don’t get it!  Use SageCRM Translations and rename Communications to Notes.

    On a final note when a presentation is ever done for an existing ACT! by Sage or similar Contact Management solutions user a few extra minutes should be taken to make sure and cover this point of difference. 

    I hope this is helpful.

    with no comments
  • How to WRITE a SageCRM SQL View for NON-PROGRAMMERS

     Overview:

    The purpose of this article is to provide instruction on how to write a SQL View for use within SageCRM.  This is designed for SageCRM Certified Consultants who are not programmers or are just learning how to write SQL Statements for SageCRM and do not know Transact SQL.  A PDF has been created to proivde the following details for off line use and for viewing the individual images referenced below.  To download the PDF click here.

    Assumptions:

    During this discussion I will be presenting how to use SQL 2005 Express Management Studio to write a SQL Statement and then apply that code within SageCRM.  Even though I am using SQL 2005 Express for this example, the logic and process would be similar on SQL 2000 or full SQL 2005.

    Perquisites:

    SQL 2005 Express must be installed before SageCRM is installed.  OR SQL 2005 Express will be installed if SQL is not found during the installation routine.

    HOWEVER, to complete this exercise an additional tool must be downloaded from Microsoft’s website.  Navigate to www.microsoft.com .  Then search for “SQL 2005 Express Management Studio”.  Once found download and install this free utility.  We will be using this tool for this whitepaper.

    Instructions:

    First start Microsoft SQL Server Management Studio Express.  Login as SA or use Windows Authentication.  For this exercise either way is fine.  In the print screen below I have clicked on the connect button.

    On the right half of the screen Navigate to <SQL_Server_Instance>| Databases | <CRM_Database> | Views, in the print screen below it can be seen that I have navigated to TTAYLORVM2 | Databases | CMR61FULL | Views.

    Once the SageCRM Views are visible, right click in a blank area on the right hand side of the screen and select New View.

    Now let us start writing a view by selecting two tables, Company and Person, as shown in the
    following print screen.  Note that the Company and Person tables have already been added.  By simply scrolling through the list of tables and clicking on the Add button. 

    Once both tables are added click close on the Add Table screen. 

    Note in the following print screen (under Scenario 1) that I checked on both tables the option of
    *(All Columns).  This allows for the results to show ALL the columns within both tables.  If only certain fields are needed, then those fields should be selected.  This area brings up several areas that from a non-programmer perspective can be a little much to grasp and are out of the scope of what this document was attended for.  From the perspective of the beginning coder, start writing views by using the
    option of *(All Columns). 

    Now let’s join the two tables together.  We first have to decide what data we want.  The following scenarios discuss examples of how to present this data.

    Scenario 1:

    What if I want all companies and their respective Primary Contact, which two fields should we link together? 

    In the print screen above I joined from Comp_PrimaryPersonID to Pers_PersonID.  This brought back all companies and their Primary contact. 
     
    Scenario 2:

    What if the customer wants all company records even when the company does not have a primary contact associated to it? 

    In the previous scenario, I joined the two fields together in a way that I only get a Company when it has a primary contact defined.  If I change the relationship to a LEFT OUTER JOIN, I get all company records even if there is not a related person, as shown in the following print screen.

    I achieve these results by simply right clicking over the join between the two tables and selecting the option “Select All Rows from Company”, as shown in the following print screen.  Once the join is changed, click the explanation point button to refresh the results shown at the bottom of the following print screen.

    Notice that the total record count at the bottom of the above print screen has jumped from the previous total of 1302 to 1319. 

     
    The first row of the record set in the print screen above shows in the second column (field name Comp_PrimaryPersonID) has a NULL value within it.

    Scenario 3:

    What about if I want ALL contacts at each company, what two fields would we link together?

    I first remove the existing join between Comp_PrimaryPersonID and Pers_PersonID.  Now we will add a join between Comp_CompanyId and Pers_CompanyId.  I also went ahead and did a LEFT OUTER JOIN by right clicking over the join between the two tables and selecting the option “Select All Rows from Company”. 

    The above results show where Every Company exists with OR without a Person Record Associated to it.

    Scenario 4:

    Taking the previous scenario one step further what if the customer wants ALL companies with or without a person record AND all persons even if they are not associated to a company?

    In order to achieve this scenario we will need to right clicking over the join between the two tables and selecting the option “Select All Rows from Person”, as shown in the above print screen.

    The above results show where Every Company exist with OR without a Person Record Associated to it and where Every Person Exist regardless of Company relationship.  So as a result I get ALL companies and ALL people regardless of a relationship existing or not existing.  In the above print screen we can see that our total count increased by one from 1926 to 1927.  Which translates to ONE person inside of SageCRM is NOT linked to a Company.  Or in SageCRM terminology we have ONE person that is an Individual.

    Summary Scenarios:

    So based on the results from the above scenarios, it is important to understand that a simple join completely alters the result set shown for a simple view.

    Applying view created to SageCRM:

    Now that we have a working view lets now add that view into SageCRM.  Lets use the view created from Scenario 3 above.  In order to do this we have to reset the joins to match the values for Scenario 3.  In the following print screen I have done so.  At this point we also need to select the code that was actually created by the selections made.

    Now that the code is actually selected, right click and select copy as shown in the following print screen.

    Once the code is stored in the computers memory we need to launch and login to SageCRM as a user with Administrator rights.

    Within SageCRM navigate to Administration | Customization | <Entity to store view under> |
    Views Tab and select the New View button.  In our example our view is pulling all companies and all their related persons so we will navigate to Administration | Customization | Company | Views Tab.

    To read up on all the definitions for the fields shown on the above screen see chapter titled “Views” within the  “System Administrator Guide.pdf”.  This chapter goes into great detail of what all of these fields are used for.
    For our purposes we are going to keep this very basic.  The following values need to be typed in before pasting the code into the View Script area.

    • View Name: vCompanyAllPersons
    • Description: This view shows all Company records with related Person records.
    • Reports View: Check this box
    • Groups View: Check this box (Optional NOT required)
    • Translation: Company Records and All Persons

    At this point the screen should match the following print screen.

    On the previous print screen you will see a high lighted area of the View Script, at this point in the
    process please remove the highlighted area ONLY, as shown in the following print screen.

    Now in the print screen above you can see that the two lines of script have been removed.  At this point paste the SQL Code that we copied from SQL Server Management Studio into the View Script area below the “AS” line shown in the previous print screen.  See the following print screen for the final results.

    At this point click on save and the view should then be available within the list of views for the Company Entity, as shown in the following print screen.

    Where can this view be used.  Remember we checked the Reports View check box earlier before
    saving the view. 

    Navigate to Main Menu | Reports | General Report Category. 

    Once there click the New Report button. 

    At this point the following screen with the newly created View as an option within the “Source View” selection list of values.

    Why does the above print screen show in the selection list “Company Records and All Persons” instead of the views actual name “vCompanyAllPersons”? 

    The value shown in the selection list is the Translation we provided when we were creating the view.

    Once the view is selected now all the available fields specified earlier are available for creating a report.  See the following print screen.

    To learn more about writing reports see the Report Writing chapter within the
    “System Administrator Guide.pdf”.

    Summary:

    The purpose of this whitepaper is to provide instruction on how to write a SQL View for use within SageCRM.  This is designed for SageCRM Certified Consultants who are not programmers or are just learning how to write SQL Statements for SageCRM and do not know Transact SQL.

    with 2 comment(s)
More Posts Next page »