Wednesday, August 29, 2012

ListBox Trick # 1 - Files with Filtering

This again is based on a query inside Foxite. The question goes:

I need to get tables only have or start names as month1,month2.....

While GetFile() is yet another easy way to get a fullpath(), it is not flexible on the filename part; only on the extension part where you can use wild characters.  You cannot do this:


As the asterisk there will be construed by VFP as part of the filename, and not as a wildcard.  Same goes with LOCFILE() and  PUTFILE()  where you cannot also use a wild character  on the filename.

Ordinarilly, the immediate thinking goes to these steps:

  • Use ADIR() to gather filenames starting with the word "Month" and with extension "dbf": 
  • Create a cursor to hold the result and perform INSERT INTO .. FROM ARRAY laMyFiles
  • Create a grid to show the result, then put some more codes either on AfterRowColChange or Click events to transfer the result into the textbox

However, while that is one of the best ways to do those,  there is yet simpler way which is using the highly unutilized feature of listbox known as RowSourceType = 7.  It involves less coding and actually is very easy to use.  For the sake of this example, I will simply filter the list of files with all dbf:

loTest = Createobject("frmListBox")


Define Class frmListBox As Form
      AutoCenter = .T.
      Height = 300
      Width = 420

      Add Object List1 As ListBox With ;
            top = 50,;
            left = 10,;
            Width = 400,;
            Height = 200,;
            RowSourceType = 7,;
            RowSource = '*.dbf'  && This is how we can filter

      Add Object label1 As Label With ;
            top = 10,;
            left = 10,;
            Caption = 'Selected',;
            AutoSize = .T.

      Add Object text1 As TextBox With;
            top = 10,;
            left = 80,;
            width = 300

      Procedure List1.InteractiveChange
            Thisform.text1.Value = Fullpath(This.Value)


And there you have it.  I actually used the same trick with one of my modules here which is linking scanned documents to specific employees.  Access to open/view those was given only to specific Managers (Admin and Payroll) and is simply done again via ShellExecute().  The box where the documents are is a listbox with RowSourceType = 7.

I am not saying that this is the best way to do it.  In reality I got lazy.  The point of my tutorials is to show some ways to achieve things or for you to familiarize yourselves with some unused features of an object.  Cheers!


Glad that Paul Gibson informed me of this because I totally forgot about this one.  There is what I deem a bug in using FULLPATH() with .RowSourceType = 7 which is while the filename portion is getting the accurate value based on list items, the path portion never gets changed and remains to be where you originally are.  To counter that problem, you can simply get the value of  item # 2 then combine it with the value of the currently selected item. On InteractiveChange event, replace it with this:

Procedure List1.InteractiveChange
   LOCAL lcPath 
   lcPath = This.ListItem(2) && to get the path portion
   Thisform.text1.Value = m.lcPath + This.Value

and that will fix that 'bug'.  Cheers!