Skip to content

Drilldown

The drilldown library is a very useful piece of code for dynamically searching through large databases. Output can be filtered and custom display options used to optimise it for your program.

  1. A custom type
  2. Initialising the application
  3. Drilldown
    a. Using drilldown to select a record
    b. Finishing up
  4. Download
  5. Source code

A custom type

The project we will be creating is a very simple one, it will search through the CUSTOMER database that comes with your SIMPOL installation to display information about the record you select.

Warning

This program will make use of a custom type, so is of medium difficulty. If you want to just skip to the drilldown declaration click here

We are using a custom type here so we can hold a reference to our database table. If this is the first time you’ve come across a custom type let me introduce you to a key tool for more advanced programs, it may be worth having a quick look through our guide on them (which can be found here). In this case it may not be entirely necessary but it gives us a brief introduction to the use of such custom types.

type drillapp (application)
  reference
  application __app resolve
  type(db1table) customer
end type

Initialising the application

As this tutorial does not cover custom types we will now only make reference to it, the only thing we still have to create will be our custom exit function, which you can see below

function exit(type(application) me, wxwindow w)
   string response
   appwindow appw
   boolean closeapp
   response = ""
   wxmessagedialog(w, sEXITAPPQUESTION, sAPPMSGTITLE, \
                   style="okcancel", icon="question", result=response)
   if response != "ok"
     closeapp = .false
   else
     closeapp = .true
     appw =@ getappwindowfromwindow(w)
     if appw !@= .nul
       closeapp = checkneedsave(appw)
     end if
     if closeapp
       me.running = .false
       wxbreak()
     end if
   end if
 end function closeapp

This isn’t an overly complicated function but it does use some interesting tricks I feel are worth highlighting: Using the result variable from wxmessagedialog to determine what the user wants; Getting the appwindow from a wxwindow, and finally checking if the form needs to be saved using checkneedsave are all invaluable tools and even if in our case some aren’t relevant they will often come in handy for more complicated programs).

Once we have determined we truly want to close the window, we then set running to .false and stops the execution using wxbreak()

Drilldown

Now that we’ve done the necessary prep work we can start working on the first form. I won’t include here how it was made as it is a very simple form, all you need to know is that the buttons on it are called bOK and bFind, and the relevant onclick functions are findcustomer and ok_oc

The second of these is the simpler of the two so we will start with that one

function ok_oc(dataform1button me, appwindow appw)
  exit(appw.app,appw.w)
end function

It simply performs an exit procedure, so we can close the form normally. The findcustomer function is vastly more interesting and the actual point of this tutorial. Because we will be using drilldown as a selector there will be some optional code here that isn’t necessarily required for a drilldown call

function findcustomer(dataform1button me, appwindow appw)
   type(db1record) r
   type(db1index) idx
   drillapp app
   tdisplayformats dispfmt
   array dispflds, colwidths
   integer e

The key things to note here are dispfmt, and the two arrays dispflds and colwidths

app =@ appw.app
dispfmt =@ app.displayformats
idx =@ app.customer!FamilyName.index

dispfmt is the displayformat of our application, these can be changed upon initialising, app is our application type, and idx is the index we will be using to search through the database, this index need to be a string datatype!

We next need to put all the fields we want to display and their widths into arrays for our example that is done as follows:

dispflds =@ array.new()
   dispflds[1] =@ app.customer!CustSerNo  
   dispflds[2] =@ app.customer!Honorific
   dispflds[3] =@ app.customer!FirstNames
   dispflds[4] =@ app.customer!FamilyName
   dispflds[5] =@ app.customer!PostCode 

 colwidths =@ array.new()
   colwidths[1] = 65
   colwidths[2] = 30
   colwidths[3] = 150
   colwidths[4] = 120
   colwidths[5] = 70

That’s all of the preparation done we can now get into the meat of our drilldown. (Warning: wall of text)

