Summing Column In QM Grid

[TheVig]

What I want is the edit box to update constantly...really on a change to the Grid...I put in the update button only to show the function I wish to perform.

Function Sum_Column

Code:

Copy      Help

\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 128 239 "Sum_Column"
;3 QM_Grid 0x54200001 0x200 5 0 120 195 ""
;1 Button 0x54030001 0x4 9 216 48 14 "OK"
;2 Button 0x54030000 0x4 78 216 48 14 "Cancel"
;4 Edit 0x54030080 0x200 66 198 60 15 ""
;5 Static 0x54000000 0x0 35 200 20 10 "Total"
;6 Button 0x54032000 0x0 6 198 27 18 "Update"
;END DIALOG
;DIALOG EDITOR: "" 0x2030001 "" "" ""
str controls = "5"
str e5
if(!ShowDialog("Sum_Column" &Sum_Column &controls)) ret
ret
;messages
sel message
,case WM_INITDIALOG
,;Tally
,ICsv c=CreateCsv
,int hlv=id(3 hDlg)
,LvAddCol hlv 0 "Col1" 85
,LvAddCol hlv 1 "Col2" 85
,case WM_DESTROY
,case WM_COMMAND goto messages2
ret
;messages2
sel wParam
,case 6
,c=CreateCsv ;;create again because we use local variable, just to simplify grid populating and retrieving data
,c.FromQmGrid(id(3 hDlg) 0)
,int i
,CURRENCY i2
,for i 0 c.RowCount
,,i2=i2+val(c.Cell(i 1) 2)
,_s=i2
,_s.setwintext(id(4 hDlg))
,case IDOK
,case IDCANCEL
ret 1

Thanks for help

[Gintaras]

QM grid control programming is not easy. Also it is not available in exe. Better use some ActiveX control grid. For example scgrid.

QM_Grid help and definitions in QM.

Function qmgrid

Code:

Copy      Help

;About QM_Grid control

;This control is based on SysListView32 control. You can use its messages, notifications
;and styles. They are documented in the MSDN Library.
;In addition to SysListView32 control functionality, QM_Grid control allows user to edit all
;or some cells, and can also show other controls, such as combo box, check box and button.
;By default, this control can be used to enter text, like with edit control but where each
;line is divided into columns. It also can be used to enter values of multiple properties,
;instead of dialog with multiple controls, similarly as in many programming IDEs.
;By default, to edit cells, is displayed temporary single-line edit control. You can use
;LVM_QG_SETCELLTYPE and/or LVM_QG_SETCOLUMNTYPE to set other control types and styles.
;These control types/styles are supported: single-line edit (default), multiline edit,
;combo box (edit with drop-down list), sorted combo box, check box (sets item text to Yes
;or empty), and none (read-only text). Also, a button can be displayed by the control.

;A. Initializing
;1. Create control of QM_Grid class (for example in dialog editor). Optionally use listview styles.
;2. Optionally set listview extended styles, imagelists.
;3. Optionally send LVM_QG_SETSTYLE.
;4. Add columns (LVM_INSERTCOLUMN).
;5. Optionally send LVM_QG_SETCOLUMNTYPE to set default cell control type for columns (initially it is QG_EDIT).
;6. Add items and subitems. You can use listview messages (LVM_INSERTITEM, etc). Or use LVM_QG_SETALLCELLS to add all cells. Or use ICsv.ToQmGrid. You can add items and subitems in single call, or you can add items only, and later add subitems.
;7. Optionally send LVM_QG_SETCELLTYPE to set control types of separate cells.

;B. Receiving notifications.
;You can optionally process LVN_ and LVN_QG_ notifications.
;You must process LVN_QG_BUTTONCLICK for cells that have QG_BUTTONATRIGHT style. For example, show a dialog and set edit control text.
;You must process LVN_QG_COMBOFILL for cells that have QG_COMBO type. Use CB_ADDSTRING to add combo box items. This message is sent each time before showing dropdown list.
;You optionally can process LVN_QG_COMBOITEMCLICK for cells that have QG_COMBO type. If you don't process it, or process but return 0, combo box edit control text is set to selected item's text. This message allows you to override this behavior. You for example can show a dialog, set edit control text, and return 1.
;Also you can process LVN_ENDLABELEDIT to validate user input, save/apply, etc.

;C. Getting text from cells.
;Use LVM_GETITEMTEXT to get text from each cell you need. Or, use LVM_QG_GETALLCELLS to get all cells. Or use ICsv.FromQmGrid.

