Sage CRM has the ability to link to external databases. Once an external database table is linked to Sage CRM the External Access settings of the linked table can be changed to allow both SData and Web Services access.

I have discussed in previous articles how easy it is to provide SData access.

• An External Table can be exposed through Sage CRM's ReadOnly SData provider
• Making an External Table Available as an SData Gadget in the Interactive Dashboard

But if you want to use Ajax calls within the main Sage CRM interface then you will need to use the SOAP Web Services rather than the SData requests. This is because the SOAP web services share the same session mechanism as the main interface whereas the SData provider uses a different security mechanism.

The general principle of how the SOAP web services can be used to an AJAX request is discussed in the article "Grabbing Serverside Information using a SOAP Webservice call (AJAX)".

I want to consider the Supplier table from the Northwind database that was used as the example in the articles listed at the top of this document.

But there are some important things to note.

• Sage CRM web service methods like Query(), QueryEntity() and QueryRecord() can only reference 'tables' that exist in the Sage CRM database.
• All 'table' columns must use the a table prefix like 'proj_' or 'supp_'.
• The 'table' must include the xxxx_deleted column.

In my research I have assumed that any connection to an external table via the Sage CRM web services should be for ReadOnly purposes. If data manipulation is needed then the external application own web services should be used, as these would take the correct business logic into account.

So how can we make an external table like Suppliers appear to be available through the Web Services?

The following steps need to be taken.

1) Build a View in Sage CRM that uses fully qualified syntax to identify the external database table.

This technique can be used for databases that are either within the same server or are on linked server. Please see the article "Connecting to a Sage MAS ERP 90 Database as a Linked Server within MS SQL Server 2008" for a more complete discussion.

The idea of using views that link to external tables through the use of the fully qualified four-part identifier syntax would allow reports and groups to be built include that data. See the article "Building Cross Database Views for Reporting" for more details.

I called the view "vNorthwindSuppliers" and created in the context of "System" as it is unrelated to any of the existing tables.

The SQL for the view is

CREATE VIEW vNorthwindSuppliers
AS 
SELECT     SupplierID AS supp_supplierid, CompanyName AS supp_companyname, ContactName AS supp_contactname, ContactTitle AS supp_contacttitle, 
                      Address AS supp_address, City AS supp_city, Region AS supp_region, PostalCode AS supp_postalcode, Country AS supp_country, 
                      Phone AS supp_phone, Fax AS supp_fax, HomePage AS supp_homepage, NULL AS supp_deleted
FROM         Northwind.dbo.Suppliers

Note

The view has added the supp_deleted column as null and also alias the columns so that they have a proper prefix "supp_".

This is necessary because the Web Services API strips the first element of the names to the underscore.

Please see the article "The importance of column prefixes in tables exposed via the Web Services API" for more details of this issue.

2. A connection then needs to be made to this view as though it is a table. This can be done in

Administration -> Advanced Customization -> Tables and Databases

I provided my system with the following details

• Table Name: vNorthwindSuppliers
• Table Caption: vNorthwindSuppliers
• Database: --unassigned--
• ID Field Name: supp_supplierid

Note: The database is unassigned.

3. Once the record has been saved, it can be edited to provide the details of the column prefix and to expose the 'Table' to web services.

4. Last of all to make sure that the columns for my table are correctly defined I have edited each to confirm the entrytype and the translation to be used for this new 'Table'. This was done under

Administration -> Customization -> vNorthwindSuppliers

You can then reference the external 'Table' in webservice code like this C# example below:

private void buttonQueryRecord_Click(object sender, EventArgs e)
{
    queryrecordresult myQueryRecordResult = CRMbinding.queryrecord("supp_supplierid, supp_companyname", "supp_city='London'", "vnorthwindsuppliers", "");
    MessageBox.Show(myQueryRecordResult.more.ToString());
 
    crmrecord[] myRecordList = myQueryRecordResult.records;
    MessageBox.Show(myRecordList.Length.ToString());
    listBoxCompanyNames.Items.Clear();
 
    for (int intCount = 0; intCount ">

2282659330260supp_supplierid, supp_companynamesupp_city='London'vnorthwindsuppliers

And the returned XML would look like

vnorthwindsupplierssupplierid1integercompanynameExotic Liquidsstringfalse