r =@ drilldown(me.form.container,540,485, idx,maxentries=20,minactivationchars=1,"Select a customer","Customer list (by Surname)", dispflds = dispflds, colwidths = colwidths, defboolean = dispfmt.defboolean, definteger=dispfmt.definteger, defnumber=dispfmt.defnumber, defdate=dispfmt.defdate, deftime=dispfmt.deftime, defdatetime=dispfmt.defdatetime, caseinsensitive=.true, datelocale=app.SBLlocale.datelocale, numlocale=app.SBLlocale.numlocale, error=e)

This is a more or less standard drilldown, width, height as well as the maximum number of entries and the minimum number of characters required to activate are the only really variable variables. The two strings are the title and search box text. There is a tonne of customisation that can be done here however. Check out the Language Reference Manual for the full list of options, optionally there is also a tutorials on tablesearch also available (here)

Using drilldown as a selector (Optional)

The following code changes the forms record to the one we just selected using drilldown

if r !@= .nul
     dataform1 f
     type(db1index) searchidx
     searchidx =@ app.customer!CustSerNo.index
     f =@ appw.form
     f.selectkey(r!CustSerNo,searchidx,error=e) 
   end if

Finishing the Project

Now we’ve set up the drilldown the last thing we need to do is create our main function, this is a very simple one

cd = getcurrentdirectory()
formfilename = cd + getdirectorysepchar() + 'drilldownmain.sxf'
e = 0
app =@ drillapp.new()
if app !@= .nul
  appw =@ app.windows.getfirst()
  e = 0

  if not fileexists(formfilename)
    wxmessagedialog(appw.w, "Form file 'drilldownmain.sxf' not found", sAPPMSGTITLE,icon="error")
  else
    appw.openformdirect(formfilename,sAPPMSGTITLE)
    if appw.form !@= .nul
      prepmainform(appw)
      appw.resizewindowtoform()

      lt =@ point.new(0,0)
      br =@ point.new(0,0)
      getcenteredwindowrect(appw.w.outerwidth, appw.w.outerheight, lt, br, error=e)
      if e == 0
        appw.w.setposition(lt.x, lt.y)
      end if
      appw.setcurrentpath(cd)
      appw.w.setstate(visible=.true)
      !beginthread(deferprocessing, startkludge, appw, appw.form)
      app.run()
    end if
  end if
  app.exit()
end if

function prepmainform(appwindow appw)
   dataform1 form
 form =@ appw.form
   form!bOK.onclick.reference =@ appw
   form!bFind.onclick.reference =@ appw
 end function

It finds the current directory, and form file, initialises the application, opens the form, prepares the main form, resizes and centres it before running it

Downloads

drilldownexampleDownload

Source Code

constant sAPPTITLE            "Customer Details"
constant sAPPMSGTITLE         "SIMPOL Drilldown Error"
constant sEXITAPPQUESTION     "Close SIMPOL Drilldown?"

include "errors.sma"

function main()
  drillapp app
  appwindow appw
  string formfilename,cd;// cd
  integer e
  point lt, br
  
  cd = getcurrentdirectory()
  formfilename = cd + getdirectorysepchar() + 'drilldownmain.sxf'
  e = 0
  app =@ drillapp.new()
  if app !@= .nul
    appw =@ app.windows.getfirst()
    e = 0

    if not fileexists(formfilename)
      wxmessagedialog(appw.w, "Form file 'drilldownmain.sxf' not found", sAPPMSGTITLE,icon="error")
    else
      appw.openformdirect(formfilename,sAPPMSGTITLE)
      if appw.form !@= .nul
        prepmainform(appw)
        appw.resizewindowtoform()

        lt =@ point.new(0,0)
        br =@ point.new(0,0)
        getcenteredwindowrect(appw.w.outerwidth, appw.w.outerheight, lt, br, error=e)
        if e == 0
          appw.w.setposition(lt.x, lt.y)
        end if
        appw.setcurrentpath(cd)
        appw.w.setstate(visible=.true)
        !beginthread(deferprocessing, startkludge, appw, appw.form)
        app.run()
      end if
    end if
    app.exit()
  end if
end function

function startkludge(appwindow appw, dataform1 form, boolean dodefer)
  selectrecord(appw, "selectcurrent", silent=.true)
