11/21/2024 6:50:48 PM
slxdeveloper.com
Now Live!
|
|
| |
| Creating DataGrid Columns at Runtime |
|
| Description: |
This article focuses on how to change the SQL query a SalesLogix v6 DataGrid is bound to at runtime and programmatically create DataGrid Columns.
|
| Category: |
SalesLogix ActiveX Controls
|
| Author: |
Ryan Farley
|
| Submitted: |
11/7/2002
|
| |
|
| Stats: |
Article has been read 75000 times
|
Rating:
 - 5.0 out of 5 by 20 users |
|
|
|
fiogf49gjkf0d
This article focuses on how to change the SQL query a DataGrid is bound to at runtime and programmatically create DataGrid Columns.
The new SalesLogix 6.0 DataGrid control has many new robust features. It can do inline editing of data, drag & drop grouping, even connect to external data. Although these properties are easy enough to configure at design time, many of them can be a bit more difficult to configure at run time. For example, configuring the DataGrid columns is very easy at design time using the Columns editor (See the Columns property). However, there are many times that you will want to configure the columns at run time. The problem with doing this via VBScript is really just a matter of not knowing what exactly these extended properties in the Columns editor are called. Since VBScript does not use strong types (i.e. Everything is a Variant), the Columns collection is seen as a single Variant, not as a Collection of Column objects. Therefore, you’re not going to see the code completion drop-down when you type "DataGrid1.Columns." making it difficult to know what is available.
All of the new SalesLogix controls are nothing more than ActiveX controls found in SLXControls.ocx, located in the SalesLogix directory. This makes exploring the properties & methods in the DataGrid easy because we can use any object browser. For this example, we’ll use the object browser in Visual Basic.
Open Visual Basic and create a new project. When the project is open, add the ‘SalesLogix Controls’ component to the toolbox. You can do this by right clicking on the toolbox and select ‘Components’. Browse to find the library named ‘SalesLogix Controls’. You will now see all of the SalesLogix controls in the toolbox (a future article will cover actually using these controls from a VB application). Now, let’s take a look in the object browser. Go to ‘View’, Object Browser’ or just hit the shortcut key ‘F2’. When the Object Browser opens, change the Library drop-down (top left corner) to SLXControls. You will now see all of the SalesLogix controls listed in the Object Browser, including the DataGrid. Take a look around to explore the properties & methods available. With enough looking around we can see that the DataGrid has a Columns property, which is of type DataGridColumns. Further examination reveals that the DataGridColumns object is a collection of DataGridColumn objects. Select DataGridColumns in the left pane to see the properties for a DataGridColumn object.
So, what does all this tell us? We know that the DataGridColumns collection (i.e. the Columns property) is a collection of DataGridColumn objects. Knowing what properties are exposed by the DataGridColumn object gives us all the information we need to be able to add columns to a DataGrid at run time. Well, almost. If you look at the DataGridColumns collection’s Add method, you’ll see it takes a parameter of TxDataGridColumnType. This is an enumerated value. In VBScript, since there are no data types, we cannot use the actual enumerated labels. Instead we must use the numeric values. The values are easy enough to find out using the Object Browser, but here is a complete list:
| |
TxDataGridColumnType
|
| |
ctStandard = 0
ctMask = 1
ctButton = 2
ctDate = 3
ctCheck = 4
ctImage = 5
ctSpin = 6
ctCombo = 8
ctCalculator = 9
ctPictureBlob = 10
ctHyperlink = 11
ctTime = 12
crCurrency = 13
ctMemo = 14
|
The DataGridColumns.Add method will take a value from the list above and return to us a DataGridColumn object of that type. Ok, enough looking around; let’s give it a try.
Open Architect and create a new Account Form. Add a DataGrid and a Button. Don’t bother setting the SQL or Columns properties of the DataGrid, we’re going to do that at run time. Create a stub for the Button’s Click event.
In the Button1Click sub, add the following code:
Sub Button1Click(Sender)
Dim col
Dim i
With DataGrid1
.SQL.Text = "SELECT ACCOUNT, TYPE, MAINPHONE, ACCOUNTID FROM ACCOUNT"
With .Columns
'remove any existing columns
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
'add column for ACCOUNT field
Set col = .Add(0)
col.FieldName = "account"
col.Caption = "Account"
col.ReadOnly = True
'add column for TYPE field
Set col = .Add(0)
col.FieldName = "type"
col.Caption = "Type"
col.ReadOnly = True
'add column for MAINPHONE field
Set col = .Add(0)
col.FieldName = "mainphone"
col.Caption = "Phone"
col.ReadOnly = True
'add column for ACCOUNTID field
Set col = .Add(0)
col.FieldName = "accountid"
col.Caption = "accountid"
col.Visible = False
End With
'now refresh the grid to see the new columns
.Refresh
End With
End Sub
Let’s take a look at what exactly is going on. First, we give the DataGrid a SQL query to bind to. We do this by setting the SQL property’s Text value.
DataGrid1.SQL.Text = "SELECT ACCOUNT, TYPE, MAINPHONE, ACCOUNTID FROM ACCOUNT"
After doing this, the grid will actually be bound to the data, except you will not see anything because you have not defined any columns. This is why you need to set up the Columns collection - so it knows what to display. The first thing we’ll do is clear any columns that may be in the grid from the last query it was bound to. The only way I’ve found to cleat the Columns property is to loop through it’s child objects and call the Delete method of each of them.
If (.Count > 0) Then
For i = 0 To .Count - 1
.Item(0).Delete
Next
End If
Notice that we are always deleting the object at index 0. That is because if there are 5 columns in the Columns collection, and we delete the first one at index 0, then there are only 4 left (obviously). So, the object that was at index 1 is now at index 0. Each time you delete the first object the remaining objects indexes drop down by 1. If you delete the first one (index 0) each time then you don’t need to worry about decrementing the index value you are deleting.
Adding the Columns was easy. When we call the ‘Add’ method of the Columns property (i.e. the DataGridColumns collection), we pass it a value that indicates the type of column we are adding. In the example above we are only adding columns of type ctStandard (which has a value of 0). By calling ‘Add’, we are returned a reference to the newly added DataGridColumn object. We can use this reference to set any additional properties, such as the field it is bound to, the column’s caption, and so on.
Set col = .Add(0)
In the code above we have added a column of type ctStandard and were returned a reference to the column. We can set any additional properties for this column. Below is a complete list of properties & methods exposed by the DataGridColumn object.
| |
DataGridColumn Object
|
| |
Property Alignment As TxAlignment
Property Caption As String
Property ColIndex As Long
Property Color As OLE_COLOR
Property DisableEditor As Boolean
Property FieldName As String
Property Font As IFontDisp
Property HeaderAlignment As TxAlignment
Property HeaderGlyph As IPictureDisp
Property HeaderMaxLineCount As Long
Property MaxLength As Long
Property MinWidth As Long
Property ReadOnly As Boolean
Property Sizing As Boolean
Property VertAlignment As TxTextAlignment
Property Visible As Boolean
Property Width As Long
Sub Delete()
|
We can see that there are a few other enumerated types for some of the properties, such as the TxAlignment value for the HeaderAlignment & Alignment properties and the TxTextAlignment for the VertAlignment property. A full list of these enumerated values follows:
| |
TxAlignment
|
| |
taLeftJustify = 0
taRightJustify = 1
taCenter = 2
|
| |
TxTextAlignment
|
| |
tlTop = 0
tlCenter = 1
tlBottom = 2
|
The method of adding columns at run time examined in this article will allow you to take your use of SalesLogix DataGrids to a new level. No longer will you need to set conditions in the grid or try to figure out how to filter on a joined value. Now, you can simply change the SQL and set up the Columns. In a future article we’ll look at how to make a completely dynamic DataGrid by generically adding the Columns (without knowing the columns in the query ahead of time). Keep checking for this in the related articles below.
Until next time, Happy Coding.
-Ryan
|
|
|
|
|
|
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]
|
|
|
- subject is missing.
- comment text is missing.
|
|
|
| Runtime DataGrids
Posted: 11/27/2002 11:42:11 AM | fiogf49gjkf0d Step two to this is even better. Back in the old DATATRIEVE days we had a QueryName, QueryFormat kinds of stuff which is like the DISPLAYNAME in the SECTABLEDEFS table. What we need are the Default Values for certain properties of the Column whenever you create the Grid using the fields.....
So if Account.Account is "Company", default width XXX and Opportunity.aCTUALAMOUNT is 'Actual $', default width 72, Format Type Currency, Format String %10.0m% then that would be set up everytime........
Even better would be if this occurred everytime you built a Query or DataGrid.....you get the field and the defaults are automatically filled in.....
Thoughts on this?
RJ Samp | |
|
| Thanks RJ...
Posted: 12/2/2002 12:19:27 AM | fiogf49gjkf0d The article that covers what you mentioned is "in the works" and coming soon.
-Ryan | |
|
| Re: Thanks RJ...
Posted: 12/14/2002 11:10:35 AM | fiogf49gjkf0d The article that shows how to dynamically create the columns based on the fields in the query is complete. See 'Related Articles' above.
-Ryan | |
|
| Dynamically Creating DataGrid Columns
Posted: 2/11/2003 1:40:44 PM | fiogf49gjkf0d Thanks a bunch! A great article. Using VB6 to explore inside these ActiveX controls is a real insight. I have a related point. If you are using a Managed View with a grid to a stand alone table, you must use a script on the form open event to set the Grid's SQL query to display the entire table. If you attempt to simply use the SQL parameter property in the grid at design time, you will have to declare a "Bind ID" or the grid will not work. I don't know why. If you use a real BindID, then you will not be displaying the entire table. But if you enter the SQL statement at run time (ie: DataGrid1.SQL.Text = "Select ...."), the grid works correctly.
I'm new to this website and to SalesLogix Version 6.0. This site seems like it is and will be a wonderful source of information for all of us. We should all contribute. ...And talking about that, did you know that the Tab Control doesn't work correctly? Controls placed on secondary tabs (ie: ActiveIndex > 0) end up being dumped onto the first tab instance. A simple workaround is to shorten the height of the Tab Control to only show the tab headings. Directly belown this control, place a series of overlaying Panels which conform to the outline of the intended Tab control layout. And you got, as the user clicks on one tab or another, have a simple script which make the appropriate tab visible and the other not visible.
Thanks again,
Bob Levine, Advantageware, Inc.
| |
|
| Pardon my English
Posted: 2/11/2003 1:44:31 PM | fiogf49gjkf0d That last sentence should have read: And, you got it.., as the user clicks on one tab or another, have a simple script which makes the appropriate tab visible and the others not visible.
Bob Levine
| |
|
| Re: Tab control...
Posted: 2/11/2003 2:22:03 PM | fiogf49gjkf0d Yes Tab Control is broken and will be fixed in SP1. The problem exists when you either rename a control you've dropped on the tab, or attempt to move (or paste) an existing control into the tab. So, solution is to drop directly from toolbox into the tab and then don't rename them (or drop a panel first onto the tab, which you don't rename, and then drop all other controls into it).
The problem you describe with the v6 DataGrid applies when the grid is on a form that is "launched" modally. It will not work unless the grid has "something" in the BindID. So, I'll just put something there like "SOMEVALUE", but not put anything in the bind side in the query. Then all works well. Of course, you can just set the SQL and set up the columns and this is not a problem (BindID ot not).
Glad you like the site.
-Ryan | |
|
| Related Question
Posted: 2/13/2003 4:47:25 PM | fiogf49gjkf0d In playing around with the DataGrid control, I created a Managed form with a grid and a related Edit View to add and edit records in the common table. Upon an Add, the grid refreshes automatically. But upon an Edit, the grid does not refresh. I dumped a ".Refresh" into the grid's double click event, and yes, double clicking will cause the grid to now refresh. But how can I set the properties to have the grid automatically do a refresh after an edit. Also, in prior versions, it seemed easy to have the old grid invoke the edit view upon a double click on a given row. In this new grid, I don't see how this edit function can be invoked via a double click. Any suggestions?
| |
|
| Re: Related Question
Posted: 2/17/2003 10:42:52 AM | fiogf49gjkf0d Bob,
This is a bug in the current version. It refreshes fine after Adds & Deletes, but not Edits. This is the case for *any* datagrid that has been launched on a modal form (ie: It does not happen for a grid on the A/C/O detail screens or a tab).
Haven't checked yet if it is on the list for fixes in SP1.
-Ryan | |
|
| Grid Refresh.....
Posted: 2/18/2003 11:14:33 AM | fiogf49gjkf0d Ryan,
Thanks again. I wouldn't mind if I had a functional 'return from edit' event where I could place a .Refresh statement. Until this is fixed, I placed a manual .Refresh statement in the double click event for the grid.
Consider this a workaround but not a fix.
Bob
| |
|
| Formating Question
Posted: 2/22/2003 9:46:38 AM | fiogf49gjkf0d This article really helped me address a requirement I had for my current project. A quick question I couldn't figure out how to format a column. I would like the set a column to number and do a %.0n or %.2n format to it. Are these properties exposed? | |
|
| Re: Formatting Question...
Posted: 2/24/2003 6:13:27 PM | fiogf49gjkf0d >Are these properties exposed?
Not yet. There are many of the "extended properties" that you can't get to as of yet. This is the case for many of the non-standard column types as well. You can do things like create a checkbox column, but can't get to the properties specific to the checkbox column to set it's properties. But even on the standard column type, you cannot get to things like FormatType property yet. I assume that SLX will expose these objects in a future release.
For now, the workaround I use for format type is to do all the formatting in the SQL statement I bind to the grid. Know what I mean? Eventually these objects will be exposed and I'll write a followup article on the subject. I haven't seen this change in the SP1 beta yet, so it might not be until 6.1.
-Ryan | |
|
| SQL Parse error
Posted: 4/5/2003 5:49:07 PM | fiogf49gjkf0d Ryan,
I was able to use your example and it worked fine. I tried to modify it to do something slightly different and I'm getting an error message "Failed to Parse SQL" The statement that I am passing to the data grid is:
.SQL.Text = "Select c.LastName, c.FirstName from C_IAContact iac, join C_InvAccount ia on iac.c_invaccountid = ia.c_invaccountid join Contact c on iac.contactid = c.contactid where c.lastname = '" & strLastName & "' and c.firstname = '" & strFirstName & "'".
I wrote this first in Query Ananlyzer so I know technically it works. Not sure where it's getting stuck. Any ideas?
Thanks,
Tom | |
|
| Re: SQL Parse error...
Posted: 4/14/2003 4:47:03 PM | fiogf49gjkf0d Very strange join syntax you're using there. The SLX Provider does not like any kind of cross-join (which you're syntax looks a lot like to me).
Instead always use explicit inner/outer/left/right/etc joins. Re-do your query.
-Ryan | |
|
| Populating a datagrid from an external datasourse in v5.2
Posted: 5/6/2003 3:13:37 PM | fiogf49gjkf0d Can anyone provide assistance in populating a datagrid from an external datasourse in v5.2? I am aware this will be much easier in v6 but at this time we cannot upgrade. Basically, we have a data warehouse that I would like to query and return a record set in a data grid. I have the external ID stored in the Contact table. I have been able to get this to work populating a listbox but the formatting of the columns becomes an issue.
Any assistance is greatly appreciated.
Alan | |
|
| Re: Populating a datagrid from an external datasourse in v5.2...
Posted: 5/15/2003 5:51:07 PM | fiogf49gjkf0d Personally, I would write it as an external application, like in VB or similar. Then you could launch it and pass any ID values on the command line to the app. The app would be responsible for gathering the external data and bind it to a grid. If you wanted to keep it inside of SLX, then would it be possible for you to create a SQL view in the SLX database to view this data? As long as the view is owned by sysdba then you can select it as the table for a SLX grid.
-Ryan | |
|
| Populating a datagrid from an external datasourse in v5.2
Posted: 6/3/2003 9:34:48 AM | fiogf49gjkf0d Thanks for the advice. I was able to create a view to return the external data in a grid. However, the grid will only return data if the view is written against a database that is on the local SQL Server with the SLX database. The database that I need to query is on another SQL Server on the network. I created a linked server to that database. The query to the linked database runs in QA but will not return rows in the SLX datagrid. Any ideas? | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 3/4/2004 4:17:32 PM | fiogf49gjkf0d Excellent article. What is the trick to using memo fields (LONGNOTES) in a grid? The only thing I can get it to do is show an icon of a note... | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 5/17/2005 1:53:12 PM | fiogf49gjkf0d Is there any way to format a phone number in a dynamicly created column. I didn't see it as one of the column types?
Thanks,
Matt R | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 5/23/2005 4:47:59 PM | fiogf49gjkf0d Any ideas on how to set the column type to a picklist and set the picklist name? | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 5/23/2005 10:07:54 PM | fiogf49gjkf0d Brian,
Note this only works with 6.2 or higher - To create a picklist column you do the following:
Set col = DataGrid1.Columns.Add(15) 'ctPickList
col.PickListName = "My Picklist"
col.Caption = "My Picklist Column"
col.FieldName = "MYFIELD"
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 5/23/2005 10:10:35 PM | fiogf49gjkf0d Matt,
To format a value as a phone number requires the use of 6.2 or higher. In 6.2 you can do the following:
DataGrid1.Columns(index).FormatType = 7 'ftPhone
If you are using a version prior to 6.2 then your only hope is to create the column at design time and set the format type property for the column or do any formatting in the SQL the grid is bound to (and hope the provider doesn't choke on it).
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 5/23/2005 10:14:04 PM | fiogf49gjkf0d Doug,
To use a memo field in the grid, you cannot use a Standard column type (ctStandard = 0). You must change the column type to a memo column type (ctMemo = 14) and then set the PaintStyle property to a 2 (bpsText) to display the contents as text.
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/5/2005 5:19:03 AM | fiogf49gjkf0d Ryan,
Do you have any idea on how to display a picture blob field in a data grid?
I have added a column of type ctPictureBlob (10) to the grid and bound it to a blob field in the database.
Unfortunately there are only two types of icons displayed: an empty note icon when the db field contains no data, and a note icon with some pixels in it when the blob is filled with data. But nothing that looks like the nice picture I get using the same blob in an image control.
Thanks,
Ingmar
| |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/18/2005 5:31:29 PM | fiogf49gjkf0d Ingmar,
To tell the truth I've not tried to bind a grid column to a image in the database - but I doubt the SLX grid control will be able to do that. Since the options are icon & text (or default which still just means icon or text) my guess is that the only thing you'll get to display is an icon indicating that there is BLOB data there, but not the image itself. You'll probably have to use some other 3rd party grid control.
-Ryan | |
|
| How do we get the a field value when the grid is multiselect
Posted: 7/18/2006 8:58:00 PM | fiogf49gjkf0d x = grid.selection.item(i) always returns nothing | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/19/2006 12:08:43 AM | fiogf49gjkf0d Binu,
There is a bug with the grid, but you can work around it by adding the primary key field to the grid and making it hidden. Then you will be able to get the selected rows. Make sense?
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/19/2006 12:32:02 PM | fiogf49gjkf0d Ryan - How do you assign a primary key to the recordset. I have multiple ID fields in the query I use to populate the grid dynamically. Am I being a bone-head and missing the obvious? Thanks for you help. | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/19/2006 12:53:25 PM | fiogf49gjkf0d Binu,
When you set up a grid, the primary key for the base table is assigned as the KEYFIELD property. This is the value you will retrieve with calls to grid.selection.item(i). This is also the field that you have to explicitly add to the fields in the grid (and make hidden) in order for calls to grid.selection.item(i) to work.
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 7/19/2006 9:33:05 PM | fiogf49gjkf0d Yep, I didn't have any base table for the grid. Works like a charm now...Thank you very much for your help, this will make my life a lot easier... | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 11/12/2007 9:22:00 AM | fiogf49gjkf0d Do we know what the ftFormatTypes are?
from the SLX exportGridorRStoEXCEL script:
Select Case strFormatType
Case 3 'adInteger
GetDBFormatType = "#,##0" 'DNL
Case 4 'adSingle
GetDBFormatType = FormatCurrencyString
Case 5 'adDouble
GetDBFormatType = FormatCurrencyString
Case 6 'adCurrency'
GetDBFormatType = FormatCurrencyString
Case 11 'adBoolean
GetDBFormatType = "#,##0" 'DNL
Case 131 'adNumeric
GetDBFormatType = "#,##0" 'DNL
Case Else
GetDBFormatType = ""
What's ftUSERNAME?
ftOWNER
ftPercentage?
ftPhoneNumber?
etc.
Thanks!!! | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 9/22/2009 1:47:27 PM | fiogf49gjkf0d Ryan (or anyone) ... I'm trying to add a picklist to the Opportunity Products grid in v7.5.1. When I added the parameter "col.PickListName = " ... it says "Object doesn't support this property or method 'col.PickListName'.
Any thoughts?
Also, how do I add a calendar control for a date field? I have that as col.FormatType = 3, and it shows the date and all, but it is not displaying the calendar.
Thanks! | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 9/22/2009 4:09:01 PM | fiogf49gjkf0d Kevin,
Maybe try checking to make sure you're creating the correct column type. I'd suggest starting a post in the forums where you can post more of your code for us to look at.
-Ryan | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 9/22/2009 4:12:47 PM | fiogf49gjkf0d Ryan - Thanks for the response. I figured out what the problem was ... I had the column correct, but the correct syntax for the picklist name is:
col.PicklistName = Application.PickLists.Item("Registration Status").Name | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 9/22/2009 5:30:07 PM | fiogf49gjkf0d Kevin,
Or you could just do this:
col.PicklistName = "Registration Status" | |
|
| Re: Creating DataGrid Columns at Runtime
Posted: 2/18/2010 5:03:10 AM | fiogf49gjkf0d Thanks M. Farley for this article.
I'm trying to put a lookup control on a datagrid.
The issue is on getting the lookupID
My code :
Application.BasicFunctions.DoInvoke "Lookup", "USERINFO:NOMUTILISATEUR"
strResult= Application.BasicFunctions.InvokeResult
InvokeResult returns blank
How would you do ?
Thanks for your help
| |
|
| |
| Re: Creating DataGrid Columns at Runtime
Posted: 8/22/2013 7:21:45 AM | fiogf49gjkf0d Hi Ryan,
Thanks for this great article that helped me a lot.
However I am still having trouble creating Image Columns at runtime with SLX 7.5.2.
I managed to add a new ImageColumn using myDataGrid.Columns.Add(5) but then I can't see any property or method in the resulting column that would allow me to specify the Images to be used.
Anyone out there having an idea on how to do so?
Armand | |
|
|
|
|
|
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...
|
|
|
|
|
|