XBrowse with Add,Modify,Delete methods (13.07)

[nageswaragunupudi]

If you are having FWH 13.07, I suggest you please try this sample.

Normal table maintenance requires browing with support for Add, Modify and delete with appropriate dialogs. The purpose of this posting is to introduce the new methods Edit() and Delete() of XBrowse provding an extermely easy way to support Add,Edit,Delete support.

At first glance you may consider this functionality to be just ornamental and not userful for serious programming, I request you to run these samples and reconsider. These methods, tightly integrated with TDataRow class, not only greatly simplify the job but also enables you to write fully portable code.

By "portable code", I mean you write browse and edit screen only once, whatever be the datasource, be it DBF, Array, ADO RecordSet, TDataBase. ( We are yet to extend to TDolphin)

That means, you may deliver your software to some clients with DBF, some with MySql,/ Access. etc., you still write single code and just change the data pluggin.

First sample is a familiar xbrowse of customer table. The table can be customer.dbf or customer table in xbrtest.mdb in the fwh\samples folder.

Functionality of Add, Edit and delete are provided by just adding only 3 lines of code:

Code: Select all  Expand view  RUN

   @ 10, 10 BUTTON "Edit"   SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit()
   @ 10, 60 BUTTON "Append" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit( .t. )
   @ 10,110 BUTTON "Delete" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Delete()
 

Please complile and run this sample.
( Note: This sample assumes the data to be in c:\fwh\samples folder. If in your case it is in a different folder, please change the value of static variable cFolder. )

At the outset you will get an option to choose DBF, Array, TDataBase or ADO.

Please check Add, Edit, Delete functionality for all the data sources.

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "xbrowse.ch"
#include "adodef.ch"

REQUEST DBFCDX

static aHead
static cFolder := "c:\fwh\samples\\"
//----------------------------------------------------------------------------//

function Main()

   local cust_table

   cust_table := OpenCust()
   BrowseTable( cust_table )
   CloseTable ( cust_table )

return nil

//----------------------------------------------------------------------------//
// TESTING XBROWSE BUILTIN ADD/EDIT/DELETE
//----------------------------------------------------------------------------//

static function BrowseTable( uData )

   local oDlg, oBrw, oFont, oRec

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE GetSysMetrics(0) * 0.9,400 PIXEL FONT oFont ;
      TITLE "XBROWSE/ADD/EDIT/DELETE : " + FWVERSION

   @ 30,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg DATASOURCE uData ;
      AUTOCOLS AUTOSORT FOOTERS CELL LINES NOBORDER

   WITH OBJECT oBrw
      if ValType( uData ) == 'A'
         :cHeaders   := aHead
      endif
      :nEditTypes    := EDIT_GET
      :Married:SetCheck()
      :CreateFromCode()
   END

   @ 10, 10 BUTTON "Edit"   SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit()
   @ 10, 60 BUTTON "Append" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit( .t. )
   @ 10,110 BUTTON "Delete" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Delete()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function OpenCust()

   local nChoice, cust_table

   nChoice := Alert( "Choose DataSource", { "DBF", "ARRAY", "TDATABASE", "ADO", "Quit" }, "DATASOURCE" )
   if nChoice  <= 3
      USE ( cFolder + "CUSTOMER" ) NEW ALIAS CUST SHARED VIA "DBFCDX"
      if nChoice == 1
         cust_table    := Alias()
      elseif nChoice == 2
         cust_table    := FW_DbfToArray()
         aHead          := ArrTranspose( DbStruct() )[ 1 ]
         DBCLOSEAREA()
      else
         DATABASE cust_table
      endif
   elseif nChoice == 4
      cust_table := FW_OpenRecordSet( cFolder + "xbrtest.mdb", "CUSTOMER" )
   else
      QUIT
   endif

return cust_table

//----------------------------------------------------------------------------//

static function CloseTable( cust_table )

   if ValType( cust_table ) == 'C'
      ( cust_table )->( DbCloseArea() )
   elseif ValType( cust_table ) == 'O'
      cust_table:Close()
      if cust_table:ClassName() == "TOLEAUTO"
         cust_table:ActiveConnection:Close()
      endif
   endif

