xBrowse + EDIT_GET

[vilian]

Friends,

I'm having a problem with the code bellow where I'm showing a rowset content and trying allow to edit two fields(Columns 3 and 9). But the EDIT_GET is only working for the column 3. For the other column(9) double click is not activating the edit.

I have already tried with EDIT_GET_BUTTON, but don't work too. Do you know what am I doing wrong ?

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "Splitter.ch"

FUNCTION Main()
LOCAL oCn, oRs,nSec:=Seconds(),oQryMvg,aCols,oLbx[3],oWndAgd,oCol,oPanel[2],oVSplit,oPanelExplorer,oExBar,oMenu

   oCn   := Connect()

      //QUERY DE GUIAS
   oQryMvg := oCN:Query("SELECT mvg.*,cli.fantasia,cli.end_fatu,cli.no_fatu,loc.descricao,loc.zs,pes.nome,ttp.descricao AS tipo,ttp.natureza,mps.hora_ini "+;
                                 "FROM tmovguias AS mvg LEFT JOIN clientes AS cli ON mvg.ccliente=cli.ccliente LEFT JOIN tlocais AS loc ON mvg.clocal=loc.clocal "+;
                                 "LEFT JOIN ttipoviag AS ttp ON mvg.ctipo=ttp.ctipo LEFT JOIN tmovgpes AS mps ON mps.data = ? AND mps.funcao IN('F','O') AND "+;
                                 "mvg.cequipto=mps.cequipto LEFT JOIN tpessoal AS pes ON mps.cmat=pes.cmat WHERE mvg.data = ?",{Date()-2,Date()-2})

   DEFINE WINDOW oWndAgd TITLE "Ordens de Serviço para "+Dtoc(date()) MENU oMenu

      oExBar := TExplorerBar():New( 0, 0, 255, 300, oPanelExplorer )

      //CRIA BROWSE DE VEÍCULOS
      oPanel[1] := oExBar:AddPanel( "Veículos",,255 )


      //CRIA BROWSE DE EQUIPES
      oPanel[2] := oExBar:AddPanel( "Equipe" ,,165 )


      //BROWSE DE OS
      aCols := {{"IF(natureza='R' AND cterno=NIL,[Recolhimento],cterno)","Nº Terminal",,85,0},{{|| oQryMvg:fantasia+CRLF+IF(oQryMvg:descricao=NIL.OR.Empty(oQryMvg:descricao),Rtrim(oQryMvg:end_fatu)+","+oQryMvg:no_fatu,oQryMvg:descricao)},"Cliente/Local",,300,0},;
                {"cequipto","Viatura","!!!!!",70,0},{"nome","Operador/Fiel",,200,0},{"hora_ini","Hr.Inicio","@R 99:99",50,0,"hora_ini"},{"hora_fim","Hr.Término","@R 99:99",60,0,"hora_fim"},{"hora_lim","Hr.Limite","@R 99:99",50,0,"hora_lim"},;
                {"tipo","Tipo de OS",,120,0,"tipo"},{"zs","Rota","@!",120,0},{{|| aSituacao[Max(At(oQryMvg:situacao,"NEIFTC"),1)]},"Situação",,100,0},{"clote","Lote",,45,0,"clote"},{"valordin","Valor","999,999,999.99",80,1}}

      @ 00,255 XBROWSE oLbx[3] OF oWndAgd COLUMNS aCols DATASOURCE oQryMvg CELL LINES FASTEDIT AUTOSORT PIXEL
               oLbx[3]:nRowHeight    := 35
               oLbx[3]:nMarqueeStyle := 3
               oLbx[3]:nStretchCol   := STRETCHCOL_LAST

               oCol:= oLbx[3]:aCols[ 3 ]
               oCol:nEditType   := EDIT_GET

               //the problem is happening in this column
               oCol:= oLbx[3]:aCols[ 9 ]
               oCol:nEditType   := EDIT_GET

               oLbx[3]:CreateFromCode()

      @ 0,255    SPLITTER oVSplit ;
                 VERTICAL ;
                 PREVIOUS CONTROLS oExBar ;
                 HINDS CONTROLS oLbx[3];
                 LEFT MARGIN 250 ;
                 RIGHT MARGIN 80 ;
                 SIZE 4, 355  PIXEL ;
                 OF oWndAgd ;
                 _3DLOOK

   ACTIVATE WINDOW oWndAgd MAXIMIZED ;
      ON RESIZE (oVSplit:AdjRight() )

   oItalic:End()
   oBold:End()

   oCn:Close()

return nil

[nageswaragunupudi]

Can you kindly reproduce this problem using any tables that are already exist in our demoserver?
You can log into the demo server with FW_DemoDB(). Check the existing tables with oCn:ListTables()
You can also add any test table of your own with dummy data.

[vilian]

Mr Rao,

It's difficult because I don't know those tables, how are the relations, etc. The worst is IF works your tables I will continue with my problem

[nageswaragunupudi]

There are well known tables customer and states and in addition, more tables.
You can use customer and states.
Or you can create your own table and fill some dummy data or 2 rows.

