xBrowse as record adding tool for data tables

reinaldocrespo · Post by **reinaldocrespo** » Tue Oct 24, 2017 3:12 pm

Hello everyone;

I'm guessing xbrowse could be used to add/edit records on a table. The problem is I don't want to [**CAN NOT**] append a blank record to a table to be edited. I would like to append a blank record to the xbrowse for editing without appending the blank record to the table. I can take care of appending the new record after modifications to the table at another event.

If you are wondering... I can not append a blank record to a dbf table as it would violate a data integrity rule enforced by ADS SQL engine where a record can not have a blank primary key and a blank foreign key.

Having said that, I'm guessing xbrowsing a dbf will allow me to add a blank record to the browse without adding it to the dbf. Does someone know how?

Thank you,

Reinaldo.

Post by **cnavarro** » Tue Oct 24, 2017 4:29 pm

Dear Reinaldo

But aesthetically you want to add in an xbrowse line, no ?, it can not be in a dialogue, right?

Pero estéticamente quieres añadir en una linea del xbrowse, no?, no puede ser en un diálogo, verdad?

reinaldocrespo · Post by **reinaldocrespo** » Tue Oct 24, 2017 4:47 pm

Cristobal -¿que tal? Espero que bien.

Añadir el nuevo record con un dialogo sería una solución más común.

Yo pensé en añadir una linea en blanco al xbrowse que pueda ser editada usando los mismos métodos del xbrowse. Pero cuando un XBROWSE está basado en un alias solo le puedo añadir una linea en blanco al xbrowse añadiendo un record en blanco a la tabla. Añadir un record en blanco a la tabla es no que no puedo ni quiero hacer.

Ya que el xbrowse es tan popular y lo han trabajado tanto, he pensado que tal vez existe la posibilidad de añadir una linea en blanco sin añadir un record en blanco a la tabla. Tal vez creando un datarow() basado en un array y añadiendo al los rows del xbrowse.

Si esto no fuese posible, entonces simplemente crearé un dialogo en el que el usuario pueda entrar los datos a ser añadidos.

Saludos,

Reinaldo.

Post by **cnavarro** » Tue Oct 24, 2017 5:19 pm

Reinaldo, por aqui seguimos, como siempre, y tú qué tal?

El tema de que por defecto añada un registro en blanco, entiendo los motivos por los que no quieres que lo haga, pero es Mr Rao el que te lo puede confirmar, pero en cuanto al tema de editar en un diálogo mira el method edit, bEdit de Xbrowse y sobre todo funcionar con oRow, a través de su clase TDataRow
Seguro que hay algun ejemplo en el foro, por ejemplo

viewtopic.php?f=6&t=33293&p=196231&hilit=tdatarow#p196195

Rick Lipkin · Post by **Rick Lipkin** » Tue Oct 24, 2017 8:44 pm

Reinaldo

In my Invoice routine I use the down arrow to test for oRs:eof

Code: Select all | Expand


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

Then I fill in the blanks and add my primary key .. just kinda follow the flow .. not the logic..

Rick Lipkin

Code: Select all | Expand


//----------------
Static Func _AddNewRow( oRsDetail,nRN,nTech,cLocation,oBrw,cAsk,oRsRepair,cReturn )

Local Saying,nEid,nLine,nUpdated

If empty( cAsk )
   cAsk := "Y"
Endif

If Empty(cReturn)
   cReturn := "ADD"
ENdif


If cAsk = "Y"

   Saying := "Do you wish to Add a New Record ?"
   If MsgYesNo( Saying )
   Else
      oBrw:SetFocus()
      Return(.f.)
   Endif

Endif

nEid := _GenEid(1)
If nEid = -1
   Saying := "Error in Creating Unique EID for Repair Detail"
   Msginfo( Saying )
   oBrw:SetFocus()
   Return(.f.)
Endif

nLine := _GenLine()
If nLine = -1
   Saying := "Error in Creating Unique LINE for Repair Detail"
   Msginfo( Saying )
   oBrw:SetFocus()
   Return(.f.)
Endif

// update the concurrent counter in repair header

nUpdated := oRsRepair:Fields("Updated"):Value

If Empty(nUpdated)
   nUpdated := 1
Else
   nUpdated++
Endif

oRsRepair:Fields("Updated"):Value := nUpdated
oRsRepair:Update()

oRsDetail:AddNew()

oRsDetail:Fields("RepDetailEid"):Value     := nEid
oRsDetail:Fields("Unique Line"):Value      := nLine
oRsDetail:Fields("Repair Number"):Value    := nRn

If cReturn = "RETURN"
   oRsDetail:Fields("Qty"):Value              := -1
Else
   oRsDetail:Fields("Qty"):Value              := 1
Endif

