Thanks Cetin,
I've been working with this control for the last several days and am starting to see some results. I especially appreciate the demostration of the sorting. I am still trying to grasp the Shape command and still have some research to do with the ADO connection (as I haven't used this before). Still I do have it working at a very basic stage. I do have a few questions that I hope you might know:
1) Is there a way to change column captions?
2) Is this conrol resizeable? I can't find that property, so I assume not.
3) Is there a way to not show certain fields that are included in the shape command, such as 'customer ID'?
4) I keep getting an error 'Command Not Available when connecting to heirarchical data'. This occurs when setting the sort property on the hflexgrid. What causes this?
Sorry for so many questions. Thanks very much.
David
>Geoff,
>chm at least was available with VS6 help (MSDN oct 2001 as I remember). If installed explicitly check the chm under MSDN folder. I don't have 't on th's computer but when I get to work I might locate it.
>
>Here are few samples:
>1) This one demonstrates both 'grouping sort' and some other features like coloring.
>2) Demonstrates hierarchical data view - like a Treeview but more like a TreeData. Also demonstrates coloring again but this time 'bandwise'
>
>Note: In both samples the control is HierarchicalFlexGrid and not FlexGrid. Hflex does all Flex does and more, I found it to be more reliable.
>
>
>#Define TESTDATALOC _samples+'data\testdata.DBC'
>
>Public oForm
>oForm = Createobject('myForm')
>oForm.Show
>
>Define Class myform As Form
> Top = 0
> Left = 0
> Height = 480
> Width = 750
> DoCreate = .T.
> Caption = "Flex Grid Sort Sample - Drag a column to left to group by it"
>
> Add Object hflex As OleControl With ;
> Top = 0, ;
> Left = 0, ;
> Height = 420, ;
> Width = 750, ;
> Name = "Hflex", ;
> OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'
>
> Add Object cmdFun As CommandButton ;
> with Top = 425, Caption='Show some other features', AutoSize = .T.
>
> Procedure dosort
> Lparameters toObject
> With toObject
> .Col = 0
> .ColSel = .Cols - 1
> .Sort = 1
> Endwith
> Endproc
>
> Procedure hflex.MouseDown
> *** ActiveX Control Event ***
> Lparameters Button, Shift, x, Y
> With This
> .Tag = ""
> If .MouseRow = 0
> .Tag = Str(.MouseCol)
> .Drag( 1 )
> Endif
> Endwith
> Endproc
>
> Procedure hflex.DragDrop
> Lparameters oSource, nXCoord, nYCoord
> If !Empty(This.Tag)
> With This
> .Redraw = .F.
> .ColPosition(Val(.Tag)) = .MouseCol
> Thisform.dosort(This)
> .Redraw = .T.
> Endwith
> Endif
> Endproc
>
> Procedure Init
>
> Local oRecordset,oConnection, strCn, strShp
>
> strCn = [Provider=MSDataShape;Persist Security Info=False;]+;
> [Data Source=]+TESTDATALOC+[;Data Provider=VFPOLEDB]
> strShp = [SHAPE { select customer.cust_id, Company, ]+;
> [ orders.order_id, order_date, order_net, shipped_on, ]+;
> [ line_no, prod_name ] +;
> [ From customer ]+;
> [ Left Outer Join orders ]+ ;
> [ On customer.cust_id = orders.cust_id ] +;
> [ inner Join orditems ]+ ;
> [ On orditems.order_id = orders.order_id ] +;
> [ inner Join products ]+ ;
> [ On orditems.product_id = products.product_id } ]
>
>
> oRecordset = Createobject("adodb.recordset")
> oConnection = Createobject("adodb.connection")
>
> With oConnection
> .Provider = "MSDataShape"
> .ConnectionString = strCn
> .Open
> Endwith
>
> With oRecordset
> .ActiveConnection = oConnection
> .Source = strShp
> .Open
> Endwith
>
>
> With Thisform.hflex
> .Datasource = oRecordset
> For ix = 1 To .Cols - 1
> .MergeCol(ix) = .T.
> Endfor
> .MergeCells = 3
>
> Endwith
> Thisform.dosort(Thisform.hflex)
> Endproc
>
> Procedure cmdFun.Click
> With Thisform.hflex
> .FixedRows=1
> .FixedCols=1
>
> .FillStyle = 1
> .WordWrap = .T.
>
> * First Column
> .Row = 1
> .Col = 0
> .Rowsel = .Rows-1
> .ColSel = 0
> .CellBackcolor = 0xFF0000
> .CellForeColor = 0xFFFFFF
> .CellAlignment = 9
> .CellFontName = 'Arial'
> .CellFontBold = .T.
> .CellFontItalic = .F.
> .CellFontSize = 9
>
> * First Row
> .Row = 0
> .Col = 1
> .Rowsel = 0
> .ColSel = .Cols-1
> .CellBackcolor = 0xFF0000
> .CellForeColor = 0xFFFFFF
> .CellAlignment = 9
> .CellFontName = 'Arial'
> .CellFontBold = .T.
> .CellFontItalic = .F.
> .CellFontSize = 9
>
> *Initial coloring of data cells
> .Col = 1
> .Row = 1
> .ColSel = .Cols-1
> .Rowsel = .Rows-1
> .CellAlignment = 9
> .CellBackcolor=0x00FFFF
>
> * Enter some manual text on row 4,7 on lefmost fixed col
> .TextMatrix(4,0)="Manual 4"
> .TextMatrix(7,0)="Manual 7"
>
> * Enter text manually in left topmost corner cell
> .TextMatrix(0,0)="VFP:)"
>
> * Change color for a group of cells
> .Row = 4
> .Col = 5
> .Rowsel = 5
> .ColSel = 7
> .CellBackcolor = 0xFFFF00
> .CellForeColor = 0xFF0000
> Endwith
> Endproc
>Enddefine
>>
>Sample 2
>
>#Define TESTDATALOC _samples+"data\testdata.DBC"
>
>Public oForm
>oForm = Createobject('myForm')
>oForm.Show
>
>
>Define Class myform As Form
> Top = 0
> Left = 0
> Height = 450
> Width = 750
> DoCreate = .T.
> Caption = "Form1"
> Name = "Form1"
>
> Add Object command1 As CommandButton With ;
> Autosize = .T., ;
> Top = 0, ;
> Left = 0, ;
> Name = "Set1", ;
> Caption = 'Sample 1'
>
> Add Object command2 As CommandButton With ;
> Autosize = .T., ;
> Top = 0, ;
> Left = 0, ;
> Name = "Set2", ;
> Caption = 'Sample 2'
>
> Add Object hflex As OleControl With ;
> Top = 0, ;
> Left = 0, ;
> Height = 420, ;
> Width = 750, ;
> Name = "Hflex", ;
> OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'
>
> Procedure LoadSet
> Lparameters tnSet
> Local oRecordset,oConnection, strCn, strShp
>
> strCn = [Provider=MSDataShape;Persist Security Info=False;]+;
> [Data Source=]+TESTDATALOC+[;Data Provider=VFPOLEDB]
>
>
> lcSel1 = [ select customer.cust_id, ]+;
> [ customer.Company,]+;
> [ orders.order_id,]+;
> [ orders.Order_date ]+;
> [ From customer ]+;
> [ inner Join orders On customer.cust_id = orders.cust_id ]
>
> lcSel2 = [ select od.order_id, od.line_no, ]+;
> [ products.prod_name, ]+;
> [ products.no_in_unit As 'Packaging', ]+;
> [ od.unit_price, ]+;
> [ od.Quantity, ]+;
> [ od.unit_price * od.Quantity As ExtendedPrice ]+;
> [ From orditems As od ]+;
> [ inner Join products On od.product_id = products.product_id ]
>
> Do Case
> Case tnSet = 1
>
> strShp = [SHAPE { select Company, cust_id from customer } ]+;
> [Append (( Shape { Select Distinct First_name, Last_name, a.emp_id + cust_id As "Emp_sel", cust_id From employee a inner Join orders b On a.emp_id = b.emp_id } ]+;
> [Append (( Shape { Select Order_date, order_net, shipped_on, emp_id + cust_id As "Emp_sel",order_id From orders } ]+;
> [Append ( { Select order_id, line_no, prod_name From orditems inner Join products On products.product_id = orditems.product_id } As rsOrditems ]+;
> [Relate order_id To order_id )) As rsEmployee ]+;
> [Relate emp_sel To emp_sel )) As rsOrders ]+;
> [Relate cust_id To cust_id ) ]
> Case tnSet = 2
> strShp = [ SHAPE ]+;
> [(Shape {]+lcSel1+[ } as rs1 ]+;
> [ Append ({]+lcSel2+[ } AS rsDetails RELATE order_id TO order_id), ]+;
> [ Sum(rsDetails.ExtendedPrice) As OrderTotal, Any(rsDetails.order_id)) As rsOrders ]+;
> [Compute rsOrders, ]+;
> [Sum(rsOrders.OrderTotal) As CustTotal, ]+;
> [Any(rsOrders.Company) As Cmpny ]+;
> [ By cust_id ]
>
> Endcase
>
> oRecordset = Createobject("adodb.recordset")
> oConnection = Createobject("adodb.connection")
>
> With oConnection
> .Provider = "MSDataShape"
> .ConnectionString = strCn
> .Open
> Endwith
> With oRecordset
> .ActiveConnection = oConnection
> .Source = strShp
> .Open
> Endwith
>
> With This.hflex
> .Datasource = oRecordset
> .Mergecells = 3
> .GridColorBand(1) = Rgb(255,0,0)
> .GridColorBand(2) = Rgb(0,0,255)
> .GridColorBand(3) = Rgb(0,255,0)
> .ColWidth(0,0) = 300
> .CollapseAll
> Endwith
> Endproc
>
> Procedure Init
> With This
> .Set2.Left = .Set1.Left + .Set1.Width + 5
> .hflex.Top = .Set1.Top + .Set1.Height + 5
> .hflex.Height = .Height - (.hflex.Top + 5)
> .hflex.Left = 5
> .hflex.Width = .Width - 10
> .LoadSet(1)
> Endwith
> Endproc
> Procedure Set1.Click
> Thisform.LoadSet(1)
> Endproc
> Procedure Set2.Click
> Thisform.LoadSet(2)
> Endproc
>Enddefine
>>Cetin
>
>>Hi Cetin
>>
>>I've never heard of this control before but it seems to be something I have a use for. Unfortunately I cant find the CHM for it and the MS help is a bit sparce.
>>
>>Do you have an example of how to use it?
>>Is it possible to have a DynamicBackColor with it?
>>
>>Regards
>>Geoff Scott
>>
>>>>>>I'm working on a form consisting of a treeview and combobox. I want the user to be able to re-sort the treeview, based on the selected combobox option. For instance, if my table consists of date, module, severity, status and description, I may want the user to be able to sort on any, or any combination, of selected fields
>>>>>>
>>>>>>The table I am extracting information from is not in heirarchical format, but does have a unique record ID field (numeric). I want to create a heirarchical table based on the user selection.
>>>>>>
>>>>>>I have been working on this and have figured out a fairly elaborate way to create the parent nodes and associate the detail data to the parent node. While my method works, it is limited to the number of levels that I am allowing programtically. While this will work for me, I can't help but feel that there must be a better way (such as a Select command), or an existing utility, which handles this.
>>>>>>
>>>>>>Any ideas?
>>>>>>
>>>>>>Thanks much.
>>>>>>
>>>>>>David
>>>>>
>>>>>David,
>>>>>You might want to check UT magazine June 2001 issue (first one).
>>>>>But IMHO it'd be easier and cleaner if you simply show the data in a HierarchicalFlexgrid control.
>>>>>Cetin
>>>>
>>>>Hi Cetin - thanks for the suggestion. I have never worked with that control, but will surely take a look at it. Do you know where can i find the Heirarchical Flexgrid control?
>>>>
>>>>David
>>>
>>>It's installed with VS and I think VFP only installation installs it too.
>>>
>>>in code:
>>>
>>>Add OBJECT hflex AS olecontrol WITH ;
>>> OleClass = 'MSHierarchicalFlexGridLib.MSHFlexGrid'
>>>
>>>I tried to locate one of the codes I posted before but UT search lies-none found:)
>>>Cetin
