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.