oRsDetail:Fields("Item Description"):Value := space(100)
oRsDetail:Fields("Price"):Value            := 0.00
oRsDetail:Fields("Tech Number"):Value      := nTech
oRsDetail:Fields("Inventory Id"):Value     := space(50)
oRsDetail:Fields("Inventory Type"):Value   := space(50) //"Labor"+space(45)
oRsDetail:Fields("Cost"):Value             := 0.00
oRsDetail:Fields("Covered By Warranty"):Value := .f.
oRsDetail:Fields("Location"):Value         := cLocation
oRsDetail:Fields("Code"):Value             := space(25)
*oRsDetail:Fields("Shipping Ref"):Value     := space(64)
oRsDetail:Fields("Serial Number"):Value    := space(50)
oRsDetail:Fields("NoTax"):Value            := 0
oRsDetail:Fields("Warranty Provider"):Value := space(50)
*oRsDetail:Fields("Quote Price"):Value      := 0
oRsDetail:Fields("LockedDown"):Value       := .f.
oRsDetail:Fields("IsSerial"):Value         := .f.
oRsDetail:Fields("IsLabor"):Value          := .f.
oRsDetail:Update()

oBrw:ReFresh()
oBrw:nColSel(3) // move to part number on add
*__Keyboard( Chr( VK_RETURN ))
oBrw:SetFocus()

Return(.t.)

//-------------------
Static Func _GenEid(nNum)

LOCAL nRAND
LOCAL oRs, cSQL, oERR

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

Do Case
Case nNum = 1
   cSQL := "SELECT RepDetailEid from [Repair Detail]"
Case nNum = 2
   cSQL := "SELECT InvoiceEid from [Invoice]"
Case nNum = 3
   cSql := "SELECT InvDetailEid from [Invoice Detail]"
Case nNum = 4
   cSql := "SELECT SerialEid from [SerialNumber]"
Case nNum = 5
   cSql := "SELECT LaborEid from [RoLabor]"
Case nNum = 6
   cSql := "SELECT PaymentEid from [Payments]"
Case nNum = 7
   cSql := "SELECT ScrapId from [UtilScrapHeap]"

EndCase


TRY
   oRs:Open( cSQL,xConnect )
CATCH oErr
      Do Case
      Case nNum = 1
         MsgInfo( "Error in Opening Repair Detail to Create Unique EID" )
      Case nNum = 2
         MsgInfo( "Error in Opening Invoice to Create Unique EID" )
      Case nNum = 3
         MsgInfo( "Error in Opening Invoice Detail to Create Unique EID" )
      Case nNum = 4
         MsgInfo( "Error in Opening SerialNumber to Create Unique EID" )
      Case nNum = 5
         MsgInfo( "Error in Opening RoLabor to Create Unique EID" )
      Case nNum = 6
         MsgInfo( "Error in Opening Payments to Create Unique EID" )
      Case nNum = 7
         MsgInfo( "Error in Opening UtilScrapHeap to Create Unique EID" )

      EndCase
      Return(-1)
END TRY

DO WHILE .T.

   nRAND := nRANDOM(100000000)

   // 1 is reserved and 0 is a null key //

   IF nRAND = 1 .or. nRAND = 0 .or. nRAND = NIL
      LOOP
   ENDIF

   IF oRs:eof
   ELSE
      oRs:MoveFirst()

      Do Case
      Case nNum = 1
         oRs:Find("RepDetailEid = "+ltrim(str(nRand)) )
      Case nNum = 2
         oRs:Find("InvoiceEid = "+ltrim(str(nRand)) )
      Case nNum = 3
      *   oRs:Find("InvDetailEid = "+ltrim(str(nRand)) )
      Case nNum = 4
         oRs:Find("SerialEid = "+ltrim(str(nRand)) )
      Case nNum = 5
         oRs:Find("LaborEid = "+ltrim(str(nRand)) )
      Case nNum = 6
         oRs:Find("PaymentEid = "+ltrim(str(nRand)) )
      Case nNum = 7
         oRs:Find("ScrapId = "+ltrim(str(nRand)) )
      EndCase

   ENDIF

   IF oRs:eof
      EXIT
   ELSE
      LOOP
   ENDIF

   EXIT

ENDDO

oRs:Close()
oRs := nil

RETURN( nRAND )

//-------------------
Static Func _GenLine()

LOCAL nRAND
LOCAL oRs, cSQL, oERR

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

cSQL := "SELECT [Unique Line] from [Repair Detail] order by [Unique Line]"

TRY
   oRs:Open( cSQL,xConnect )
CATCH oErr
      MsgInfo( "Error in Opening Repair Detail to Create Unique LINE" )
      Return(-1)
END TRY

If oRs:Eof()
   nRand := 1
