Home | Forums | Contact | Search | Syndication  
 
 [login] [create account]   Tuesday, March 19, 2019 
 
How to Add a Custom Column to the Manage Products Grid  
Description:  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.

Category:  Architect How To Articles
Author:  Mark Mazzitello
Submitted:  4/26/2007
   
Stats: 
Article has been read 16231 times

Rating: - 5.0 out of 5 by 6 users
 

fiogf49gjkf0d
How to Add a Custom Column to the Manage Products Grid

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.


Jumping Right In

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.


Conclusion

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.

 

About the Author

Mark Mazzitello
Vital Images, Inc.

fiogf49gjkf0d


View online profile for Mark Mazzitello
 

[ back to top] [ send to a friend]  

Rate This Article you must log-in to rate articles. [login here] 
 
Please log in to rate article.
 

Comments & Discussion you must log-in to add comments. [login here] 
 
Author Article Comments and Discussion
Dale Richter
 

Re: How to Add a Custom Column to the Manage Products Grid
Posted: 4/27/2007 8:26:03 AM
fiogf49gjkf0d
Thanks for the great article. I love the examples. I think I could even do that myself, now. I am not a programmer and will never have to create this, but.... I will definetly use this this knowledge in my System Administrator's class.
 
Mike Spragg

slxdeveloper.com Forum Top 10 Poster!

Re: How to Add a Custom Column to the Manage Products Grid
Posted: 11/12/2007 7:48:18 AM
fiogf49gjkf0d
These are the steps I use when working in this area - they should still be current - but they document a few other areas that the above "touches" on too !

==========================================
"Simply" adding a new field to OppProduct:
==========================================

Form: Opportunity:Products
--------------------------
Sub PopulateDataGrid : modify to include col in sql, and the grdProducts
Sub CommitChanges : Modify to include col in sql, and the update rs

Form: System:Insert Opportunity
------------------------------
Sub SaveOpportunityProductInfo( )

Script: System:Insert Opportunity Common
----------------------------------------
Sub DefineProductsGrid(oppID) : modify to include new section detailing new column
Function CreateProductsRSStructure : modify to include new column
Function DoGridCalculations(Sender, FieldName, Value, OppID)
: Modify to include within loop/case the new field
If strCrntRecord = .Fields("KEYFIELDID").Value Then
.Fields("yourfieldhere").Value = Value
End If
Sub SaveOpportunityProductInfo( )
Sub RemoveProducts( )


Form: System:Add Opportunity Product
------------------------------------
Sub AddProductToGrid(objRS, intQty) : Modify to include any defaults
Sub UpdateCurrentProduct( )
Sub AddDeleteProductToDB( )
 
RJ Samp

slxdeveloper.com Forum Top 10 Poster!

Re: How to Add a Custom Column to the Manage Products Grid
Posted: 8/10/2010 8:29:18 AM
fiogf49gjkf0d
Has anyone tried adding a grid Column property column for the BASE table?

I'm getting TableDisplayName.FieldDisplayName as the uneditable column field name property....even though adding a field through the SQL gives me A1.FieldName as expected.

 
 

       Visit the slxdeveloper.com Community Forums!
Not finding the information you need here? Try the forums! Get help from others in the community, share your expertise, get what you need from the slxdeveloper.com community. Go to the forums...
 



 slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation.
 This site, and all contents herein, are Copyright © 2019 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.

code of conduct | Subscribe to the slxdeveloper.com Latest Article RSS feed
   
 
page cache (param): 3/19/2019 6:44:38 PM