return nil

//----------------------------------------------------------------------------//

init procedure PrgInit

   SET DATE ITALIAN
   SET CENTURY ON
   SET DELETED ON
   SET EXCLUSIVE OFF
   XbrNumFormat( 'A', .t. )
   SetKinetic( .f. )
   SetGetColorFocus()

return
 

The edit dialogs we see here are default dialogs provided by TDataRow class. This default dialog is inteneded to be used during development stage. Definitely one would like to design a better looking dialog for final release.

Now how we integrate our custom designed dialog with this feature.
Again, this is very simple.

This is a sample of custom edit function I have made. This function receives oRec as parameter. oRec is an instance of TDataRow class. If oRec:RecNo == 0, it is a new record else it is an existing record. We build our own dialog to GET values of oRec:data vars and finally call oRec:Save() to save data.

Code: Select all  Expand view  RUN

static function CustEditDlg( oRec )

   local oDlg, oFont, oGrp

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 618,382 PIXEL FONT oFont TITLE "CUSTOMER"

   @ 0, 4 GROUP oGrp TO 168, 305 OF oDlg PIXEL

   @ 12, 10 SAY "First:" OF oDlg SIZE 15, 8 PIXEL
   @ 10, 44 GET oRec:First    OF oDlg SIZE 105, 12 PIXEL UPDATE
   if oRec:FieldPos( "ID" ) > 0
      @ 12, 200 SAY If( oRec:RecNo == 0, "NEW RECORD", "ID " + cValToChar( oRec:ID ) ) SIZE 80,8 PIXEL UPDATE
   else
      @ 12, 200 SAY If( oRec:RecNo == 0, "NEW RECORD", "RecNo " + cValToChar( oRec:RecNo ) ) SIZE 80,8 PIXEL UPDATE
   endif
   @ 26, 10 SAY "Last:" OF oDlg SIZE 15, 8 PIXEL
   @ 24, 44 GET oRec:Last     OF oDlg SIZE 105, 12 PIXEL UPDATE
   @ 40, 10 SAY "Street:" OF oDlg SIZE 21, 8 PIXEL
   @ 38, 44 GET oRec:Street   OF oDlg SIZE 155, 12 PIXEL UPDATE
   @ 54, 10 SAY "City:" OF oDlg SIZE 13, 8 PIXEL
   @ 52, 44 GET oRec:City     OF oDlg SIZE 155, 12 PIXEL UPDATE
   @ 68, 10 SAY "State:" OF oDlg SIZE 19, 8 PIXEL
   @ 66, 44 GET oRec:State    OF oDlg SIZE 15, 12 PIXEL UPDATE
   @ 82, 10 SAY "Zip:" OF oDlg SIZE 12, 8 PIXEL
   @ 80, 44 GET oRec:Zip      OF oDlg SIZE 55, 12 PIXEL UPDATE
   @ 96, 10 SAY "Hiredate:" OF oDlg SIZE 29, 8 PIXEL
   @ 94, 44 GET oRec:Hiredate OF oDlg SIZE 44, 12 PIXEL UPDATE
   @ 108, 44 CHECKBOX oRec:Married  PROMPT "&Married:" OF oDlg SIZE 43, 12 PIXEL UPDATE
   @ 124, 10 SAY "Age:" OF oDlg SIZE 15, 8 PIXEL
   @ 122, 44 GET oRec:Age      OF oDlg SIZE 12, 12 PIXEL UPDATE PICTURE  "99"
   @ 138, 10 SAY "Salary:" OF oDlg SIZE 21, 8 PIXEL
   @ 136, 44 GET oRec:Salary   OF oDlg SIZE 40, 12 PIXEL UPDATE PICTURE  "999999.99"
   @ 152, 10 SAY "Notes:" OF oDlg SIZE 21, 8 PIXEL
   @ 150, 44 GET oRec:Notes    OF oDlg SIZE 255, 12 PIXEL UPDATE
   @ 172, 175 BUTTON "&Undo" OF oDlg SIZE 42, 14 PIXEL WHEN oRec:Modified() ACTION ( oRec:UnDo(), oDlg:Update() )
   @ 172, 219 BUTTON "&Save" OF oDlg SIZE 42, 14 PIXEL WHEN oRec:Modified() ACTION ( oRec:Save(), oDlg:Update() )
   @ 172, 263 BUTTON "&Close" OF oDlg SIZE 42, 14 PIXEL CANCEL ACTION ;
      ( If( oRec:Modified() .and. MsgYesNo( "Save Changes ?" ), oRec:Save(), nil ), oDlg:End() )

   AEval( oDlg:aControls, { |o| If( o:ClassName == "TGET", o:bValid := { || oDlg:AEvalWhen(), .t. }, nil ) } )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil
 