Else

   oRs:MoveLast()
   nRand := oRs:Fields("Unique Line"):Value+1

Endif

oRs:CLose()
oRs := NIL

Return(nRand)

reinaldocrespo · Post by **reinaldocrespo** » Tue Oct 24, 2017 9:35 pm

Hey Rick!

I kind-of would prefer not to generate a unique key unless the user clicks on save. If the user, never clicks on save I would also rather not have to deleted that "phantom" inserted record.

BTW and this happens to be a frequently discussed theme: I generate sequential unique keys with an SQL stored procedure. So whenever any client connected to the database attempts to insert a new record, the next sequential key is generated. By client I mean web app, my own win32 apps, android, iOS or whatever (if ever). The beauty of the stored procedure is that it removes that part of the business logic to the database and away from my app. The database also has a referential integrity rule that rejects any insert attempt of a record missing a primary unique key among other things.

The fun part is: my unique keys follow a pattern of two characters depending on the field being generated for followed by the last two digits of the year followed by a dash and then a sequenced number of up to n digits zero filled on the left. Every year the stored procedure "knows" it needs to restart a new sequence for that year. So a uniquely generated key by the stored procedure for the PropertyManagers table may look like: PM17-00003001. Next key during 2017 would be PM17-00003002 and so on. The first record inserted on this table on 2018, would be PM18-00000001.

Whenever I get an email or phone call from a customer referencing CL17-00002520, I know he is speaking of a claim. If instead he is referencing EN17-00003328, I know he is referencing an encounter...

If anyone is interested on the stored procedure code, I will gladly share it. It probably works the same for most SQL engines.

I've always gathered new data to be inserted using a dialog. I was hoping for this particular small transactions tables where I store changes on section 8 portions assigned to the tenant, to use the very same xbrowse row edit capabilities. But that would require showing an empty line on xbrowse where that record really does not exist on the table. The line would only serve the purpose of allowing the user to input data. If saved, then I would execute the appropriate insert statement. I'm sure it can be done. Xbrowse is very versatile. I was hoping not having to follow that infinite source code. :-)

Reinaldo.

Marc Venken · Post by **Marc Venken** » Tue Oct 24, 2017 9:57 pm

I see that Xbrowse show a blanco line for appending and editing a new record, but when you don't put any data into the row, it is not appended to the database and not shown also into the browse.

I've seen in in combination with :

oRs:lAutoAppend := .t. and MariaDB

and than the xbrowse.

I though that Xbrowse does it on all database platforms (Mariadb, Sql and dbf)

I hope i'm reading this post correct and this can help.

BTW : i'm interested in the code !!

reinaldocrespo · Post by **reinaldocrespo** » Tue Oct 24, 2017 10:10 pm

It does help. To clarify, I'm not using any additional layer as in ADO. These are .adt/adi tables and I'm simply using RDDADS.

Code: Select all | Expand


      REDEFINE XBROWSE ::oBrw ID 131 OF ::oDlg ;
        HEADER "LeaseID", "PropertyID", "LandLordID", "ManagerID", "StartDate", "EndDate", "TenantName", ;
               "Rent", "PMagmntFee" ;//, "Balance", "Expenses", "Net", "Payments"  ;
       COLUMNS "LeaseID", "PropertyID", "LandLordID", "ManagerID", "StartDate", "EndDate", "TenantName", ;
               "Rent", "PmFee" ;
        ;//FIELDS { || ::LoadBalances(), ::oDbf:Balance }, { || ::oDbf:Expenses }, {|| ::oDbf:Net }, { || ::oDbf:Payments } ;
         SIZES 100, 170, 140, 140, 70, 70, 100, 100, 90, 100, 100, 100, 100 ;
      PICTURES ,,,,"@d","@d",,"9,999,999.99", "9,999.99", "9,999,999.99", "9,999,999.99", "9,999,999.99", "9,999,999.99" ;
         ALIAS ::oDbf:cAlias ;
     ON CHANGE Eval( ::bChange ) ;
   ON DBLCLICK Eval( ::bEdit ) ;
       FOOTERS ;
          WHEN ! ::lEdit

I'll share the solution when/if I find one.

Thank you all,

Reinaldo.

Post by **anserkk** » Wed Oct 25, 2017 4:04 am

Its just an idea.

All the recordset data will be read into an array initially, then use this Array on xBrowse. So you will be adding a blank array row and the data is validated and if all the data typed in by the user is as per your rules, then append the data to your DBF/Recordset. Of course you will have to manage the EDIT too

Regards
Anser

Marcelo Via Giglio · Post by **Marcelo Via Giglio** » Wed Oct 25, 2017 6:49 pm

Hola Reinaldo,

I think we can't mix data source in xBrowse, maybe with ADS you can use transaction to rollback a not confirm insertion.