;D. Mouse and keyboard navigation.
;Click cell to edit it.
;Click-drag at right or left to select rows. Also, you can Ctrl+Click or Shift+Click.
;Right click or use App key to show context menu.
;In cell edit mode:
,;Use Tab, Shift+Tab or arrows to edit next, prev, below or above.
,;Use Enter to insert and edit new row.
,;Use standard edit control navigation and context menu.
,;Use Esc to deactivate cell edit mode.
;Not in cell edit mode:
,;Use Spacebar to edit first editable cell of selected or first row.
,;Use Ctrl+A to select all, Delete to delete selected, Ctrl+Up/Down to move selected cell.


;//////////////////////////////////////////////////

;Definitions

;Styles (use with LVM_QG_SETSTYLE)
def QG_NOEDITFIRSTCOLUMN 1 ;;does not allow user to edit cells in first column.
def QG_NOAUTOADD 2 ;;does not allow user to add, delete, insert and move rows.
;By default, all cells are editable, and user can easily add, delete and move rows.
;And you can use list view control styles.

;Cell control flags (use with LVM_QG_SETCELLTYPE and LVM_QG_SETCOLUMNTYPE)
;control type
def QG_EDIT 0
def QG_COMBO 1
def QG_CHECK 2
def QG_NONE 7 ;;read-only text
def QG_CONTROLTYPEMASK 7 ;;not used
;control style
def QG_EDIT_MULTILINE 8 ;;use with QG_EDIT
def QG_COMBO_SORT 8 ;;use with QG_COMBO
def QG_BUTTONATRIGHT 16 ;;use with any type
;example: QG_EDIT|QG_EDIT_MULTILINE|QG_BUTTONATRIGHT ;;multiline edit control with button

;LVM_QG_SETALLCELLS flags
def QG_SET_FIRST 1 ;;set only first column
def QG_SET_NOFIRST 2 ;;set all except first column
;LVM_QG_GETALLCELLS flags
def QG_GET_NOFIRST 1 ;;get all except first column
def QG_GET_TRIM 2 ;;trim spaces from cell values

;Messages
def LVM_QG_FIRST (LVM_FIRST+240) ;;not used
def LVM_QG_SETSTYLE (LVM_QG_FIRST) ;;Sets grid style (defined above). wParam is styles, lParam is mask (can be used -1).
def LVM_QG_SETCELLTYPE (LVM_QG_FIRST+1) ;;Sets control flags of a single cell (defined above). wParam is item, lParam is column in low-order word and flags in high-order word (flags<<16|column).
def LVM_QG_SETCOLUMNTYPE (LVM_QG_FIRST+2) ;;Sets default control flags for all cells of specified column (defined above). wParam is column, lParam is flags.
def LVM_QG_SETALLCELLS (LVM_QG_FIRST+3) ;;Populates all cells from a str variable containing multistring. wParam is LVM_QG_SETALLCELLS flags (defined above), lParam is address of the str variable.
def LVM_QG_GETALLCELLS (LVM_QG_FIRST+4) ;;Gets all cells to a str variable as multistring. wParam is LVM_QG_GETALLCELLS flags (defined above), lParam is address of the str variable.

;Notes
;A multistring is array of strings where all strings are in same str variable, each string is terminated with null character. Multistrings can be stored in registry with REG_MULTI_SZ type.
;Checkbox cell text is "Yes" if checked, empty if unchecked.

;Notifications
def LVN_QG_FIRST (LVN_LAST) ;;not used
def LVN_QG_BUTTONCLICK (LVN_QG_FIRST) ;;Sent on button click. Do whatever you want and return 0.
def LVN_QG_COMBOFILL (LVN_QG_FIRST+1) ;;Sent on combobox dropdown. Fill the combo box and return 0.
def LVN_QG_COMBOITEMCLICK (LVN_QG_FIRST+2) ;;Sent on combobox item clicked. Return 0, or 1 to preserve edit control text.
def LVN_QG_CHANGE (LVN_QG_FIRST+3) ;;Sent on cell text changed. Return 0.

;Address of variable of this type is sent with LVN_QG_ notifications as lParam.
;Some members are used only with certain notifications.
type QM_NMLVDATA NMHDR'hdr hctrl hcb ctrltype item subitem cbindex $txt
;hdr - notification code, grid control handle and id.
;hctrl - edit control (not grid control).
;hcb - combo box control.
;ctrltype - cell flags from QM_GRID_ITEM_FLAGS.
;item and subitem - specifies the cell.
;cbindex - on LVN_QG_COMBOITEMCLICK it is index of selected combo box item.
;txt - edit control text.

