Programmatically building a datagrid in SalesLogix LAN

I recently had a project where I had to configure an account-level datagrid which displayed a list of related opportunities.  Aside from standard opportunity level fields, I also needed to include a simple count of products for each opportunity.

In order to include the product count, I used a relatively simple process to define and populate the datagrid programatically.  There are basically two steps:  First I used a recordset to define the columns that would be displayed in the datagrid.  Then, I wrote a SQL statement and set the datagrids recordset property to display the data returned from that SQL Statement.

  • Step 1: While you could technically skip this step, you need to configure the columns in the datagrid in order to apply specific formatting to various datatypes returned in the SQL Statement.

Dim objRS, i, col, strSql

First, I created a blank recordset containing the fields I needed in the datagrid

Set objRS = Application.CreateObject(“ADODB.Recordset”)
objRS.CursorLocation = adUseClient
objRS.CursorType = adOpenStatic
objRS.LockType = adLockOptimistic

Fields are added using the syntax: .Fields.Append {fieldname}, {datatype}, {fieldlength}.  For the text, numeric and currency field I used the standard datatypes.  For dates, it was easier to format as date only by configuring them as 8 character varchar fields.

With objRS

.Fields.Append “OPPORTUNITYID”, adVarChar, 12
.Fields.Append “DESCRIPTION”, adVarChar, 64
.Fields.Append “STATUS”, adVarChar, 64
.Fields.Append “SALESPOTENTIAL”, adCurrency, 12
.Fields.Append “DATEOPENED”, adVarChar, 8
.Fields.Append “ESTIMATEDCLOSE”, adVarChar, 8
.Fields.Append “NUMPRODUCTS”, adDouble, 4
End With

This code is required to strip out the columns for the grid prior to reading them.  The datagrid in this example is named “dgOpportunities”.

If (dgOpportunities.Columns.Count > 0) Then

 For i = 0 to dgOpportunities.Columns.Count – 1

End If

Now I go through and add each column to the columns collection of the datagrid.  This is where formatting is defined.  Each column is defined with an integer value which defines what type of data is displayed.  Any column where you want to use the FormatType and FormatString properties, you need to define the column as a Text column.  Without defining the width property of each column, each column’s length is determined by the length of the column returned in the SQL Statement.

The column for OpportunityID was needed to allow me to double click a row in the grid to move to the opportunity.  Columns would be visible by default, so for this column I set the Visible property to False.

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “OPPORTUNITIYID”
col.Visible = False

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DESCRIPTION”
col.Caption = “Name”
col.Width = 150

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “STATUS”
col.Caption = “Status”
col.width = 75

Currency fields can be set easily by defining the column’s datatype as currency (Integer value 13)

Set col = dgOpportunities.Columns.Add(13)
col.FieldName = “SALESPOTENTIAL”
col.Caption = “Sales Potential”
col.Width = 100

Since I wanted to display the date only, I returned the columns as text (0) and set the FormatType = 3 (DateTime) and the FormatString to “ddddd”.

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “DATEOPENED”
col.Caption = “Opened”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “ESTIMATEDCLOSE”
col.Caption = “Est. Close Date”
col.FormatType = 3
col.FormatString = “ddddd”
col.Width = 75

The # of Products was set as a text field as well, setting the format type = 2 (Integer)

Set col = dgOpportunities.Columns.Add(0)
col.FieldName = “NUMPRODUCTS”
col.Caption = “# Products”
col.FormatType = 2

Finally, I set the grids recordset property to the newly defined blank recordset.

dgOpportunities.Recordset = objRS

  • Step 2: In order to get the data into the grid, I simply build a SQL Statement to return the required data, and applied that to the Recordset property of the grid as well.


“O.ACCOUNTID = ‘” & Application.BasicFuntions.CurrentAccountID & “‘ ” & _

Set dgOpportunities.Recordset = Application.GetNewConnection.Execute(strSql)

That’s it!  You should now have a properly formatted, programmatically defined datagird.


Jason Buss

Jason is a senior application developer with Customer FX.

1 Comment

  1. I don’t think you will be able to set the Grid to Rowselect = True and have the Hyperlink work.  You’ll have to leave the Rowselect false, and set the Readonly property of each column that you add to False to prevent editing of individual cells.

    Add this line to each column as you define them…

     col.Readonly = “False”


Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!