filtering a listview

AustrianOak · June 11, 2008

I have data in my listview and i want to filter some data out of the listview according to my preferences. what i had in mind was to create a drop down box with 3 or 4 options. i have already done so, but i need a basic idea on how the logic goes to actually "filtering out" the unwanted data. if i click one of the options that says 'blue', any listview item that doesn't contain the text 'blue' i want gone TEMPORARILY. how can i retrieve the data back when i unfilter the listview again.

is there some sort of listview item hide function?

nobbe · June 11, 2008

take an array of your data,

when filtering your listview take only the items that match the condition

if you change the condition, clear listview and fill again with other matches

leave original array intact

AustrianOak · June 12, 2008

what about using _GUICtrlListView_FindInText () in a for loop?

then depending on what the combo data is it will hide the listview items?

Zedna · June 12, 2008

what about using _GUICtrlListView_FindInText () in a for loop?
then depending on what the combo data is it will hide the listview items?

As far as I know there is no "Hide row" function.

I use "post #2 concept" in all my ListView filtering applications.

AustrianOak · June 12, 2008

but I'm not even sure of how to take an array of my data?....

and that would take longer to search through an ini then just to look in a listview....

Valuater · June 12, 2008

I use "post #2 concept" in all my ListView filtering applications.

8)

AustrianOak · June 16, 2008

I use "post #2 concept" in all my ListView filtering applications.

@Zedna:

Could you show me a simple piece of code from on of your listview applications so I can get an idea on how to go about it?

Zedna · June 16, 2008

@Zedna:

Could you show me a simple piece of code from on of your listview applications so I can get an idea on how to go about it?

Just look at my LogView project in my signature.

Note: it's in old AutoIt's syntax so take it as concept example.

In log_view.au3:

- NactiLog() - load LOG file from file into global data[] array

- Filtruj() - filtering of listview - based on given criteria deletes old listview values and fills listview with appropriate data from global

data[] array

- Filtruj() is called when some of input filter criteria changes

DaRam · June 16, 2008

If you are comfortable with Activex Data Objects (ADO) Recordsets, you can use the built in .Filter() property to Filter using SQL Like keywords (=, <> AND, etc.)

This might sound complicated initially, but the end result is easy implementation.

The steps are:

1. Create an ADO Recordset

$adoRsItems = ObjCreate( "ADODB.RecordSet" )

2. Define columns

With $adoRsItems
        .CursorLocation = 3; adUseClient
        .CursorType = 2; adOpenDynamic
        .LockType = 2; adLockPessimistic
        .Fields.Append("ID", 3, 5, 32); adInteger, 5, adFldIsNullable
        .Fields.Append("Description", 200, 400, 32); adVarChar, 400, adFldIsNullable
        .Fields.Append("Type", 200, 40, 32)
        .Open()
    EndWith

3. load it with all the data for the ListView

With $adoRsItems
        .AddNew()
        .Fields(0).Value = 0; ID Field
        .Fields(1).Value = "Description-1"
        .Fields(2).Value = "Type-1"
,etc.

4. On Combo or other control change, Filter the Recordset and Load to ListView

Examples:

$adoRsItems.Filter = "ID = 0"
        $adoRsItems.Filter = "ID < 5"
        $adoRsItems.Filter = "Type = 'Application'"

Check:

If $adoRsItems.EOF() Then
        MsgBox(64, "Filter Result", "Filter Excluded All Items" & @CRLF & $adoRsItems.Filter)
        $adoRsItems.Filter = 0
       EndIf

Populating Listview:

_GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($lvw_Items)); Clear ListView
    $adoRsItems.MoveFirst()
    $J = $adoRsItems.Fields.Count() - 1
    While NOT $adoRsItems.EOF()
        For $I = 1 To $J
            If $I = 0 Then
               $K = _GUICtrlListView_AddItem($lvw_Items, $Txt, $K)
            Else
               _GUICtrlListView_SetItemText($lvw_Items, $K, $Txt, $I - 1)
            EndIf
        Next; $I
        $adoRsItems.MoveNext()
    Wend