end function

function exit(type(application) me, wxwindow w)
  string response
  appwindow appw
  boolean closeapp

  response = ""
  wxmessagedialog(w, sEXITAPPQUESTION, sAPPMSGTITLE, \
                  style="okcancel", icon="question", result=response)
  if response != "ok"
    closeapp = .false
  else
    closeapp = .true
    appw =@ getappwindowfromwindow(w)
    if appw !@= .nul
      closeapp = checkneedsave(appw)
    end if
    if closeapp
      me.running = .false
      wxbreak()
    end if
  end if
end function closeapp

function prepmainform(appwindow appw)
  dataform1 form

  form =@ appw.form
  form!bOK.onclick.reference =@ appw
  form!bFind.onclick.reference =@ appw
end function

function findcustomer(dataform1button me, appwindow appw)
  type(db1record) r
  type(db1index) idx
  drillapp app
  tdisplayformats dispfmt
  array dispflds, colwidths
  integer e

  app =@ appw.app
  dispfmt =@ app.displayformats
  idx =@ app.customer!FamilyName.index;//Needs to be a string for indexing
  dispflds =@ array.new()
  dispflds[1] =@ app.customer!CustSerNo  
  dispflds[2] =@ app.customer!Honorific
  dispflds[3] =@ app.customer!FirstNames
  dispflds[4] =@ app.customer!FamilyName
  dispflds[5] =@ app.customer!PostCode 

  colwidths =@ array.new()
  colwidths[1] = 65
  colwidths[2] = 30
  colwidths[3] = 150
  colwidths[4] = 120
  colwidths[5] = 70

  e = 0
  r =@ drilldown(me.form.container,540,485, idx,maxentries=20,minactivationchars=1,"Select a customer","Customer list (by Surname)", \
                 dispflds = dispflds, colwidths = colwidths, defboolean = dispfmt.defboolean, \
                 definteger=dispfmt.definteger, defnumber=dispfmt.defnumber, defdate=dispfmt.defdate, \
                 deftime=dispfmt.deftime, defdatetime=dispfmt.defdatetime, caseinsensitive=.true, \
                 datelocale=app.SBLlocale.datelocale, numlocale=app.SBLlocale.numlocale, error=e)
    if r !@= .nul
      dataform1 f
      type(db1index) searchidx
      searchidx =@ app.customer!CustSerNo.index
      f =@ appw.form
      f.selectkey(r!CustSerNo,searchidx,error=e) 
    end if
end function

function ok_oc(dataform1button me, appwindow appw)
  exit(appw.app,appw.w)
end function

type drillapp (application)
  reference 
  application __app resolve
  type(db1table) customer
end type

function drillapp.new(drillapp me)
  appwindow appw
  datasourceinfo src
  type(db1table) t
  integer e
  boolean ok
  string file
  
  ok = .false
  e = 0
  
  me.__app =@ application.new(appiconfile="", iconimagetype="", inifilename="", apptitle =sAPPTITLE)
  me.__app.__ =@ me
  me.onexitrequest.function =@ exit
  me.running = .true
  appw =@ appwindow.new(me,visible=.false,mb=.nul,tb=.nul,sb=.nul,border="simple",maxbutton=.false)
  if appw =@= .nul
    wxmessagedialog(appw.w, "Error creating window", sAPPMSGTITLE,icon="error")
  else
    file = getpublicdatadir() + "\SIMPOL Business\customer.sbm"; //this is where the database file will be found
    src =@ me.opendatasource("sbme1", file, appw, error=e)
    if src =@= .nul
      wxmessagedialog(appw.w,"Error opening customer.sbm file",sAPPMSGTITLE,icon="error")
    else
      t =@ appw.opendatatable(src,"CUSTOMER",error=e)
      if t =@= .nul
        wxmessagedialog(appw.w,"Error opening the 'Customer' table", sAPPMSGTITLE,icon="error")
      else
        me.customer =@ t
        ok =.true
      end if
    end if
  end if

  if not ok
    me =@ .nul
  end if
end function me

Leave a Reply