;Also, the control extends these SysListView32 messages and notifications:
;LVM_EDITLABEL ;;lParam specifies subitem.
;LVN_BEGINLABELEDIT ;;LVITEM.iSubItem specifies subitem.
;LVN_ENDLABELEDIT ;;LVITEM.iSubItem specifies subitem. Return 1 to leave old text.

Example.
Requires QM 2.3.0 or later.

Function dlg_QM_Grid

Code:

Copy      Help

\Dialog_Editor
function# hDlg message wParam lParam
if(hDlg) goto messages

#compile "qmgrid"

if(!ShowDialog("dlg_QM_Grid" &dlg_QM_Grid 0)) ret

;BEGIN DIALOG
;0 "" 0x90C80A44 0x100 0 0 291 175 "QM_Grid"
;3 QM_Grid 0x54200001 0x200 0 0 294 154 ""
;1 Button 0x54030001 0x4 2 158 48 14 "OK"
;2 Button 0x54030000 0x4 52 158 48 14 "Cancel"
;END DIALOG
;DIALOG EDITOR: "" 0x2030003 "" "" ""

ret
;messages
sel message
,case WM_INITDIALOG
,int g=id(3 hDlg)
,;use first column as noneditable
,SendMessage g LVM_QG_SETSTYLE QG_NOEDITFIRSTCOLUMN -1
,;add columns
,LvAddCol g 0 "read-only" 80
,LvAddCol g 1 "edit" 50
,LvAddCol g 2 "edit+button" 80
,LvAddCol g 3 "edit multiline" 80
,LvAddCol g 4 "combo" 80
,LvAddCol g 5 "check" 50
,;set column cell control default types
,SendMessage g LVM_QG_SETCOLUMNTYPE 2 QG_EDIT|QG_BUTTONATRIGHT
,SendMessage g LVM_QG_SETCOLUMNTYPE 3 QG_EDIT|QG_EDIT_MULTILINE
,SendMessage g LVM_QG_SETCOLUMNTYPE 4 QG_COMBO
,SendMessage g LVM_QG_SETCOLUMNTYPE 5 QG_CHECK
,;populate ICsv variable
,ICsv c=CreateCsv; c.Separator=","
,lpstr si=
;a1,b1,c1,d1,e1,yes
;a2,b2,c2,d2,e2,
;,,c3
,c.FromString(si)
,
,;Make sure that ncols is equal to the number of columns in the grid that you want to set (also depends on ToQmGrid flags).
,;If not, ToQmGrid will fail.
,;out "ncols=%i, nrows=%i" c.ColumnCount c.RowCount
,
,;populate grid control
,c.ToQmGrid(g 0)
,
,case WM_DESTROY
,case WM_COMMAND goto messages2
,case WM_NOTIFY goto messages3
ret
;messages2
sel wParam
,case IDOK
,;get and show all cells
,c=CreateCsv; c.Separator=","
,c.FromQmGrid(id(3 hDlg))
,str s
,c.ToString(s)
,mes s
,
,case IDCANCEL
ret 1
;messages3
NMHDR* nh=+lParam
if(nh.idFrom=3) ret DT_Ret(hDlg gridNotify(nh))

Function gridNotify

Code:

Copy      Help

;/dlg_QM_Grid
function NMHDR*nh

;Examples for all QM_Grid-specific notifications.

NMLVDISPINFO* di=+nh
QM_NMLVDATA* cd=+nh
str s
sel nh.code
,case LVN_BEGINLABELEDIT
,,out "begin edit: item=%i subitem=%i text=%s" di.item.iItem di.item.iSubItem di.item.pszText
,case LVN_ENDLABELEDIT
,,out "end edit: item=%i subitem=%i text=%s" di.item.iItem di.item.iSubItem di.item.pszText
,case LVN_QG_BUTTONCLICK:
,,out "button: item=%i subitem=%i text=%s" cd.item cd.subitem cd.txt
,,if(OpenSaveDialog(0 s))
,,,s.setwintext(cd.hctrl)
,case LVN_QG_COMBOFILL
,,out "combo fill: item=%i subitem=%i" cd.item cd.subitem
,,TO_CBFill cd.hcb "one[]two[]show inp"
,case LVN_QG_COMBOITEMCLICK
,,out "combo click: item=%i subitem=%i cbitem=%i text=%s" cd.item cd.subitem cd.cbindex cd.txt
,,if(cd.cbindex=2)
,,,if(inp(s))
,,,,s.setwintext(cd.hctrl)
,,,,ret 1
,case LVN_QG_CHANGE
,,out "text changed: item=%i, subitem=%i, text=%s, newtext=%s" cd.item cd.subitem cd.txt _s.getwintext(cd.hctrl)