AustrianOak · June 16, 2008

DaRam:

I'm not sure if I understand anything in your post.

2. Define columns
CODE
    With $adoRsItems
        .CursorLocation = 3; adUseClient
        .CursorType = 2; adOpenDynamic
        .LockType = 2; adLockPessimistic
        .Fields.Append("ID", 3, 5, 32); adInteger, 5, adFldIsNullable
        .Fields.Append("Description", 200, 400, 32); adVarChar, 400, adFldIsNullable
        .Fields.Append("Type", 200, 40, 32)
        .Open()
    EndWith

3. load it with all the data for the ListView
CODE
    With $adoRsItems
        .AddNew()
        .Fields(0).Value = 0; ID Field
        .Fields(1).Value = "Description-1"
        .Fields(2).Value = "Type-1"
,etc.
4. On Combo or other control change, Filter the Recordset and Load to ListView
Examples:
CODE
$adoRsItems.Filter = "ID = 0"
        $adoRsItems.Filter = "ID < 5"
        $adoRsItems.Filter = "Type = 'Application'"

Whats all that? I'm actually not familiar with ActiveX at all.

DaRam · June 16, 2008

DaRam:
I'm not sure if I understand anything in your post.

An Example is worth a thousand back and forths', so....

#include <GUIConstants.au3>
#Include <GuiListView.au3>

Global $GUI, $adoRsItems, $ListView1, $Button1, $Button2, $Button3, $Button4, $Button5, $Button6
Local $I, $J, $K, $Txt, $DataFile

$GUI = GUICreate("ADORS ListView Filtering", 633, 447, -1, -1)
$Button1 = GUICtrlCreateButton("All", 10, 10, 50, 50, 0)
$Button2 = GUICtrlCreateButton("< 21", 10, 70, 50, 50, 0)
$Button3 = GUICtrlCreateButton("> 20", 10, 130, 50, 50, 0)
$Button4 = GUICtrlCreateButton("Type-1", 10, 200, 50, 50, 0)
$Button5 = GUICtrlCreateButton("Type-2", 10, 260, 50, 50, 0)
$Button6 = GUICtrlCreateButton("Type-3", 10, 320, 50, 50, 0)

$ListView1 = GUICtrlCreateListView("ID|Description|Type", 72, 8, 553, 425)
$DataFile = StringReplace(@ScriptFullPath, ".au3", ".Xml")
$adoRsItems = ObjCreate( "ADODB.RecordSet" )
With $adoRsItems
    .CursorLocation = 3; adUseClient
    .CursorType = 2; adOpenDynamic
    .LockType = 2; adLockPessimistic
    If FileExists($DataFile) Then
       .Open($DataFile) ; Load From XML File
    Else ; Define Columns
      .Fields.Append("ID", 3, 5, 32); adInteger, 5, adFldIsNullable
      .Fields.Append("Description", 200, 400, 32); adVarChar, 400, adFldIsNullable
      .Fields.Append("Type", 200, 40, 32)
      .Open()
      ; Populate with Data
      For $I = 1 to 50
          .AddNew()
          .Fields(0).Value = $I
          $Txt = " "
          For $J = 0 to Random(10, 20, 1)
          If Random() < 0.5 Then
             $Txt = $Txt & Chr(Random(Asc("A"), Asc("Z"), 1))
          Else
             $Txt = $Txt & Chr(Random(Asc("a"), Asc("z"), 1))
          Endif
          Next ;$J
          .Fields(1).Value = "Description-" & $I & $Txt
          If $I < 30 Then
             .Fields(2).Value = "Type-1"
          Else
             .Fields(2).Value = "Type-2"
          EndIf
          .Update
      Next ; $I
      .Save($DataFile, 1) ; Save to XML File
    EndIf
EndWith
PopulateListView()
GUISetState(@SW_SHOW)

