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

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