Other idea (fast implementation sample) can be

Code: Select all | Expand

#include "FiveWin.ch"

function Main()
   local oBrw, oDlg, aData := {{1,2,3},{4,5,6},{7,8,9},{1,1,1}}
   
   define dialog oDlg from 10,10 to 400,400 pixel
   
   @ 5,5 Xbrowse oBrw ;
              OF oDlg ;
         columns 1,2,3 ;
          header "A","B","C" ;
           sizes 90,90,90;
           ARRAY aData ;
            size 170,170 ;
           pixel
   
   oBrw:createFromCode()
   
   oBrw:bPastEof := {|| if( newRecord( aData, oBrw, oDlg ), ( oBrw:setArray( aData ), ;
                                                              oBrw:goBottom(),;
                                                              oBrw:refresh() ), NIL  ), .T. }
   
   activate dialog oDlg center          

return nil

FUNCTION newRecord( aData, oBrw, oD )
   LOCAL oDlg, v1 := 0, v2 := 0, v3 := 0, lRet := .F.   
   LOCAL aRect := GetCoors(oD:hWnd)
   LOCAL nCol := oBrw:aCols[1]:nDisplayCol + aRect[2], ;
         nRow := ( ( oBrw:nRowSel - 1 ) * oBrw:nRowHeight ) + oBrw:HeaderHeight() + aRect[1] 
    
   
   DEFINE DIALOG oDlg FROM 400,400 to 440,780 PIXEL STYLE nOR( WS_CHILD, WS_POPUP, WS_VISIBLE )
         @ 5,5   GET v1 OF oDlg SIZE 45,12 pixel
         @ 5,50  GET v2 OF oDlg SIZE 45,12 pixel
         @ 5,95 GET v3 OF oDlg SIZE 45,12 pixel
         @ 5,140 BUTTON "ADD" OF oDlg SIZE 45,12 PIXEL ACTION ( AADD( aData, { v1, v2, v3 } ), lRet := .T. , oDlg:end() )
   ACTIVATE DIALOG oDlg ON INIT oDlg:move( nRow + 60, nCol )
   
RETURN lRet

we can put too other xbrowse (1 row) enstead of the of the gets and syncronize both

un abrazo

Marcelo

Rick Lipkin · Post by **Rick Lipkin** » Wed Oct 25, 2017 9:22 pm

Marcelo

I use the same logic .. however I don't think Reinaldo wants to create a new record and generate the primary keys unless the user confirms all the information ... I understand where Reinaldo is coming from .. when you test for oBrw:bPastEof .. you have to create a new record .. and if the user doesn't confirm the data .. you have a blank record in the table you have to Delete.

Rick Lipkin

Marcelo Via Giglio · Post by **Marcelo Via Giglio** » Wed Oct 25, 2017 9:35 pm

Rick,

if we want to confirm the data for a single record, the sample code can be a possible solution, but if Reinaldo needs to confirm many record at the same time, it is other thing

saludos

Marcelo

James Bott · Post by **James Bott** » Thu Oct 26, 2017 1:20 am

I have never used ADS, is it browsing the DBF directly or a recordset? If a recordset, can't you just add a blank record (without a key) and then the key is generated when (and if) the data is saved from the recordset to the DBF?

I always use dialogs for editing, so this issue hasn't come up for me. I just use a blank record object from my TRecord class, then just edit that. It is only saved to the database if the user selects to save it.

James

Post by **nageswaragunupudi** » Thu Oct 26, 2017 6:37 pm

Mr Reinaldo

If you still have sources of Clipper 5.3, see the sources of DBU and SAMPLE\BROWSE.PRG.
There you find the logic to create fantom record, without actually appending a record. Once user modifies any field, you can then insert the new record.

SergeGirard · Post by **SergeGirard** » Thu Dec 07, 2017 10:58 am

Hello Reinaldo,

I am trying to call a MySQL stored procedure but I cannot get it working.
The stored procedure itself is OK and I can call it with PHP:

Code: Select all | Expand


$sql2    = "CALL GET_ALL_P0001()  "; 
$res     = $mysqli->query($sql2);

while($row = $res->fetch_assoc()):
   $FIL_NR  = $row["FIL_NR"] ;   
   $DATUM   = $row["DATUM"] ; 
   $table   .= '<tr><td>' . $FIL_NR . '</td><td>'. $DATUM . '</td></tr>' ;
endwhile;

But in my PRG I get an error: cannot give result in th is context (or something like that...)

Do you have any idea on how to call and retrieve results from a stored procedure?

Thanks in advance,

Serge

FiveTech Software tech support forums

xBrowse as record adding tool for data tables

xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables

Re: xBrowse as record adding tool for data tables