I want to share some code from my custom grid class that creates an easy way to programmatically bind columns in a grid to a cursor that is opened in the workspace. Basically, this method binds each column’s ControlSource property to the field in the cursor that you specify. It also handles calculated fields, Casting, and a few more things I’ll show in the code samples below.
You do have a custom grid class that you use in your apps, right? So, now just do two things to your grid class:
- Create a new placeholder method on your grid class named SetColumnControlSources().
- Create a new method named BindColumn() and add my code from the BindColumn() code sample below.
Now, in each instance of the grid that you use on a form or class or subclass, create the required columns on the grid and set the header captions to match what you intend to show in the grid from the cursor. Be sure to give each column a smart Name property for easy coding (see example below). You do not set the ControlSource of each column while designing the grid. That’s what my fancy BindColumn() method does for you, along with a few more things you’ll see in the code sample. You will make calls to the BindColumn() method from your SetColumnControlSources() method to do the real work. I’ll show you how…
In the SetColumnControlSources() method of each grid instance, you’ll write code like this (specific to each grid<—>cursor matching that you want to build):
Sample SetColumnControlSources() method code:
Local lcRecordSource
With this
.BindColumn(.colItem, ‘item’)
.BindColumn(.colPrint, ‘print’)
.BindColumn(.colPartNo, ‘part_no’)
.BindColumn(.colAltPartNo, ‘altpartno’)
.BindColumn(.colDwgNo, ‘dwg_no’)
.BindColumn(.colDesc, ‘desc’)
.BindColumn(.colQty, ‘qty’)
.BindColumn(.colPrice, ‘price’)
.BindColumn(.colTotal, ‘qty * _RecordSource.price’)
.BindColumn(.colLaborCost, ‘laborcost’)
.BindColumn(.colLaborHrs, ‘laborhrs’)
.BindColumn(.colMachRate, ‘laborcost / _RecordSource.laborhrs’, ‘N(10,2)’)
.BindColumn(.colMtlCost, ‘mtlcost’)
.BindColumn(.colMtlMarkup, ‘mtlmarkup’)
.BindColumn(.colPartCost, ‘laborcost + _RecordSource.mtlcost + _RecordSource.mtlmarkup’)
.BindColumn(.colTotalCost, ‘qty * (_RecordSource.laborcost + _RecordSource.mtlcost + _RecordSource.mtlmarkup)’)
EndWith*– Other fancy stuff you can do in addition to the above —
lcRecordSource = this.RecordSource
If !Empty(this.RecordSource)
this.colItem.DynamicFontUnderline = ‘iif(‘ + lcRecordSource + ‘.divider=.t., .t., .f.)’
this.colprice.DynamicCurrentControl = ‘iif(‘ + lcRecordSource + ‘.no_quote, [lblNoQuote], [Text1])’
EndIf
Notice how we can build calculated columns that are not even in the cursor, and easily setup a Cast() to control decimals and some DynamicXXX formatting too. Plus, it gives me one nice code window to see all my column binding without having to use the Property Sheet to see what each column is bound to.
So the above is *your* code, and now here’s *my* custom BindColumn() method code that you need to add to your grid class to handle the code you’ll write like the above. This methods assigns the Column.ControlSource property, expanding any _RecordSource references, and also handles the optional Cast() function (see code for .colMachRate above to see how to specify a Cast() format in the third parameter of the BindColumn method).
BindColumn() method code:
Procedure BindColumn() (Add this method to your grid class)
Lparameters toColumn, tcBindExpression, tcCast
Local lcFieldExpression, lcBindExpression, lcRecordsource
If Vartype(toColumn) = ‘O’ and Upper(toColumn.baseclass) = ‘COLUMN’
lcRecordsource = toColumn.parent.RecordSourceIf !Empty(lcRecordsource) and !Empty(tcBindExpression)
lcFieldExpression = Alltrim(Strtran(Upper(tcBindExpression), ‘_RECORDSOURCE’, lcRecordSource))*– Combine RecordSource.FieldExpressiopn
lcBindExpression = lcRecordSource + ‘.’ + lcFieldExpression*– Wrap in Cast() statement if casting was passed in
If Vartype(tcCast) = ‘C’
lcBindExpression = ‘Cast(‘ + lcBindExpression + ‘ as ‘ + tcCast + ‘)’
EndIf*– Finally, set controlsource on column…
toColumn.ControlSource = lcBindExpression
EndIfElse
Return
EndIf
Finally, to make it all work, you simply set the RecordSource property on the grid and then call SetColumnControlSources():
thisform.Grid1.RecordSource = ‘csrDataCursor’
thisform.Grid1.SetColumnControSources()
I found having this in place to be an effective way to handle times when I need to kill the cursor, rebuild it, and then re-bind to it.
Like this:
thisform.Grid1.RecordSource = ‘’
*—Now rebuild the cursor
thisform.Grid1.RecordSource = ‘csrDataCursor’
thisform.Grid1.SetColumnControSources()
So there you go. Several tricks could be used to pass in the cursor field that you want to bind to. I’ve also thought about including a provision for passing in the Header caption too.
Enjoy, revise, and post comments to share any other ideas you have for extending this.
Slick.
You didn't post the grid's setColumnControlSources() method, but I'm picturing a FOREACH oColumn IN This; oColumn.bindColumn(); NEXT;?
I used to use the Comment field for holding, but I like your custom field idea better.
I did post a sample of what a SetColumnControlSources() method could look like. It's the first section of sample code that's posted near the top. It's a model of how you might write your method to work on each column by making calls into my BindColumn() method. There are lots of potential ways to iterate over your columns, I just showed how I do it in per column kind of way working directly on each column. So, the second code section is the actual BindColumn() method that you need to add to your base class. That's the real constant code base that I'm offering here, along with the sample of how to make use of it.