Master/Detail with TDolphin + MySQL

[Kleyber]

Hello All,

I am building a dialog Master/Detail using TDolphin + MySQL using XBrowse, but differently from DBF, I am not sure about the best way to create this in FWH. Can anyone give me some suggestions or examples?

My doubts are specifically when I have to insert a new record in the master table and after this, into the browse (xbrowse) insert a new record in the detail table. The database is normalized, with the foreign keys defined and so on.

TIA,

[Kleyber]

Anybody?

[Rick Lipkin]

Kleyber

I don't know if this will help you .. I use Ms Sql ( or ms Access ) with ADO and it should not be much different coding one Sql database with any other Sql database.

What you will see is a Customer with bills and billing detail. The Customer Primary key is on CustomerId which I use to craft a Sql statement to fetch a set of Billing records. Billing and billing detail are linked via the primary key RN.

Here is the resulting screen shot of that master billing ( header ) and the BillingDetail using xBrowse. I hope the code will give you the answer to your question .. Obviously how you connect to MySql and use TDolphin is proprietary .. but once you create your recordsets and fetch your data .. how you manipulate the data should be the same.

Rick Lipkin

Code: Select all  Expand view

// BillView.prg
//

#INCLUDE "FIVEWIN.CH"
#Include "xBrowse.Ch"

//----------------------
FUNC _BillView( cMODE,oRsBill,oFontB,oBtn0,oBtn1,oBtn2,oBtn3,oBtn4,oBtn13  )

LOCAL SAYING, oUSERS, lOK, oBmp,oLbxD
LOCAL lOK1,oButt1,oButt2,cTitle

Local dBillDate,nInvoiceNumber,cPublication,cDescription,nAmountDue,nBills,cDetail
Local oBillDate,oInvoiceNumber,oPublication,oDescription,oAmountDue,oBills,oDetail
Local oSay1,oSay2,oSay3,oSay4,oSay5,oSay6,oSay7

Local oRsBillDetail,cSql,oErr,nRn
Local cAdName,oAdName

IF oRsBill:EOF
   SAYING := "SORRY ... No Billing Information to View"
   MsgInfo( SAYING )
   RETURN(.F.)
ENDIF

cMode := "V"

nRn := oRsBill:Fields("Rn"):Value

oRsBillDetail := TOleAuto():New( "ADODB.Recordset" )
oRsBillDetail:CursorType     := 1        // opendkeyset
oRsBillDetail:CursorLocation := 3        // local cache
oRsBillDetail:LockType       := 3        // lockoportunistic

cSql := "Select * from [BillingDetail] where [Rn] = "+ltrim(str(nRn))
cSql += " Order By [PublicationName],[Week]"

TRY
   oRsBillDetail:Open( cSQL,xCONNECT )
CATCH oErr
   MsgInfo( "Error in Opening BillingDetail table" )
   RETURN(.F.)
END TRY

oBtn0:Disable()
oBtn1:Disable()
oBtn2:Disable()
oBtn3:Disable()
oBtn4:Disable()
oBtn13:Disable()


LightGreenGrad()
*LightYellow()


dBillDate      := If(empty(oRsBill:Fields("BillDate"):Value),Ctod(""),;
                      TtoDate(oRsBill:Fields("BillDate"):Value ))
nInvoiceNumber := If(empty(oRsBill:Fields("InvoiceNumber"):Value),0,;
                           oRsBill:Fields("InvoiceNumber"):Value)
cPublication   := If(empty(oRsBill:Fields("PublicationName"):Value),space(50),;
                           oRsBill:Fields("PublicationName"):Value)
cDescription   := If(empty(oRsBill:Fields("Description"):Value),space(50),;
                           oRsBill:Fields("Description"):Value)
cAdName        := If(empty(oRsBill:Fields("AdName"):Value),space(50),;
                           oRsBill:Fields("AdName"):Value )
nAmountDue     := If(empty(oRsBill:Fields("AmountDue"):Value),0,;
                           oRsBill:Fields("AmountDue"):Value)
nBills         := If(empty(oRsBill:Fields("Bills"):Value),0,;
                           oRsBill:Fields("Bills"):Value)

cTITLE := "Billing-Invoicing Information"

DO CASE
CASE cMODE = "A"
     cTITLE := "Billing-Invoicing Information   ADD"
CASE cMODE = "E"
     cTITLE := "Billing-Invoicing Information  EDIT"