Function LvAddCol

Code:

Copy      Help

;/
function hlv index $txt width

;Adds column to SysListView32 control that has LVS_REPORT style (1).
;To create control with this style, specify 0x54000001 style in dialog definition.
;Index of first colunm is 0.


LVCOLUMNW col.mask=LVCF_WIDTH|LVCF_TEXT
col.pszText=@txt
col.cx=width
SendMessage hlv LVM_INSERTCOLUMNW index &col

[TheVig]

Problem...Using QM 2.3.0.2 with this section of code:

Function dlg_QM_Grid

Code:

Copy      Help

NMHDR* nh=+lParam
if(nh.idFrom=3) ret DT_Ret(hDlg gridNotify(nh))

gridNotify-->Error in "dlg_QM_Grid /1748"odlg_QM_Grid: unknown identifier.

So I won't be able to use QM Grid Control in an exe....that's probably a deal breaker If you could be kind enough to show me scgrid example...I'll search the forum, haven't had a chance yet....

All I really need it to do is sum the numbers in a column and put the summed value in an edit box...

Thanks for all your help, I didn't realize that QM Grid was so complicated. Really I thought this question would be a no brainer for everyone...
Jimmy Vig

[Gintaras]

I forgot to add the function. Now all here.

[stupomer]

Hi Gintaras,
I am taking a big deep breath and jumping into QM_Grid dialogs. They are very cool and I actually have come along much farther than I thought. I have been able to reverse engineer your example dlg_QM_Grid to be able to import a large excel spreadsheet with 14 columns and over a hundred rows. I have been able to assign checkboxes, combo boxes and save the changes back into my excel (csv) file using the ICsv functions.


My questions at this time are:

1) There seems to be a bug in the gridNotify function. When you attempt to add an input str s as the setwintext of cd.hctrl, there is an error RT: "window not"
However, the same step (i.e. setwintext(cd.hctrl)) works for

Function gridNotify

Code:

Copy      Help

,case LVN_QG_BUTTONCLICK:
,,out "button: item=%i subitem=%i text=%s" cd.item cd.subitem cd.txt
,,if(OpenSaveDialog(0 s))
,,,s.setwintext(cd.hctrl)

2) Is there a way to have different combo box choices for different columns. When I change the

Function gridNotify

Code:

Copy      Help

,case LVN_QG_COMBOFILL
,,out "combo fill: item=%i subitem=%i" cd.item cd.subitem
,,TO_CBFill cd.hcb "one[]two[]show inp"

to CBFill choices of my choosing, it makes those the options for all columns with LVM_QG_SETCOLUMNTYPE 7 QG_COMBO as its type

3) This might be asking too much but Is there an easy way to
- sort to sort columns, e.g. alphabetize by clicking on the upper bar
- filter - e.g. by one of the drop down choices in a CB Box for that column
- Search the grid
- select multiple cell contents at once (adjacent single cells in a column, whole rows, adjacent or not)

Thanks so much - even if the answer is that these aren't supported.

Stuart

[Gintaras]

3. QM_Grid does not support sorting etc. You would have to sort your data and send it to the control, replacing current its contents. To search, get all cells and search the array. Can select only full rows.

2. cd.subitem is column index.

1. When does not work? Note that these small controls are temporary. When the cell is deactivated, the control is destroyed.

[stupomer]

3. Thanks. I will probably continue with the solution I have with Excel but will look into some of the other grids that I can use with QM

2. Thanks.

1. Still having error. When I select select the cd.cbindex=2 (i.e. "show inp") option and enter data, it doesn't fill the cell but rather gives a window not found error. Is it working for you? I am running the functions straight with no modifications. The similar function works with the

Code:

Copy      Help

case LVN_QG_BUTTONCLICK

earlier in the same dialog.

Thanks again so much for all your responses.
Stuart

[Gintaras]

Please post code that does not work.

[stupomer]

I get the error:

Error (RT) in "ridNotify: window not found

when I select the combobox item from dlg_QM_Grid that calls on the case

Function gridNotify

Code:

Copy      Help

,case LVN_QG_COMBOFILL
,,out "combo fill: item=%i subitem=%i" cd.item cd.subitem
,,TO_CBFill cd.hcb "one[]two[]show inp"

in gridNotify.
Specifically, the "show inp" cb item, which then calls

Function gridNotify

Code:

Copy      Help