Please try this small sample to view the tables

Code: Select all  Expand view  RUN

#include "fivewin.ch"

REQUEST DBFCDX

function Main()

   local oCn

   SET DATE ITALIAN
   SET CENTURY ON

   FW_SetUnicode( .t. )

   oCn   := FW_DemoDB()

   XBROWSER oCn:ListTables() ;
      TITLE "Dbl-Click to View Table" ;
      SETUP ;
      oBrw:aCols[ 1 ]:bLDClickData := ;
      { |r,c,f,o| ShowTable( oCn, o:Value ) }

   oCn:Close()

return nil


function ShowTable( oCn, cTable )

   local oRs

   if cTable == "custbig"
      MsgRun( "Reading " + cTable, "Please wait", { || oRs   := oCn:RecSet( cTable, -1 ) } )
   else
      MsgRun( "Reading " + cTable, "Please wait", { || oRs   := oCn:RowSet( cTable ) } )
   endif

   XBROWSER oRs TITLE cTable FASTEDIT NOMODAL SHOW RECID

return nil

[vilian]

Mr Rao,

IF I create the rowset only with the table CUSTOMER everything is fine. Is there some table that has a relation with this table ?

[nageswaragunupudi]

Code: Select all  Expand view  RUN

TEXT INTO cSql
   select c.* ,s.code as statecode, s.name as statename
   from customer c
   left join states s on c.`state` = s.code
ENDTEXT
 

[vilian]

Mr Rao,
Here is it:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "Splitter.ch"

function Main()

   local oCn, oRs,nSec:=Seconds(),oQryMvg,oItalic,oBold,aCols,oLbx[3],oWndAgd,oCol,oPanel[2],oVSplit,oPanelExplorer,oExBar,oMenu

   oCn   := FW_DemoDB()

      //QUERY DE GUIAS
   oQryMvg := oCN:Query("SELECT cus.*,sta.name FROM customer AS cus LEFT JOIN states AS sta ON cus.state = sta.code WHERE cus.state = ?",{"WA"})


   //EXIBE JANELA DA AGENDA
   DEFINE FONT oBold   NAME "Arial" SIZE 0,-12 bold
   DEFINE FONT oItalic NAME "Arial" SIZE 0,-10 italic

   DEFINE WINDOW oWndAgd TITLE "Ordens de Serviço para "+Dtoc(date()) MENU oMenu

      oExBar := TExplorerBar():New( 0, 0, 255, 300, oPanelExplorer )

      //CRIA BROWSE DE VEÍCULOS
      oPanel[1] := oExBar:AddPanel( "Veículos",,255 )


      //CRIA BROWSE DE EQUIPES
      oPanel[2] := oExBar:AddPanel( "Equipe" ,,165 )


      //BROWSE DE OS
      aCols := {{"first","Nº Terminal",,85,0},{{|| oQryMvg:street+CRLF+oQryMvg:city},"Cliente/Local",,300,0},;
                {"state","Viatura","!!!!!",70,0},{"zip","Operador/Fiel",,200,0},{"name","Hr.Inicio",,50,0},{"salary","Valor","999,999,999.99",80,1}}

      @ 00,255 XBROWSE oLbx[3] OF oWndAgd COLUMNS aCols DATASOURCE oQryMvg CELL LINES FASTEDIT AUTOSORT PIXEL
               oLbx[3]:nRowHeight    := 35
               oLbx[3]:nMarqueeStyle := 3
               oLbx[3]:nStretchCol   := STRETCHCOL_LAST

               oCol:= oLbx[3]:aCols[ 3 ]
               oCol:nEditType   := EDIT_GET
               //oCol:bEditBlock  := { |r,c,oCol| MsgInfo("Test 1"),NIL }

               //the problem is happening in this column
               oCol:= oLbx[3]:aCols[ 5 ]
               oCol:nEditType   := EDIT_GET
               //oCol:bEditBlock  := { |r,c,oCol| MsgInfo("Test 2"),NIL }

               oLbx[3]:CreateFromCode()

      @ 0,255    SPLITTER oVSplit ;
                 VERTICAL ;
                 PREVIOUS CONTROLS oExBar ;
                 HINDS CONTROLS oLbx[3];
                 LEFT MARGIN 250 ;
                 RIGHT MARGIN 80 ;
                 SIZE 4, 355  PIXEL ;
                 OF oWndAgd ;
                 _3DLOOK

   ACTIVATE WINDOW oWndAgd MAXIMIZED ;
      ON RESIZE (oVSplit:AdjRight() )

   oItalic:End()
   oBold:End()

   oCn:Close()

return nil

 

[nageswaragunupudi]

I will check it now.

[nageswaragunupudi]

5th column in the browse shows the field "name" in the query.
This is a field from joined table "states", not the base table "customer"
By default, only the fields in the basetable are editable. Other fields read from joined tables are flagged as readonly.

Now why do you want to modify the name of state in the states table, while browsing customer table? What is your requirement?

[vilian]

