Access to external data

There are several ways to have ChannelCRM showing external data. Data Browsing (described here) is an extremely flexible method that will allow you to link any kind of data to companies within the CRM system.

Your user will see the data in additional tabs below the company window and the data itself will be displayed in tabular formats. The tables (no limits to the number) can be linked in master-detail fashion.

The source of the data can be any table/view/stored procedure in SQL Server.

To setup Data Browsing will require som technical expertise as the data is captures by use of stored procedures.

Conceptually Data Browsing goed like this:

1.	In the company view you add tabs with unique names exactly matching what you are to show.

2.	In SQL Server you create one or more stored procedures responsible for fetching and presenting the data.

3.	During user login the system will check if data browsing is defined and if so the names of the tabs are stored.

4.	When the user opens a company window and selects one of the new tabs, the data if fetched and the visual elements (grids and links) are built dynamically.

This method is flexible and scalable. But setup is somewhat cumbersome.

Stored Procedures

You have to create a stored procedure to fetch the required data and to describe the data. This description is partly to give meaningful names to the tables and to link tables (master-detail).

As an example we will show a procedure that will get information from the tables FinNavisionInvoice og FinNavisionInvoiceLine

CREATE PROCEDURE [ccrm].[spGetTest]

( @CustomerKey varchar(20) )

BEGIN

SET NOCOUNT ON;

SELECT * FROM ccrm.FinNavisionInvoice WHERE DebtorNumber = @CustomerKey;

SELECT FinNavisionInvoiceLine.* FROM ccrm.FinNavisionInvoiceLine JOIN ccrm.FinNavisionInvoice ON FinNavisionInvoice.Number = FinNavisionInvoiceLine. InvoiceNumber WHERE FinNavisionInvoice.DebtorNumber = @CustomerKey;

SELECT 'Invoice'as TableName union

SELECT 'InvoiceLine'as TableName

SELECT 'Invoice' as LinkMasterTable, 'Number' as LinkMasterField, 'InvoiceLine' as LinkDetailTable, 'InvoiceNumber' as LinkDetailField

END

Select one and two fetches data. Here we have used * (all fields). In actual use you will probably need to select a subset of fields and to provide aliases so as to present the data with meaningful names.

The third select is a union select providing names for the two tables. Again these names could be a more "nice" version of the actual table names.

Lastly you will se a select (could be union if more than two tables were involved) to provide links between the tables.In this example the link is specified as Invoice.Number = InvoiceLine.InvoiceNumber.

Setup Data Browsing in Global Settings

Within ChannelCRM open Administration | System setup | Global settings. In the grid you add a parameter CompanyCustomTab1 ( or 2 or 3) and the value is entered as TabName;ProcedureName. In our example the value will be "Test;ccrm.spGetTest". (Note that the name of the procedure is entered with schema.)

Add the tab to Company window

In the company window you right-click on an empty space and select Customize. When the resulting dialog is shown you right-click on an empty location just right to the last tab in Linked information and select Add tab.

You the rename this tab (in our example to "Test"). This name has to exactly match the name from previous section.

Close the Customize dialog.

Test

By now Data Browsing should be functional on your user account. To enable your colleges to see the data you either define the tabs for them (via the Customize dialog) or you (having sufficient rights) promote your layout to global status.

Page url: http://www.channelcrm.net/us/index.html?net_show_external_data.htm