We now attach this function to the xbrowse system by this one line code:

Code: Select all  Expand view  RUN

oBrw:bEdit         := { |oRec| CustEditDlg( oRec ) }
 

Even at the cost of repetition, I give here the full sample incorporating our custom dialog in to the browse/add/edit system, to make it easy to copy and paste the full program.

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "xbrowse.ch"
#include "adodef.ch"

REQUEST DBFCDX

static aHead
static cFolder := "c:\fwh\samples\\"

//----------------------------------------------------------------------------//

function Main()

   local cust_table

   cust_table := OpenCust()
   BrowseTable( cust_table )
   CloseTable ( cust_table )

return nil

//----------------------------------------------------------------------------//
// TESTING XBROWSE BUILTIN ADD/EDIT/DELETE
//----------------------------------------------------------------------------//

static function BrowseTable( uData )

   local oDlg, oBrw, oFont, oRec

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE GetSysMetrics(0) * 0.9,400 PIXEL FONT oFont ;
      TITLE "XBROWSE/ADD/EDIT/DELETE : " + FWVERSION

   @ 30,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg DATASOURCE uData ;
      AUTOCOLS AUTOSORT FOOTERS CELL LINES NOBORDER

   WITH OBJECT oBrw
      :bEdit         := { |oRec| CustEditDlg( oRec ) }
      if ValType( uData ) == 'A'
         :cHeaders   := aHead
      endif
      :nEditTypes    := EDIT_GET
      :Married:SetCheck()
      :CreateFromCode()
   END

   @ 10, 10 BUTTON "Edit"   SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit()
   @ 10, 60 BUTTON "Append" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Edit( .t. )
   @ 10,110 BUTTON "Delete" SIZE 40,12 PIXEL OF oDlg ACTION oBrw:Delete()

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

//----------------------------------------------------------------------------//

static function OpenCust()

   local nChoice, cust_table

   nChoice := Alert( "Choose DataSource", { "DBF", "ARRAY", "TDATABASE", "ADO", "Quit" }, "DATASOURCE" )
   if nChoice  <= 3
      USE ( cFolder + "CUSTOMER" ) NEW ALIAS CUST SHARED VIA "DBFCDX"
      if nChoice == 1
         cust_table    := Alias()
      elseif nChoice == 2
         cust_table    := FW_DbfToArray()
         aHead          := ArrTranspose( DbStruct() )[ 1 ]
         DBCLOSEAREA()
      else
         DATABASE cust_table
      endif
   elseif nChoice == 4
      cust_table := FW_OpenRecordSet( cFolder + "xbrtest.mdb", "CUSTOMER" )
   else
      QUIT
   endif

return cust_table

//----------------------------------------------------------------------------//

static function CloseTable( cust_table )

   if ValType( cust_table ) == 'C'
      ( cust_table )->( DbCloseArea() )
   elseif ValType( cust_table ) == 'O'
      cust_table:Close()
      if cust_table:ClassName() == "TOLEAUTO"
         cust_table:ActiveConnection:Close()
      endif
   endif

return nil

//----------------------------------------------------------------------------//

init procedure PrgInit

   SET DATE ITALIAN
   SET CENTURY ON
   SET DELETED ON
   SET EXCLUSIVE OFF
   XbrNumFormat( 'A', .t. )
   SetKinetic( .f. )
   SetGetColorFocus()

return

