x Want to participate? Join the group to interact
Sage CRM
Related
Recommended

Creating a simple ASP List Page that Displays Data from an external Database using an ODBC driver

2 minute read time.

This article is part of a series that discusses how you can link to an external database and list, search and view data from a table in that external system. I have used Sage ERP MAS 90 as my example database. The techniques discussed here can be used within implementations of the Extended Enterpise Suite (4.3 or 4.4).

Below is a simple list page that shows data that has been retrieved from the ap_vendor table from within Sage ERP MAS 90.

Before you can create the list page you must first create a connection to the external database.

You can read how to do that in the article "Connecting to an External ODBC database".

Once you have defined your connection to the database and the external table you can then give the fields enhanced definition in Meta Data. You can change the caption for the field and you can change the entry type. How changing the entry type will change the way data in the external table is displayed has been discussed in the article "Changing the EntryType for Fields in an External Table".

The List Page has been created using the COM API.

The list page has been called from the My CRM (User) tabgroup. You can edit this in the System Administration screens.

Changing Tabs and System Tabs is discussed in the documentation.

The Tab calls an ASP page.

The ASP page needs to call a List block defined in Meta Data.

Administration -> Customization -> ap_vendor

The List list has to reference the table name of the external table. I have called my new list "VendorList".

Once the new List is saved. The fields that should be displayed can then be included in to the List.

You can include any of the fields from the external table and change the properties as you need to. You can set fields to allow sorting and you can change alignment etc.

The Code for the Simple List page is shown below.


<%
var myBlock = CRM.GetBlock("VendorList");
CRM.AddContent(myBlock.Execute());
Response.Write(CRM.GetPage());
%>

The ASP page has been saved in to the custompage under the install

  • C:\Program Files\Sage Software\CRM\[installname]\WWWRoot\CustomPages\VendorList.asp

Note: This is a very simple example and there is no linking to existing context in Sage CRM. That is covered in other articles.

Note: This page assumes that the 'include' file eware.js is in the same folder. In your implementation you may have a differently named "include" file available to you called accpaccrm.js or sagecrm.js. Please see the article "DPP and Standard include files for ASP pages" for more details.

Note: In Sage ERP MAS 90 Extended Enterprise Suite, the software is installed under a folder in Program Files called "Sage Software". If you are working with other implementations of Sage CRM then the path will be:

  • C:\Program Files\Sage\CRM\[installname]\WWWRoot\CustomPages\VendorList.asp

Parents

  • Michelle

    Once you have the external database definition created in Sage CRM then you can reference that database table pretty much like a Sage CRM table.

    For example when using FindRecord then you can build the where clause that restricts the data returned.

    Consider

    var intRecordId = CRM.GetContextInfo("company","comp_northwindcode");

    var myRecord = CRM.FindRecord("MyExternalTable","northwindcode="+intRecordId);

    Or if you are using the queryobject or the SQL property of a list then you can build a statement with crosses both the CRM and the external database (but you should fully qualify the access to the external database table.

    select * from [ServerName].[database name].[user name].table name;

    e.g.

    select vusers.user_firstname,vusers.user_lastname, northwind..orders.* from vusers

    left join northwind.dbo.orders on user_userid = employeeid;

Comment

  • Michelle

    Once you have the external database definition created in Sage CRM then you can reference that database table pretty much like a Sage CRM table.

    For example when using FindRecord then you can build the where clause that restricts the data returned.

    Consider

    var intRecordId = CRM.GetContextInfo("company","comp_northwindcode");

    var myRecord = CRM.FindRecord("MyExternalTable","northwindcode="+intRecordId);

    Or if you are using the queryobject or the SQL property of a list then you can build a statement with crosses both the CRM and the external database (but you should fully qualify the access to the external database table.

    select * from [ServerName].[database name].[user name].table name;

    e.g.

    select vusers.user_firstname,vusers.user_lastname, northwind..orders.* from vusers

    left join northwind.dbo.orders on user_userid = employeeid;

Children
No Data
Generic Content
Generic Content

*Community Hub is the new name for Sage City