While 1
    $I = GUIGetMsg()
    Switch $I
    Case $GUI_EVENT_CLOSE
         ExitLoop
    Case $Button1
         $adoRsItems.Filter = 0
         PopulateListView()
    Case $Button2
         $adoRsItems.Filter = "ID<21"
         PopulateListView()
    Case $Button3
         $adoRsItems.Filter = "ID>20"
         PopulateListView()
    Case $Button4
         $adoRsItems.Filter = "Type='Type-1'"
         PopulateListView()
    Case $Button5
         $adoRsItems.Filter = "Type='Type-2'"
         PopulateListView()
    Case $Button6
         $adoRsItems.Filter = "Type='Type-3'"
         PopulateListView()
    EndSwitch
Wend

Func PopulateListView()
Local $I, $J, $K, $Txt
   ;Msgbox(0, "LV Populate", "Populating with " & $adoRsItems.RecordCount() & " Items", 5)
   _GUICtrlListView_DeleteAllItems(GUICtrlGetHandle($ListView1)); Clear ListView
   If $adoRsItems.EOF() Then
      MsgBox(64, "Filter Failed", "Filter Excluded All Items." & @CRLF & "Filter=" & $adoRsItems.Filter & @CRLF & @CRLF & "Reverting to All Items Display.", 5)
      $adoRsItems.Filter = 0
      GUICtrlSetState($I, $Button1)
   EndIf
   $adoRsItems.MoveFirst()
   $J = $adoRsItems.Fields.Count() - 1
   While NOT $adoRsItems.EOF()
     $K = $adoRsItems.Fields("ID").Value
     For $I = 0 To $J
         $Txt = $adoRsItems.Fields($I).Value
        If $I = 0 Then
           $K = _GUICtrlListView_AddItem($ListView1, $Txt, $K)
        Else
           _GUICtrlListView_SetItemText($ListView1, $K, $Txt, $I)
        EndIf
     Next; $I
     $adoRsItems.MoveNext()
  Wend
  ; Adjust ListView Column Widths
  $J = _GUICtrlListView_GetColumnCount ($ListView1)
  For $I = 0 To $J
    GUICtrlSendMsg($ListView1, $LVM_SETCOLUMNWIDTH, $I, -1)
    GUICtrlSendMsg($ListView1, $LVM_SETCOLUMNWIDTH, $I, -2)
  Next
EndFunc ; PopulateListView

AustrianOak · June 16, 2008

thank you, i will try it out.

SofaKing · July 3, 2008

Incredible code DaRam, helped get me out of a pretty big data input jam.

I have scoured the Interweb, this site and the help file but can't find additional information:

Is there any documentation about what syntax is used for the (ADODB.RecordSet).Filter command?

SofaKing · July 3, 2008

Incredible code DaRam, helped get me out of a pretty big data input jam.
I have scoured the Interweb, this site and the help file but can't find additional information:
Is there any documentation about what syntax is used for the (ADODB.RecordSet).Filter command?

Nevermind, more searching netted this gem from Microsoft:

Microsoft ADODB Filter Command

marko001 · June 15, 2011

Mainly to Zedna and DaRam:

I looked at this script and can do what exactly I need, that is filtering a LV.

To be honest me too have no knowledge of ADO syntax but..

I don't use as file a .xml but I used a .csv converted for my use and data are stored in this way::

First Name | Last Name | Address | Phone | Country

and this is the code I use now:

#include <Array.au3>
#include <file.au3>
#include <GuiConstantsEx.au3>
#include <GuiListView.au3>
#include <WindowsConstants.au3>
#include <WinAPI.au3>
#include <GuiImageList.au3>
#include <GuiEdit.au3>
#include <ButtonConstants.au3>

dim $aRecords
Global $main_Array[1][1]
$switch = False

If Not _FileReadToArray("data.csv",$aRecords) Then
   Dim $arecords[9] ; so I don't have errors but can create aswell the LV
EndIf


For $x = 1 to $aRecords[0]
    ReDim $main_Array[UBound($main_Array,1)+1][9]
    $avArray = StringSplit($aRecords[$x], "|")
    For $j = 1 To $avArray[0]
        $main_Array[$x][$j-1] =  $avArray[$j]
    Next
Next
$main_Array[0][0] = UBound($main_Array,1)-1
_ArrayDelete($main_Array,0)
_ArrayDelete($main_Array,0)