CASE cMODE = "V"
     cTITLE := "Billing-Invoicing Information  VIEW"
ENDCASE

lOK := .F.

DEFINE BITMAP oBmp   RESOURCE "BILLVIEW"
DEFINE DIALOG oUSERS RESOURCE "BILLVIEW" ;
       TITLE cTITLE

    REDEFINE SAY oSay1 ID 115 OF oUsers UPDATE   // billdate
             oSay1:SetFont( oFontB )
    REDEFINE SAY oSay2 ID 117 OF oUsers UPDATE   // invoice number
             oSay2:SetFont( oFontB )
    REDEFINE SAY oSay3 ID 119 OF oUsers UPDATE   // publication
             oSay3:SetFont( oFontB )
    REDEFINE SAY oSay4 ID 128 OF oUsers UPDATE   // description
             oSay4:SetFont( oFontB )
    REDEFINE SAY oSay5 ID 113 OF oUsers UPDATE   // ad campaign
             oSay5:SetFont( oFontB )
    REDEFINE SAY oSay6 ID 123 OF oUsers UPDATE   // amount due
             oSay6:SetFont( oFontB )
    REDEFINE SAY oSay7 ID 125 OF oUsers UPDATE   // number bills
             oSay7:SetFont( oFontB )


    REDEFINE xBROWSE oLbxD           ;
         RECORDSET oRsBillDetail     ;
         COLUMNS "PUBLICATIONNAME",  ;
                 "DESCRIPTION",      ;
                 "TOTALBILLED"       ;
         COLSIZES 160,155,75         ;
         HEADERS "Publication",      ;
                 "Description",      ;
                 "Billed"            ;
         ID 111 of oUsers            ;
         AUTOCOLS LINES CELL

         oLbxD:lRecordSelector := .f.
         oLbxD:lHScroll := .f. // turn off horiz scroll bar

         _BrowColor(oLbxD)

    REDEFINE GET oBillDate      VAR dBillDate      ID 116 of oUSERS COLOR "N/W" READONLY
    REDEFINE GET oInvoiceNumber VAR nInvoiceNumber ID 118 of oUSERS PICTURE "999999999999" COLOR "N/W" READONLY
    REDEFINE GET oPublication   VAR cPublication   ID 120 of oUSERS COLOR "N/W" READONLY
    REDEFINE GET oAdName        VAR cAdName        ID 112 of oUSERS COLOR "N/W" READONLY
    REDEFINE GET oDescription   VAR cDescription   ID 127 of oUSERS COLOR "N/W" READONLY
    REDEFINE GET oAmountDue     VAR nAmountDue     ID 124 of oUSERS PICTURE "99999999.99" COLOR "N/W" READONLY
    REDEFINE GET oBills         VAR nBills         ID 126 of oUSERS PICTURE "9999" COLOR "N/W" READONLY


REDEFINE BTNBMP oButt1 ID 150 of oUSERS   ;     // ok
         RESOURCE "OK", "DOK", "DOK" ;
         PROMPT "  &Ok    " LEFT 2007;
         ACTION (lOK := .T., oUsers:END())

REDEFINE BTNBMP oButt2 ID 160 of oUSERS   ;    // cancel
         RESOURCE "CANCEL", "DCANCEL", "DCANCEL" ;
         PROMPT "&Cancel   " LEFT 2007;
         ACTION ( lOk := .f., oUSERS:END())

         ACTIVATE DIALOG oUSERS ;
         ON INIT (oLbxD:SetFocus() );
         ON PAINT (PalBmpDraw( hDC, 0, 0, oBmp:hBitmap ))

oRsBillDetail:CLose()
oRsBillDetail := nil

LightGreyGrad()

oBmp:End()

oBtn0:Enable()
oBtn1:Enable()
oBtn2:Enable()
oBtn3:Enable()
oBtn4:Enable()
oBtn13:Enable()

RETURN(.t. )


// end BillView.prg
 

[Kleyber]

Hi Rick,

Thank you very much for your answer (even on a Sunday). It's gonna give me a good idea about what to I have to do. I just have a question: When you add a detail record, do you use the browse to insert it? Let me clarify what I really need:

In DBF I have 2 files, as you have in MSSQL. But when I start to insert a new bill, I create a temporary DBF (master) and another temporary DBF (detail). And those are related. After doing that, I make the saving the master temporary DBF into the master DBF and the detail temporary DBF into the detail DBF, in order to avoid direct access to the real master and detail DBF. And I do this using a dialog with xbrowse, but the details are inserted using the browse directly.

