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

  • Jeff..

    THANK YOU! THANK YOU!! THANK YOU!!!

    Nesting the quotes did the trick!!! I am definitely doing a happy dance!!! :-)

    I feel pretty good about writing SQL in the Management Studio but I am still learning how to tie records together in Sage CRM with Javascript. :-)

    I still have more that I want to do with this screen ... but getting the location data on the screen from an external entity when my "JOIN" was not the record id was a hurdle for me ... but I learned a lot getting there! Thank you!

    Next step...see if I can add the Edit Page. Fingers crossed! :-)

    Again your assistance is always greatly appreciated!!! :-)

  • Jeff...

    I made some progress!! :-) I found my error (but now I have a different error..dang!)

    I had this in my asp page...

    var AddressRecord = CRM.FindRecord('address', 'addr_addressid=' + Values('Key58'));

    I changed it to this...

    var AddressID = Request.QueryString("Key58");

    var AddressRecord = CRM.FindRecord("address", "addr_addressid="+AddressID);

    I was trying to use Values() in an ASP page and as you explained in DP training today...you cannot do that!!!

    So my new error is an SQL error....I think I have some syntax wrong as this is the SQL error that is being returned...

    select count(*) as fcount from LOCATIONSITE WHERE alias=NOBLEPLACE

    Invalid column name: 'NOBLEPLACE'

    This is the code I have that is returning the SQL error....

    var myBlock = CRM.GetBlock(LocContactsList);

    var AddressID = Request.QueryString("Key58");

    var AddressRecord = CRM.FindRecord("address", "addr_addressid="+AddressID);

    var LocationAlias = AddressRecord.addr_localias;

    var Arg = "alias="+LocationAlias;

    CRM.AddContent(myBlock.Execute(Arg));

    Response.Write(CRM.GetPage());

    It is after midnight where I am located and my brain does not want to think anymore...so I am going to look at this with fresh eyes in the morning.

    If you see this post and can spot my error....I will not be disappointed if you tell me!!! :-)

    Thank you for your assistance!!! I greatly appreciate it!!!!!

  • Michelle

    Changing the error message is a definite sign that progress is being made. :-)

    I think 'alias' is a reserved word in SQL (msdn.microsoft.com/.../ms189822.aspx).

    Having said that....

    Since NOBLEPLACE is a string the code needs to take that into account try

    var Arg = "alias='"+LocationAlias+"'";

    I've nested some quotes as we need to make NOBLEPLACE read 'NOBLEPLACE'

  • Thanks Jeff!

    I will review the examples you have here and compare it to what I am doing. As of right now, I am getting the following error returned..

    There has been an error

    Error Name: TypeError

    Error Number: -2146823281

    Error Number: 5007

    Error Description: Object expected

    Hopefully I can see what I have done! :-)

  • 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;

Generic Content
Generic Content

*Community Hub is the new name for Sage City