and then

$hGUI = GUICreate("ListView Get ISearch", 750, 550)
    $hListView = _GUICtrlListView_Create($hGUI, "", 2, 2, 655, 500, BitOR($LVS_EDITLABELS, $LVS_REPORT))
    _GUICtrlListView_SetExtendedListViewStyle($hListView, $LVS_EX_GRIDLINES)
    _GUICtrlListView_AddColumn($hListView, "First Name", 150,-1)
    _GUICtrlListView_AddColumn($hListView, "Last Name", 65,-1)
    _GUICtrlListView_AddColumn($hListView, "Address", 75,-1)
    _GUICtrlListView_AddColumn($hListView, "Phone", 50,-1)
    _GUICtrlListView_AddColumn($hListView, "Country", 50,-1)


    _GUICtrlListView_AddArray($hListView,$main_Array)

        guisetstate()

Is there a way to (more or less) exactly do what has been done for the previous posts?

I need to have, in same gui, several "filtering" buttons that can hide (not delete) the LV to show me just what I need.

Thanks a lot,

M.

Edited June 15, 2011 by marko001

Zedna · June 15, 2011

As I said look at my old example project

log_view.png

log_view.zip

marko001 · June 16, 2011

Unable to run it (Found old #Compiler Directives).

But I studied a lot from the example before and I found a working solution.

I'm using a .xml file (self-converted from a .csv)

Just need few hints:

a) [Edit: SOLVED ]My columns don't resize so lots of data are "cut" and i need to click column to open it. How can I set fixed column width?

.fields.append("Full Name"  ,200,150,32) ; 150 is not enough but I think it just set a fixed width ignoring that data

b ) "Text search filter" is possible? I mean can I have an input box where to put criteria and "on click" applying them?

c) "Multifilter" is possible? i.e. (Country & Town)

d) I'm using

GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUIRegisterMsg($WM_COMMAND, "WM_COMMAND")

to have cells editable. How can I save back to source file when I modify one/some/all datas?

e) [Edit: SOLVED]Given point d) is it possible to just have SOME columns editable? Now I can edit all datas but I just need 2 columns to be editable.

f) I saw in your image that you can sort clicking on columns, but could find the code inside your file (can't understand language ^^) How can it be done?

Thanks a lot for your help,

M.

Edited June 16, 2011 by marko001

Zedna · June 16, 2011

Just need few hints:
a) [Edit: SOLVED ]My columns don't resize so lots of data are "cut" and i need to click column to open it. How can I set fixed column width?
b ) "Text search filter" is possible? I mean can I have an input box where to put criteria and "on click" applying them?
c) "Multifilter" is possible? i.e. (Country & Town)
d) I'm using
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY")
GUIRegisterMsg($WM_COMMAND, "WM_COMMAND")
to have cells editable. How can I save back to source file when I modify one/some/all datas?
e) [Edit: SOLVED]Given point d) is it possible to just have SOME columns editable? Now I can edit all datas but I just need 2 columns to be editable.
f) I saw in your image that you can sort clicking on columns, but could find the code inside your file (can't understand language ^^) How can it be done?

a) GUICtrlSendMsg($ListView1, $LVM_SETCOLUMNWIDTH, 0, $LVSCW_AUTOSIZE)

b ), c ) it's there in log_view.au3

$ed_soubor = GUICtrlCreateInput("", 72, 81, 153, 21, BitOR($ES_AUTOHSCROLL,$WS_GROUP))
...
While 1
 $msg = GuiGetMsg()
  Select
     Case $msg = $GUI_EVENT_CLOSE
         Ukonceni()
         ExitLoop

    Case $msg = $cbx_uzivatel Or $msg = $cbx_akce Or $msg = $cbx_co Or $msg = $cbx_stav Or $msg = $cbx_ip Or _
          $msg = $ed_soubor  ; Or $msg = $dt_datum
         Filtruj()
...