In my program, this is a column of other table that I want to edit, that is I'm editing two tables at the same time. I believe I will have to save the content by myself, probably using bOnPostEdit, isn't it ?

I just want the user can type the value in the xbrowse.

[nageswaragunupudi]

Ok.
That means in the above case, we want to save the changed name in states table.
Example, we like to change "Washington" as "Washigton DC" and save in the states table. Right?

[vilian]

Yes, It's.

[nageswaragunupudi]

I suggest two alternatives. In both the cases please remember to read primary key of the joined table also.

1) Update states table at xbrowse level.

Code: Select all  Expand view  RUN

#include "fivewin.ch"

function Main()

   local oCn, oRs, cSql

TEXT INTO cSql
   SELECT C.*, S.ID AS StateID, S.NAME AS StateName
   FROM customer C LEFT JOIN states S ON C.STATE = S.CODE
ENDTEXT

   oCn   := FW_DemoDB()
   oRs   := oCn:RowSet( cSql )

   XBROWSER oRs COLUMNS "First", "City", "State", "StateName" FASTEDIT SETUP MySetup( oBrw )

return nil

static function MySetup( oBrw )

   local oRs   := oBrw:oDbf
   local cSql

   WITH OBJECT oBrw:StateName
      :lReadOnly     := .f.
      :bOnPostEdit   :=    ;
         < |oCol, xVal, nKey|
         if nKey != VK_ESCAPE
            oRs:oCn:Update( "states", "name", { xVal }, "id=" + oRs:oCn:ValToSQL( oRs:StateID ) )
            oRs:ReQuery()
            oBrw:Refresh()
         endif
         return nil
         >
   END

return nil
 

2. Update states table at RowSet level:

Code: Select all  Expand view  RUN

#include "fivewin.ch"
#include "Splitter.ch"

function Main()

   local oCn, oRs, cSql

TEXT INTO cSql
   SELECT C.*, S.ID AS StateID, S.NAME AS StateName
   FROM customer C LEFT JOIN states S ON C.STATE = S.CODE
ENDTEXT

   oCn   := FW_DemoDB()
   oRs   := oCn:RowSet( cSql )

   WITH OBJECT oRs
      :Fields( "statename" ):lReadOnly := .f.
      :bTrigger := { |rs,fldname| MyTrigger( rs, fldname ) }
   END

   XBROWSER oRs COLUMNS "First", "City", "State", "StateName" FASTEDIT

return nil

static function MyTrigger( oRs, cFieldName )

   if cFieldName == "statename"  // all lower
      oRs:oCn:Update( "states", "name", { oRs:FieldGet( cFieldName ) }, "id=" + oRs:oCn:ValToSQL( oRs:StateID ) )
      oRs:requery()
   endif

return nil
 

First build and run both the programs and then adopt to your requirements.
I feel the 1st alternative is a simpler approach.

[vilian]

Mr Rao,

Thank You. It's working now

[Marc Venken]

Based on the samples below, I made a small program that create's the browse I want, But the update of field (Cat_Pos = position) is not done.

There is something missing, most likely with the primary keys not included ?

For : PS_category_product I see
id_category
id_product

For : PS_product_lang
id_product
id_shop
id_lang

Code: Select all  Expand view  RUN

#include "fivewin.ch"

static oCn, oRs

function Main()

   local oDlg, oFont, oBrwParent, oBrwChild
   local oStates, oCust, oProduct
 
   FW_SetUnicode( .f. )

  Serversetup()

  FWCONNECT oCn HOST cServer USER cUser PASSWORD cPassword DATABASE cDatabase

  if oCn == nil
     ? "Failed to connect"
     return nil
  endif

TEXT INTO cSql

SELECT
  a.id_category as Cat_Id,
  a.name as Cat_Name,
  b.position as Cat_Pos,
  b.id_product,
  b.id_category,
  c.name as Pro_Name,
  c.id_product
FROM
  ps_category_lang as a
LEFT JOIN
  ps_category_product as b
    ON a.id_category = b.id_category
LEFT JOIN
  ps_product_lang as c
    ON c.id_product = b.id_product
    where a.id_lang = 1 and c.id_lang = 1
order by Cat_Id, Cat_Pos

ENDTEXT

oRs := oCn:RowSet( cSql )

XBROWSER oRs COLUMNS "Cat_Id", "Cat_Name","Cat_Pos","Pro_Name" FASTEDIT SETUP MySetup( oBrw )

ocn:close()

return nil

//***********************************************************

static function MySetup( oBrw )

   local oRs   := oBrw:oDbf
   local cSql

   WITH OBJECT oBrw:Cat_Pos
      :lReadOnly     := .f.
      :bOnPostEdit   :=    ;
         < |oCol, xVal, nKey|
         if nKey != VK_ESCAPE
            oRs:oCn:Update( "ps_category_product", "position", { xVal }, "id=" + oRs:oCn:ValToSQL( oRs:Cat_Pos ) )
            oRs:ReQuery()
            oBrw:Refresh()
         endif
         return nil
         >
   END

return nil