fiogf49gjkf0d
This article discusses how to add a custom column to the SalesLogix Manage Products datagrid. This out of the box area is difficult to customize. Following the steps outlined in this article will help you make an easy task of customizing this area.
My fun adventure started when I attempted to add a custom column to the datagrid on the Manage Products form:
I added the column (from a custom 1:1 table associated to the PRODUCT table) in the Architect tool by simply opening the SQL property of the grdProducts datagrid and dragging the column into the layout:
I then had to modify the VBScript code behind the form - specifically the "BuildSQLQuery" subroutine - to include the new column in the select statement (because the SQL property of the grdProducts datagrid is dynamically set at run time by this routine):
The original code:
strSQL = "Select p1.PRODUCTID, p1.NAME, p1.DESCRIPTION, p1.ACTUALID, p1.FAMILY, " _
& "p1.STATUS, p2.PROGRAM, p2.PRICE, p1.MODIFYDATE, p1.MODIFYUSER, p1.FIXEDCOST " _
& "From PRODUCT p1 " _
& "Left Join PRODUCTPROGRAM p2 " _
& "On (p1.PRODUCTID = p2.PRODUCTID And p2.DEFAULTPROGRAM = 'T') "
My first attempt at altered code (my alterations in red):
strSQL = "Select p1.PRODUCTID, p1.NAME, p1.DESCRIPTION, p1.ACTUALID, p1.FAMILY, " _
& "p1.STATUS, p2.PROGRAM, p2.PRICE, p1.MODIFYDATE, p1.MODIFYUSER, p1.FIXEDCOST ", _
& "p3.moredata " _
& "From PRODUCT p1 " _
& "Left Join PRODUCTPROGRAM p2 " _
& "On (p1.PRODUCTID = p2.PRODUCTID And p2.DEFAULTPROGRAM = 'T') "
& "Left Join PRODUCT2 on p1.PRODUCTID = p2.PRODUCTID "
The result of this was that the new column displayed in the grdProducts datagrid when the Manage Product form opened but there was no data in it. No error messages, no "failed to parse SQL" or other warnings - just no data in the new column.
I then used various tools including SQL Profiler and the SLX Profiler to capture the SQL being executed by the client, and ran that SQL in the "Execute SQL" utility in the Administrator. The SQL executed properly and the data for the custom column was in fact included.
What I eventually discovered was that it was not a SQL issue at all but rather the datagrid control was not displaying the data returned by the SQL query. When the custom column was added to the grdProducts datagrid in the layout, the column’s name in the datagrid control itself was prefixed with "A2_" before the actual column name. This is only shown in the Columns property of the datagrid:
This prefix does not exist with the other "factory" columns, only with custom columns that are added from other tables. Apparently the column name in the Field property of the column must exactly match the column name of the returned SQL from the query or no data will be displayed by the control in that column. Since all of the original columns in the datagrid exactly match the actual column names in the PRODUCT table there is no issue, but the SalesLogix system automatically put the "A2_" prefix in front of my custom MOREDATA column (making it actually named "A2_MOREDATA") it didn’t know where to get the data from the SQL query because the column name didn’t match. Furthermore the Architect makes this Field property grayed out & uneditable, so there is nothing that can be changed there.
What I had to do to fix this is add an alias into the SQL statement in the BuildSQLQuery to make the custom column name in the results match the uneditable column name in the datagrid:
Code that finally worked (new change in blue):
strSQL = "Select p1.PRODUCTID, p1.NAME, p1.DESCRIPTION, p1.ACTUALID, p1.FAMILY, " _
& "p1.STATUS, p2.PROGRAM, p2.PRICE, p1.MODIFYDATE, p1.MODIFYUSER, p1.FIXEDCOST ", _
& "p3.moredata as A2_MOREDATA " _
& "From PRODUCT p1 " _
& "Left Join PRODUCTPROGRAM p2 " _
& "On (p1.PRODUCTID = p2.PRODUCTID And p2.DEFAULTPROGRAM = 'T') "
& "Left Join PRODUCT2 on p1.PRODUCTID = p2.PRODUCTID "
This simple change was all that was needed to get the column name from the SQL query results to match up with the new column in the datagrid and the data to actually display.
This is not documented anywhere in any Developer’s Reference or other publications, it is not taught in the SLXD Developer courses, and even the SDK techs at Sage support didn’t know how to deal with this issue. This will be an issue whenever a column is added to a datagrid that has its SQL property set programmatically - whether the grid is bound or not - if the column comes from any table other than the single one the grid is build on.
I hope this helps others and saves them the days of searching & frustration (even more so than the usual SalesLogix developer road blocks cause) in dealing with this issue of adding columns to a datagrid where the SQL property is set programmatically.