,case LVN_QG_COMBOITEMCLICK
,,out "combo click: item=%i subitem=%i cbitem=%i text=%s" cd.item cd.subitem cd.cbindex cd.txt
,,if(cd.cbindex=2)
,,,if(inp(s))
,,,,s.setwintext(cd.hctrl)
,,,,ret 1

The step:

,,,,s.setwintext(cd.hctrl)

seems to be the problem. The funny thing is that it works on an earlier step:

,,if(OpenSaveDialog(0 s))
,,,s.setwintext(cd.hctrl)

Thanks, but I hope this doesn't take too much of your time. I am still trying to find an outside grid solution that allow me to sort columns without reloading.

Thanks again for all your help.

Stuart

[Gintaras]

Probably it is a bug that I made in QM 2.3.0 and fixed later because now works well in the last, still not released, QM version.

[stupomer]

I can't wait - each new version of QM is more amazing than before!

Stuart

[TheVig]

QM grid control programming is not easy. Also it is not available in exe.

Why is QM Grid not available in exe?

[Gintaras]

Would add several KB. I always try to make exe smaller, and don't add rarely used functions.

[stupomer]

Hi,
I am trying to add a row of data into a QMGrid. I thought I knew how to do this directly but I realize I have been populating the QMGrid using ToQmGrid and FromQmGrid using Sqlite i.e. I have been adding the row to the Sqlite database and then repopulating the QmGrid from the Sqlite database.

That is find but now I want to grab some data from some other user entry field in the dialog and then have them be added to the QmGrid which is also in the dialog. I will save to the Sqlite database at dialog closure (or through Save button) but want to just add directly first to the QmGrid.

(e.g. Grab items from Field 1, 2, 3, 4 in the dialog and add those to columns 1, 2, 3, 4 in the last row of the QmGrid (i.e. new row entry).


I think I may have to use LVM_INSERTITEM but not sure how to use it and if only applicable at QmGrid at initialization. Any suggestions.

Thanks so much!!

Stuart

[Gintaras]

LVM_INSERTITEM can be used at any time.
Function LvAdd

Code:

Copy      Help

;/
function# hlv index lparam image ~s [~s1] [~s2] [~s3] [~s4] [~s5] [~s6] [~s7] [~s8] [~s9]

;Adds item to ListView32 control that has LVS_REPORT style and 1 to 10 columns.


if(index<0) index=0x7FFFFFFF
LVITEMW lvi.mask=LVIF_TEXT|LVIF_PARAM|LVIF_IMAGE
lvi.iItem=index
lvi.pszText=@s
lvi.lParam=lparam
lvi.iImage=image
index=SendMessage(hlv LVM_INSERTITEMW 0 &lvi)

int i; str* p=&s
for i 1 getopt(nargs)-4
,lvi.iItem=index
,lvi.iSubItem=i
,lvi.pszText=@p[i]
,SendMessage(hlv LVM_SETITEMTEXTW index &lvi)

ret index

[stupomer]

Perfect.
Thanks!!!!
Stuart

[stupomer]

For some reason I can't seem to add more than 28 arguments (not including hlv index lparam image) using the LvAdd function.

I keep on getting

Error in "LvAdd /214"oLvAdd: too many parts.

I have defined the QMGrid using QgAddColumns to have more columns and it does appear. But when I try to populate it with some values, it errors:
either in the LvAdd function or in the part of the function calling the LvAdd function.

Is this an inherent limit of arguments with this function.

The reason I needed to add so many more columns is to get around the character limit in an individual QmGrid field: I thought I would split and reassemble my text over many columns.

Thanks for any insight or alternatives:

Stuart

[Gintaras]

QM functions cannot have more than 31 argument.

Function LvAddArray

Code:

Copy      Help

;/
function# hlv index lparam image ARRAY(str)&a

;Adds item to ListView32 control that has LVS_REPORT style.

;a - array containing all items in row.


if(index<0) index=0x7FFFFFFF
LVITEMW lvi.mask=LVIF_TEXT|LVIF_PARAM|LVIF_IMAGE
lvi.iItem=index
lvi.pszText=@a[0]
lvi.lParam=lparam
lvi.iImage=image
index=SendMessage(hlv LVM_INSERTITEMW 0 &lvi)

int i
for i 1 a.len
,lvi.iItem=index
,lvi.iSubItem=i
,lvi.pszText=@a[i]
,SendMessage(hlv LVM_SETITEMTEXTW index &lvi)

ret index

Not tested.

[stupomer]

Thanks.
I will try this and let you know.
I just realized that for the data collection part, I don't need to have qmgrid so I can just write variables directly to db3.
Not sure why I didn't realize this earlier.