I hope you understand.

[Rick Lipkin]

Kleyber

There are several ways you can work directly with xBrowse .. the quickest way is to add FASTEDIT to your browse code..

Code: Select all  Expand view

 AUTOCOLS LINES CELL FASTEDIT
 

Once you see that you can now edit cells, you will want to be able to leverage the bOnPreEdit and bOnPostEdit code blocks to add additional functionality to edit your cells.

Consider this code from an Invoicing program .. notice how you can make a very rich edit experience for any cell of your choice... This code is taken from the Invoice Detail.

Rick Lipkin

Code: Select all  Expand view

REDEFINE xBROWSE oLBXB        ;
         RECORDSET oRsDetail         ;
         COLUMNS "QTY",              ;
                 "INVENTORY ID",     ;
                 "INVENTORY TYPE",   ;
                 "ITEM DESCRIPTION", ;
                 "PRICE",            ;
                 "LOCATION",         ;
                 "COVERED BY WARRANTY" ;
         COLSIZES 45,115,55,300,65,150,80,50   ;    //120
         HEADERS "Qty",              ;
                 "Part Number",      ;
                 "Type",             ;
                 "Description",      ;
                 "Price",            ;
                 "Location",         ;
                 "Warranty"          ;
         ID 172 of oWorkB            ;
       AUTOCOLS LINES CELL FASTEDIT

       // row numbers
       ADD oCol to oLbxB AT 1 DATA oLbxB:KeyNo() HEADER 'Ln' size 23 //PICTURE '9999'
       oLbxB:aCols[1]:nDataStrAlign := AL_LEFT
       oLbxB:aCols[1]:nHeadStrAlign := AL_LEFT


       If cMode <> "V"

          AEval( oLbxB:aCols, { |o| o:nEditType := EDIT_GET } )

          // row number
          oLbxB:aCols[1]:nEditType := EDIT_NONE

          // qty
          oLbxB:aCols[2]:bEditWhen := { || If(oRsDetail:fields("LockedDown"):Value = .f., .t.,.f.) }
          oLbxB:aCols[2]:bOnPostEdit := {|o,v| If(_ChkSerial( v,oRsDetail,oLbxB),;
                         _InvtLook( v, oRsDetail, oRsRepair, "2", oLbxB, @lTaxable,oTaxable, cLoc,;
                         oLabor,oParts,oMisc,oTax,oTotal,nTaxNumber,nAssignedTo), ) }

          // part number
          oLbxB:aCols[3]:bEditWhen   := { || If(oRsDetail:fields("LockedDown"):Value = .f., .t.,.f.) }
          oLbxB:aCols[3]:bOnPreEdit  := { || If(oRsDetail:Fields("Inventory Id"):Value = "  ", , __Keyboard( Chr( VK_HOME ))) }
          oLbxB:aCols[3]:bOnPostEdit := {|o,v| _InvtLook( v, oRsDetail, oRsRepair, "3", oLbxB, @lTaxable, oTaxable, cLoc,;
                                      oLabor,oParts,oMisc,oTax,oTotal,nTaxNumber,nAssignedTo) }

          // type
          oLbxB:aCols[4]:nEditType    := EDIT_LISTBOX
          oLbxB:aCols[4]:aEditListTxt := aType
          oLbxB:aCols[4]:bOnPostEdit  := {|o,v| _GetPullDown( v,oLbxB,oRsDetail ) }

          // description
          oLbxB:aCols[5]:nEditType   := EDIT_GET
          oLbxB:aCols[5]:bOnPreEdit  := { || If(oRsDetail:Fields("Item Description"):Value = " ", ,;
                                     __Keyboard( Chr( VK_HOME )) )}
          oLbxB:aCols[5]:bOnPostEdit := {|o,v| _GetText( v,oLbxB,oRsDetail ) }


          // labor
          ADD oCol to oLbxB AT 6 HEADER 'Labor' size 50     //60
          oLbxB:aCols[ 6 ]:nEditType  := EDIT_GET_BUTTON
          oLbxB:aCols[ 6 ]:bEditBlock := {|row, col, oCol| ;
                       If(oRsDetail:Fields("Inventory Type"):Value = "Labor",;
                       ( nAmount := _LabrBrow( ;
                       nRepairNumber,;
                       aEmp,;
                       aTech,;
                       oRsDetail,;
                       "","","","","","R","A",oRsDetail:Fields("Unique Line"):Value ),;
                       oRsDetail:Fields("Price"):Value := nAmount,;
                       oRsDetail:Update()), MsgInfo( "Type is not Labor"))   }  // repair

          oLbxB:aCols[ 6 ]:addbmpfile( "clockview.bmp" )
          oLbxB:aCols[ 6 ]:addbmpfile( "clockadd.bmp" )
          oLbxB:aCols[ 6 ]:bBmpData := { | lValue | If( oRsDetail:Fields("IsLabor"):Value = .t., 1, 2 ) }
          oLbxB:aCols[ 6 ]:lBtnTransparent := .t.

          //  Price
          oLbxB:aCols[7]:nEditType := EDIT_GET
          oLbxB:aCols[7]:bOnPostEdit := {|o,p| _GetPrice( p,oLbxB,oRsDetail,oRsRepair,lTaxable,oTaxable,;
                                                      oLabor,oParts,oMisc,oTax,oTotal,nTaxNumber) }
          // extention
          ADD oCol TO oLbxB AT 8 DATA {|x| x :=  _CalcExt( oRsDetail:Fields("Qty"):Value,;
                                    oRsDetail:Fields("Price"):Value )} HEADER 'Ext' size 65
          oLbxB:aCols[8]:nEditType := EDIT_NONE
          oLbxB:aCols[8]:nDataStrAlign := AL_RIGHT
          oLbxB:aCols[8]:nHeadStrAlign := AL_RIGHT

          // serial number
          ADD oCol to oLbxB AT 9 HEADER 'Serial Num' size 60
          oLbxB:aCols[ 9 ]:nEditType  := EDIT_GET_BUTTON
          oLbxB:aCols[ 9 ]:bEditBlock := {|row, col, oCol| oLbxB:GoLeftMost(),_SerBrow(;
                       nRepairNumber,;
                       oRsDetail:Fields("Inventory Id"):Value,;
                       oRsDetail:Fields("Qty"):Value,;
                       oRsDetail:Fields("Unique Line"):Value,;
                       oRsDetail,;
                       "","","","","","R","A" ) }  // repair

          oLbxB:aCols[ 9 ]:addbmpfile( "zoom2.bmp" )
          oLbxB:aCols[ 9 ]:addbmpfile( "adddbf.bmp" )
          oLbxB:aCols[ 9 ]:bBmpData := { | lValue | If( oRsDetail:Fields("IsSerial"):Value = .t., 1, 2 ) }
          oLbxB:aCols[ 9 ]:lBtnTransparent := .t.


          // location
          oLbxB:aCols[10]:nEditType := EDIT_NONE

          // warranty
          oLbxB:aCols[11]:nEditType := EDIT_GET
          if ! empty(oCol := oLbxB:oCol( "WARRANTY" ))
             oCol:SetCheck( { "ON", "OFF" } )
             oCol:cSortOrder  := nil
          endif

     *    oLbxB:bClrStd := {|| {if(oRsDetail:fields("LockedDown"):Value = .t., CLR_BLUE, CLR_BLACK), CLR_WHITE  } }
          oLbxB:oCol( "Qty" ):bClrStd         := {|| {if(oRsDetail:fields("LockedDown"):Value = .t., CLR_HBLUE, CLR_BLACK), CLR_WHITE  } }
          oLbxB:oCol( "Part Number" ):bClrStd := {|| {if(oRsDetail:fields("LockedDown"):Value = .t., CLR_HBLUE, CLR_BLACK), CLR_WHITE  } }
          oLbxB:oCol( "Type" ):bClrStd        := {|| {if(oRsDetail:fields("LockedDown"):Value = .t., CLR_HBLUE, CLR_BLACK), CLR_WHITE  } }
          oLbxB:oCol( "Ext" ):bClrStd         := {|| {if(oRsDetail:fields("LockedDown"):Value = .t., CLR_HBLUE, CLR_BLACK), CLR_WHITE  } }

          // add a new record
          oLbxB:bPastEof = {|| _AddNewRow( oRsDetail,nRepairNumber,nAssignedTo,cLoc,oLbxB ) }

       Endif
 

[Kleyber]

Thanks a lot Rick. I`ll take a good look at your sample.