Login / Register  search  syndication  about

          Jason Buss' Blog

Jason Buss on SalesLogix development & customization, SQL, and more.

Displaying external data in SageCRM 200

In this post, I will outline a relatively simply process for displaying data in SageCRM from an external data source.   To keep this simple, we're not going to worry about any sort of security, and we'll save more advanced formatting for another post.

First of all, we need to create a reference to an external data source.  For this example, I'm using the Standard "Northwind" database installed with SQL.  To create a connection to an external datasource, click the "Administration" button, then "Advanced Customization" and finally "Tables and Databases".

Once in this area, click the "New Database Connection" button which will presented a Database Details view where you enter your connection information


I've entered the following info:

  • Database Driver:  There are a number of options here, but since I'm connecting to a SQL database, I selected Microsoft SQLServer
  • Server Name:  The name of the SQL server containing the external database.
  • Database Name:  The name of the External Database.
  • Database Description
  • Username/Password:  For the sake of this example, I'm simply using the standard sa username/password for this server.

Click "Save" after entering this information.  The Northwind database will now display in the list of available Databases, however we still need to include a table.  For this example, I'm going to use the "Orders" table in Northwind, but we need to perform a step on the database before we can display information from this table on the Team CRM tab.

To display on the Team CRM tab, the table needs to contain a field named chan_channelid.  This field is used by the Team CRM tab to determine if the current user has access to see this data.  For this example, we want all users to see this, so we want this field to contain a value of 5.  We could just add a new column and insert that value across the entire table, however to avoid modifying the original source data, I'm going to create a view instead.

To create the view, I use the following syntax in SQL Query anaylizer:

create view vOrders as select 5 as chan_channelid, * from orders

I now have a view named "vOrders" which I can use in SageCRM

Back under Administration|Advanced Customizations|Tables and Databases, click the  "New Table Connection" button to bring up the table details view:

On this view, we need to fill in the following information:

  • Table Name:  This is the name of the view we created earlier
  • Table Caption:  This is the caption by which the table (view) will be identified in SageCRM (Note: This value cannot contain spaces)
  • Database:  This pull-down should contain the Northwind database that we created the connection to earlier.
  • ID Field Name:  For this example, we're going to leave this value blank for now.

Click "Save" on this view.  The data from the Northwind Orders table will now be available in SageCRM


Now that we've created connections to the Northwind table and Orders database (via a view), we need to create a list view containing Orders data.  Go to Administration|Customizations.  You should find the entry for "NorthwindOrders" listed in the Secondary entities pull-down.  Once that is selected, click the "Lists" tab, and click the "New" Button:


For the New List Definition, we simply need to enter:

  • Name:  This is the descriptive name of the List view
  • Table/View:  the name of our view (vOrders)

Click Save, and then click the "Customize" button next to the newly created view to define the columns to display:


On this view, add whichever fields you want to display and click "Save" to finish configuring the new list view.


Now that we have our list view created, the only step remaining is to add the list view to the Team CRM area.  To add a new tab to Team CRM, go to Administration|Advanced Customization and click on "System Menus".  The tabs under Team CRM are defined in the "Channel" tab group, so click "Customize" next to that entry:


Here, we need to set three values:

  • Caption:  This will display on the tab under Team CRM
  • Action: Run Block
  • Block Name:  This is the Caption we defined for the list view.


Click Save, and we are done!


If we wanted to do additional formatting, say on the date fields or column captions, I would make those sort of changes in the view we created.  Otherwise, this is a pretty simply method to display external data within SageCRM.  In future postings on SageCRM, I'll try to get into adding data related to existing entites and possibly dig more into user security topics.


I hope you find this helpful, or at least interesting.  Thanks for reading!  Smile

What's This?
Bookmark and Share

About Jason Buss

   Jason is a senior application developer with Customer FX.

Related Content
   Using Global Script functions in InforCRM
I recently had a project that required me to be able to check if a user belonged to a particular team.
Posted on May 05, 2015 by Jason Buss to Jason Buss' Blog
   Better Code Organization for Infor CRM (Saleslogix) Mobile Customizations
When customizing the Infor CRM (Saleslogix) Mobile client, it's common for the ApplicationModule to g
Posted on May 05, 2015 by Ryan Farley to Ryan Farley's Blog
   Index of Posts for Starting with Infor CRM (Saleslogix) Development
The blogs on customerfx.com go all the way back to 2003. There's years and years worth of information
Posted on Dec 30, 2014 by Ryan Farley to Ryan Farley's Blog
   Telnet Sample to Send an SMTP email
I am always forgetting how to test sending an email through an SMTP gateway using the Telnet client. 
Posted on Dec 12, 2014 by Kris Halsrud to Kris Halsrud's Blog
   Problem setting SLXTextbox enabled property / Setting unexposed base control properties for a custom control
I recently had an issue with a Quickform where I was attempting to set the Enabled property of a TextBox
Posted on Dec 05, 2014 by Jason Buss to Jason Buss' Blog


Twitted by 4SalesLogix said:

Pingback from  Twitted by 4SalesLogix

August 19, 2009 6:27 AM

Danny said:

Is it possible to create an entry screen using this method to edit/add/delete data in an external database?

February 13, 2010 12:56 PM

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register