//----------------------------------------------------------------------------//

static function CustEditDlg( oRec )

   local oDlg, oFont, oGrp

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 618,382 PIXEL FONT oFont TITLE "CUSTOMER"

   @ 0, 4 GROUP oGrp TO 168, 305 OF oDlg PIXEL

   @ 12, 10 SAY "First:" OF oDlg SIZE 15, 8 PIXEL
   @ 10, 44 GET oRec:First    OF oDlg SIZE 105, 12 PIXEL UPDATE
   if oRec:FieldPos( "ID" ) > 0
      @ 12, 200 SAY If( oRec:RecNo == 0, "NEW RECORD", "ID " + cValToChar( oRec:ID ) ) SIZE 80,8 PIXEL UPDATE
   else
      @ 12, 200 SAY If( oRec:RecNo == 0, "NEW RECORD", "RecNo " + cValToChar( oRec:RecNo ) ) SIZE 80,8 PIXEL UPDATE
   endif
   @ 26, 10 SAY "Last:" OF oDlg SIZE 15, 8 PIXEL
   @ 24, 44 GET oRec:Last     OF oDlg SIZE 105, 12 PIXEL UPDATE
   @ 40, 10 SAY "Street:" OF oDlg SIZE 21, 8 PIXEL
   @ 38, 44 GET oRec:Street   OF oDlg SIZE 155, 12 PIXEL UPDATE
   @ 54, 10 SAY "City:" OF oDlg SIZE 13, 8 PIXEL
   @ 52, 44 GET oRec:City     OF oDlg SIZE 155, 12 PIXEL UPDATE
   @ 68, 10 SAY "State:" OF oDlg SIZE 19, 8 PIXEL
   @ 66, 44 GET oRec:State    OF oDlg SIZE 15, 12 PIXEL UPDATE
   @ 82, 10 SAY "Zip:" OF oDlg SIZE 12, 8 PIXEL
   @ 80, 44 GET oRec:Zip      OF oDlg SIZE 55, 12 PIXEL UPDATE
   @ 96, 10 SAY "Hiredate:" OF oDlg SIZE 29, 8 PIXEL
   @ 94, 44 GET oRec:Hiredate OF oDlg SIZE 44, 12 PIXEL UPDATE
   @ 108, 44 CHECKBOX oRec:Married  PROMPT "&Married:" OF oDlg SIZE 43, 12 PIXEL UPDATE
   @ 124, 10 SAY "Age:" OF oDlg SIZE 15, 8 PIXEL
   @ 122, 44 GET oRec:Age      OF oDlg SIZE 12, 12 PIXEL UPDATE PICTURE  "99"
   @ 138, 10 SAY "Salary:" OF oDlg SIZE 21, 8 PIXEL
   @ 136, 44 GET oRec:Salary   OF oDlg SIZE 40, 12 PIXEL UPDATE PICTURE  "999999.99"
   @ 152, 10 SAY "Notes:" OF oDlg SIZE 21, 8 PIXEL
   @ 150, 44 GET oRec:Notes    OF oDlg SIZE 255, 12 PIXEL UPDATE
   @ 172, 175 BUTTON "&Undo" OF oDlg SIZE 42, 14 PIXEL WHEN oRec:Modified() ACTION ( oRec:UnDo(), oDlg:Update() )
   @ 172, 219 BUTTON "&Save" OF oDlg SIZE 42, 14 PIXEL WHEN oRec:Modified() ACTION ( oRec:Save(), oDlg:Update() )
   @ 172, 263 BUTTON "&Close" OF oDlg SIZE 42, 14 PIXEL CANCEL ACTION ;
      ( If( oRec:Modified() .and. MsgYesNo( "Save Changes ?" ), oRec:Save(), nil ), oDlg:End() )

   AEval( oDlg:aControls, { |o| If( o:ClassName == "TGET", o:bValid := { || oDlg:AEvalWhen(), .t. }, nil ) } )

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil
 

Now our custom dialog is at work.


Having seen all this, let us see how are we benefited by these new features provided by the combination of txbrowse and tdatarow classes.