Func Filtruj()
 GUISetCursor(15,1)
 ; prozatimni osetreni: odstraneni sipky trideni a priznaku
    SetHeaderIcon($ListView1, -1, -1)
 $nCurCol = -1
 $nSortDir = 1
 $nCol = -1
 $bSet = 0
 $Done = 0
 
 GuiSetState(@SW_LOCK, $Form1)
 _GUICtrlListViewDeleteAllItems($ListView1) ; smazat predchozi obsah listview
 
 $zadany_uzivatel = GUICtrlRead($cbx_uzivatel)
 $zadana_akce = GUICtrlRead($cbx_akce)
 $zadane_co = GUICtrlRead($cbx_co)
 $zadany_stav = GUICtrlRead($cbx_stav)
 $zadana_ip = GUICtrlRead($cbx_ip)
 $zadany_soubor = GUICtrlRead($ed_soubor)
;~     $zadane_datum = GUICtrlRead($dt_datum)
 
 ; z pole naplnit listview 
 $pocet = 0
 For $i = 1 To $data[0][0]
     ; u kazdeho radku zkontrolovat zda splnuje zadane podminky
     If Not ($zadany_uzivatel = 'Všichni' Or $zadany_uzivatel = $data[$i][1]) Then ContinueLoop
     If Not ($zadana_akce = 'Vše' Or $zadana_akce = $data[$i][3]) Then ContinueLoop
     If Not ($zadany_stav = 'Vše' Or $zadany_stav = $data[$i][2]) Then ContinueLoop
     If Not ($zadana_ip = 'Vše' Or $zadana_ip = $data[$i][4]) Then ContinueLoop
     If Not ($zadany_soubor = '' Or StringInStr($data[$i][5], $zadany_soubor) > 0 ) Then ContinueLoop
     If Not ($zadane_co = 'Vše' Or _
             ($zadane_co = 'DB' And _
                 (StringInStr($data[$i][5], '/DB/') > 0 Or _
                 StringInStr($data[$i][5], '/DB_7/') > 0 Or _
                 StringInStr($data[$i][5], '/DB_2000/') > 0)) Or _
             ($zadane_co = 'EXE' And StringInStr($data[$i][5], '/EXE/') > 0) Or _
             ($zadane_co = 'Jiné' And _
                 StringInStr($data[$i][5], '/DB/') = 0 And _
                 StringInStr($data[$i][5], '/DB_7/') = 0 And _
                 StringInStr($data[$i][5], '/DB_200/') = 0 And _
                 StringInStr($data[$i][5], '/EXE/') = 0)) Then ContinueLoop
;~         If Not ($zadane_datum = 'Vše' Or $zadane_datum = $data[$i][2]) Then ContinueLoop
      
     $pocet += 1
     $tmp = ''
     For $j = 0 To 7
         $tmp &= $data[$i][$j] & '|'
     Next
     $tmp = StringTrimRight($tmp,1)
     GUICtrlCreateListViewItem($tmp, $ListView1)

     If $ini_zobrazit_ikony = '1' Then
         ; pozn: v EXE uz jsou 3 default ikony AutoItu a pocitani je od 0
         Switch $data[$i][3]
             Case 'LOGIN'
                 If @compiled = 1 Then
                     GUICtrlSetImage(-1, @ScriptFullPath, 5)
                 Else
                     GUICtrlSetImage(-1, "icons\login.ico")
                 EndIf
             Case 'DOWNLOAD'
                 If @compiled = 1 Then
                     GUICtrlSetImage(-1, @ScriptFullPath, 6)
                 Else
                     GUICtrlSetImage(-1, "icons\download.ico")
                 EndIf
             Case 'UPLOAD'
                 If @compiled = 1 Then
                     GUICtrlSetImage(-1, @ScriptFullPath, 7)
                 Else
                     GUICtrlSetImage(-1, "icons\upload.ico")
                 EndIf
             Case 'MKDIR'
;~                     GUICtrlSetImage(-1, 'shell32.dll', 3)
                 If @compiled = 1 Then
                     GUICtrlSetImage(-1, @ScriptFullPath, 8)
                 Else
                     GUICtrlSetImage(-1, "icons\mkdir.ico")
                 EndIf
         EndSwitch 
     EndIf
 Next
  
 NastavSirkySloupcu() ; obnovit sirky sloupcu (automaticky si je totiz nastavuje dle dat)

 GuiCtrlSetData($status1,"Počet záznamů = " & $pocet & "/" & $data[0][0])
 GuiSetState(@SW_UNLOCK, $Form1)
 GUISetCursor(2)
EndFunc

f) It's there in log_view.au3

GUICtrlRegisterListViewSort($ListView1, "LVSort")

; funkce pro trideni kliknutim na zahlavi sloupcu (a zobrazeni sipky)

; function for sorting by column click (and show green arrow)
Func LVSort($hWnd, $nItem1, $nItem2, $nColumn)
    Local $nSort

 ; Switch the sorting direction
    If $nColumn = $nCurCol Then
        If Not $bSet Then
            $nSortDir = $nSortDir * -1
            $bSet = 1
        EndIf
    Else
        $nSortDir = 1
    EndIf
 
    If $Done = 0 AND $nSortDir = 1 Then
        SetHeaderIcon($hWnd, $nColumn, 0)
        $Done = 1
    ElseIf $Done = 0 AND $nSortDir = -1 Then
        SetHeaderIcon($hWnd, $nColumn, 1)
        $Done = 1
    Endif

    $nCol = $nColumn

    $val1   = GetSubItemText($ListView1 , $nItem1, $nColumn)
    $val2   = GetSubItemText($ListView1 , $nItem2, $nColumn)

 ; osetreni nestandardnich sloupcu
 If $nColumn = 0 Then ; Datum a cas
        $val1 = StringMid($val1, 7, 4) & StringMid($val1, 4, 2) & StringLeft($val1, 2) & StringMid($val1, 12)
        $val2 = StringMid($val2, 7, 4) & StringMid($val2, 4, 2) & StringLeft($val2, 2) & StringMid($val2, 12)
 ElseIf $nColumn = 6 Then ; Velikost
        $val1 = Number($val1)
        $val2 = Number($val2)
 ElseIf $nColumn = 7 Then ; Rychlost
        $val1 = Number($val1)
        $val2 = Number($val2)
    EndIf

    $nResult = 0       ; No change of item1 and item2 positions

    If $val1 < $val2 Then
        $nResult = -1  ; Put item2 before item1
    ElseIf  $val1 > $val2 Then
        $nResult = 1   ; Put item2 behind item1
    EndIf
    $nResult = $nResult * $nSortDir

    Return $nResult
EndFunc

Edited June 16, 2011 by Zedna

marko001 · June 16, 2011

Almost all solved:

A) Done easily

b-c) [EDIT: half] Done, long "if" cycle but done ^^

e) done (just looking at the code)

f) Looking now, seems not hard

Still need help on d) Saving back and overwrite file so it's ready for next reload

And last, the I can post the whole code, is it possible to JUST color some columns? Due to e) I blocked editing in columns where editing it's not necessary but I'd like to give more visibility to other columns

Thanks for your great patience,

[Edit] For c) Among other filters (I used checkboxes) , and they work fine, I added a combobox to cut the filter based on value on combobox.

Is there the possibility (easy) to "filter" a filter". I mean:

I have

$adoRsItems.Filter = ("Married = 'No' AND [Lives alone] = 'Y'") OR ("Married = 'No' AND [Lives alone] = 'n/a'"

I want to add another "general" filter AFTER this but before reloading the LV (and without using same commandline)

$adoRsItems.Filter = "Country = '" & guictrlread($combobox) & "'"

Is it possible?

Marco

Edited June 16, 2011 by marko001

Zedna · June 16, 2011

And last, the I can post the whole code, is it possible to JUST color some columns? Due to e) I blocked editing in columns where editing it's not necessary but I'd like to give more visibility to other columns

Yes. It's possible but it's not 1 line of code as for coloring of whole line. There are more examples on the forum:

http://www.autoitscript.com/forum/topic/71681-change-the-font-of-a-single-listview-item/page__p__524639#entry524639

Edited June 16, 2011 by Zedna

filtering a listview

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members