We are already aware that we can write common xbrowse code, in most casese, irrespective of the datasource. Particularly for single table maintenance, it is not necessary to write one module for dbf and another for ADO, if the source structures are identical. But we still need to write separate code for edit/add/delete and that is really a time consuming work. Now even this part is greatly simplified and we can write common code (mostly) irrespective of the datasource.

Delete:
In the absence of oBrw:Delete() method, we need to write code for deleting the record, repositioning the recordpointer to the next visible record and refresh browse without any runtime errors. Now just a call to oBrw:Delete() takes care of all. It may look simple, but you can find any postings seeking advice on these issues.

Edit:
All of us are used to implement some kind of scatter/gather systems to edit the field values in dialogs. Obviously these methods differ for different datasources. Now we have TDataRow class which does this job. This class takes care of reading field vars and saving them in the correct record automatially. oRec:Save() saves the data whether it is dbf, ado, etc. The class also knows how to handle errors avoiding ugly runtime errros in the final application. Our work is limited to handling the edit dialog and validations, whatever be the datasource.

Add:
Same way as edit. The data is appended the table and record pointer is moved to the new record. Depending on the datasource, the class uses dbappend() or oRs:New() or AAdd(),etc.

The above sample is an example of single table maintenance independant of the nature of datasource.

[elvira]

Mr. Rao,

Very very good .

One question, in oBrw:Delete() the MsgYesNo is translated using FW_Strings?.

Not only cMessage, but also cTitle for MsgYesNo?.

Thanks.

[nageswaragunupudi]

One question, in oBrw:Delete() the MsgYesNo is translated using FW_Strings?.

Not only cMessage, but also cTitle for MsgYesNo?.

oBrw:Delete() is a core function which does the job. This has no inbuilt message.

I suggest the programmer to implement a yesno message and call this method when appropriate.

Reason:
In my view core functions should avoid as much as possible messages to the "user" (or any direct communication to the user), bypassing the "programmer". Libraries generating messages should be limited to run-time errors and other exceptional situations. Programmer should have the liberty to decide what to communicate to "his" user and when.

For example, in this case. A programmer may need to delete a batch of records (selected by bookmarks). He may like to caution the user with one message to the user for the batch operation and proceed to delete the batch.
sample code:
AEval( aSelected, { |n| oBrw:BookMark := n, oBrw:Delete() } )

I am proposing to have a Delete() method in TDataRow() also. This enables programmer to delete a record, without regard to the datasource, even when he is not deleting from XBrowse.

[TimStone]

Very nice. I built a File Editor class to include in my primary application. This can actually replace some of my existing code.

Did you ever resolve the problem I brought up previously about the numeric edits ( without the decimal in the Harbour build ) ?

Will this edit perhaps replace that issue and do these changes work correctly with Harbour ?

Thanks

Tim

[nageswaragunupudi]

Mr Tim

I posted the solution to the problem pointed out by you in that thread. Please implement that solution.

TDataRow also adopts the same logic as you are using at its core. TDataRow also reads the field names and values ( and some more information ) into an array and after edit, saves the array at the correct location.

This also suffers from the same defect that you pointed out in the default edit screen. With this solution I posted the edit works fine.

My advice is that you may consider replacing your present class with TDataRow(). Advantages are:
- same code for all datasources
- safer
- well integrated with xbrowse
- works with your custom dialogs as well as provides default edit dialog
- reduces lot of code in the program
- with valuable feed back for our users this class could emerge as a very reliable and powerful tool.

Please make a few tests with this class.

[HunterEC]

Rao:

Excellent job ! May I suggest to expand this example a little bit further: ?
1. Dialog size received as parameters.
2. Parameters to define xBrowse size.
3. Instead of browsing all fields, receive an array of columns to be displayed. This way it can display calculated fields.
4. Array of functions / procedures to be passed to xBrowse for editing, deleting records.
5. Array of buttons to be displayed at a user supplied coordinates along actions to be executed. This will enhance the example
beyond the basic ADD / EDIT / DELETE options.


One final question: does TDataRow suffers from the same 9 character field name limitation as TDataBase ?

Thank you very much.