Xbrowse with more that one oRs into it.

Xbrowse with more that one oRs into it.

Postby Marc Venken » Sun Nov 12, 2017 10:39 pm

Hello,

I'm trying to show 2 browses :

PS. The tables are from a Prestashop Cardshop, and I have NO idea how the relations are set. I have access to them, and I see that they have a commen field
id_product in product file and also in the categorie file.

3 tables must be opened

ps_category_lang : here are the names from the categories, and a ID from the categorie
ps_category_product : here are all product belonging to a categorie AND the product ID's
ps_product_lang : here are the product details like name,...

I have 2 browses : (they work, only not for changing the product names)

Image

1. Top browse = Categories
2. Bottom browse = products detail from that categorie

In browse 2. I want also that the products names to be included in the 2 browse
I'm almost sure that it has to do with relations, but I don't know how the relations are setup.

Code: Select all  Expand view

// products
  oRsProduct := oCn:RowSet( "select * from ps_product_lang " )

// Groeps
  oRsGroepen:= oCn:RowSet( "SELECT * FROM `ps_category_lang` where `id_lang` = ? ", { "1" } )

// Order of product on pages
  cZoekProduct := "77"
  oRsvolgorde:= oCn:RowSet( "SELECT * FROM `ps_category_product` where `id_category` = ? ", { cZoekProduct } )


/////////////////////////////////////////////////////////////////  /////////////////////////////////////////////////
   aVelden4 :=  { ;
   { "id_category"    , "Cat_ID"          ,nil,  50 }, ; // 1
   { "name"           , "Naam"            ,nil,  200 }, ; // 2
   { "position"       , "Pos"             ,nil,  50 }}   // 9

   @ 1,1 XBROWSE oBrw[4] size 1100,100 PIXEL OF  oFld:aDialogs[ 2 ] font oFont ;
      DATASOURCE oRsgroepen;
      COLUMNS aVelden4;
      AUTOSORT CELL LINES NOBORDER FOOTERS
      //FASTEDIT

     oBrw[4]:nEditTypes = EDIT_GET
     oBrw[4]:SetChecks()


     oBrw[4]:lF2KeyToEdit := .t.  // Edit when F2 is pressed
     oBrw[4]:nHeadStrAligns  := AL_CENTER

   WITH OBJECT oBrw[4]

      :lColChangeNotify := .t.

      :oHeaderFonts     := oBold

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS

      :lFooter          := .t.
      :bRecSelHeader    := { || "RowNo" }
      :bRecSelData      := { |o| o:KeyNo }
      :bRecSelFooter    := { |o| o:nLen }
      :oRecSelFont      := oFont  // optional
      :nRecSelWidth     := "99999" // required size

      :bOnChange     := { || oRSNewGroep(oBrw[4]:cat_id:Value), oBrw[3]:refresh() }

   END

   oBrw[4]:CreateFromCode()

/////////////////////////////////////////////////////////////////  BRW FOLDER 3 /////////////////////////////////////////////////

   aVelden3 :=  { ;
   { "id_category"    , "Cat_ID"          ,nil,  50 }, ; // 1
   { "id_product"     , "Product_ID"      ,nil,  70 }, ; // 2
   { "position"       , "Pos"             ,nil,  50 }}   // 9

   @ 110,1 XBROWSE oBrw[3] size 1100,200 PIXEL OF  oFld:aDialogs[ 2 ] font oFont ;
      DATASOURCE oRsvolgorde;
      COLUMNS aVelden3;
      FIELDS oRsProduct:description_short ;
      AUTOSORT CELL LINES NOBORDER FOOTERS

     oBrw[3]:nEditTypes = EDIT_GET
     oBrw[3]:SetChecks()


     oBrw[3]:lF2KeyToEdit := .t.  // Edit when F2 is pressed
     oBrw[3]:nHeadStrAligns  := AL_CENTER

   WITH OBJECT oBrw[3]

      :lColChangeNotify := .t.

      :oHeaderFonts     := oBold

      :bClrEdits        := { || { CLR_BLACK, CLR_YELLOW }}
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }

      :nColDividerStyle := LINESTYLE_LIGHTGRAY
      :nRowDividerStyle := LINESTYLE_LIGHTGRAY
      :bClrRowFocus     := { || { CLR_BLACK, RGB(185,220,255) } }
      :nMarqueeStyle    := MARQSTYLE_HIGHLROWMS

      :lFooter          := .t.
      :bRecSelHeader    := { || "RowNo" }
      :bRecSelData      := { |o| o:KeyNo }
      :bRecSelFooter    := { |o| o:nLen }
      :oRecSelFont      := oFont  // optional
      :nRecSelWidth     := "99999" // required size

   END

   oBrw[3]:CreateFromCode()

//======================================================

function oRsNewGroep(cData)
  cZoek = alltrim(str(cData))
  oRsvolgorde:Requery( { cZoek } )
  //cZoekID = oRsAtt:id_attribute
  //oRs:Requery( { cZoekID } )
return NIL

 


In HeidiSql I see folowing structure, with indexes. How do we change the index in FW for a online database ?

ps_product_lang

Image

Thanks
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: Xbrowse with more that one oRs into it.

Postby nageswaragunupudi » Mon Nov 13, 2017 10:32 am

How do we change the index in FW for a online database ?

Why do you want to changes indexes? You can read the data from the tables without worrying about indexes.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Xbrowse with more that one oRs into it.

Postby Marc Venken » Mon Nov 13, 2017 11:13 am

I had no idea...
I never worked with online (Sql??) databases and there index's. and how they set relations

If I open them, it seems that all index's are automatic opend, but there will be a specific master index active.

So in my case above, I think that the correct index is not active.

I do think however that the logic of indexes and calling scopes is different in dbf than Sql, and I will read into it.

I found a sample with the "Fields" into the Browse and get it partial working

The Rowset is correct in the first action, but not in the products detail (products ID should show also the correct names) are not.
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: Xbrowse with more that one oRs into it.

Postby nageswaragunupudi » Mon Nov 13, 2017 11:18 am

You stop worring about indexes.
You can join tables on whatever fields you want and read whatever fields you want. It does not matter whether those fields are indexed or not.
Do not use 2 Rowsets, instead read all the fields you want by joining the tables.
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Xbrowse with more that one oRs into it.

Postby Marc Venken » Mon Nov 13, 2017 11:45 am

Thanks.

So this means that when I join tables, I never change original indexes ?

I found a small topic with a join example.

Do I have to look for info in the FW docs, or in the SQL docs ?

This kind is what you mean I should use ?, because I now have 3 rowssets and you advice to use only one.

MY Rowsets

// products
oRsProduct := oCn:RowSet( "select * from ps_product_lang " )

// Groeps
oRsGroepen:= oCn:RowSet( "SELECT * FROM `ps_category_lang` where `id_lang` = ? ", { "1" } )

// Order of product on pages
cZoekProduct := "77"
oRsvolgorde:= oCn:RowSet( "SELECT * FROM `ps_category_product` where `id_category` = ? ", { cZoekProduct } )

Code: Select all  Expand view

oRsHdr:Source               := "SELECT " +;
                                        "HdrOdc.*," +;
                                        "Proveedor.Pro_Nom," +;
                                        "Proveedor.Pro_Sub," +;
                                        "Proyecto.Pro_Des " +;
                                    "FROM " +;
                                        "HdrOdc " +;
                                    "LEFT JOIN " +;
                                        "Proveedor " +;
                                    "ON " +;
                                        "HdrOdc.Hdr_Prv = Proveedor.Pro_Pro " +;
                                    "LEFT JOIN " +;
                                        "Proyecto " +;
                                    "ON " +;
                                        "HdrOdc.Hdr_Pro = Proyecto.Pro_Pro " +;
                                    "WHERE " +;
                                        "HdrOdc.Hdr_Odc < 0"
 
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium

Re: Xbrowse with more that one oRs into it.

Postby Rick Lipkin » Mon Nov 13, 2017 2:00 pm

Marc

Both MS Sql Server and Ms Access have excellent query builders inside their tools I use both .. here are links to some examples using Sql Server Mgmt Studio .. I highly recommend the visual query builder ..

https://www.mssqltips.com/sqlservertip/ ... -designer/

https://www.youtube.com/watch?v=jOnVJwhJxhs

Rick Lipkin
User avatar
Rick Lipkin
 
Posts: 2606
Joined: Fri Oct 07, 2005 1:50 pm
Location: Columbia, South Carolina USA

Re: Xbrowse with more that one oRs into it.

Postby nageswaragunupudi » Mon Nov 13, 2017 2:12 pm

So this means that when I join tables, I never change original indexes ?

Yes. Total forget about indexes. There is no concept of opening indexes and setting / changing indexes.
At this stage, the best thing for you is to totally forget about indexes.
The Server decides if any indexes are to be used and if so what indexes are to be used. You do not need to do anything.
Do I have to look for info in the FW docs, or in the SQL docs ?

SQL docs.
But you can find plenty of examples using JOINs in fwh\samples and also many postings on this forum
Regards

G. N. Rao.
Hyderabad, India
User avatar
nageswaragunupudi
 
Posts: 10206
Joined: Sun Nov 19, 2006 5:22 am
Location: India

Re: Xbrowse with more that one oRs into it.

Postby Marc Venken » Mon Nov 13, 2017 9:23 pm

Hey Rick, Thanks for the links. It gave me the tip to look into Heidisql that I use, and there is also a nice editor for doing this.

I just don't get the right combination ready for my problem. I think I don't have the right fresh head today :twisted:

SELECT
productID,
productName,
categoryName,
companyName AS supplier
FROM
products
INNER JOIN
categories ON categories.categoryID = products.categoryID
INNER JOIN
suppliers ON suppliers.supplierID = products.supplierID

This is the way I gonna look into. It covers 3 databases, but i didn't work yet.
Will come back to this. ( I hope..)
Marc Venken
Using: FWH 23.04 with Harbour
User avatar
Marc Venken
 
Posts: 1338
Joined: Tue Jun 14, 2016 7:51 am
Location: Belgium


Return to FiveWin for Harbour/xHarbour

Who is online

Users browsing this forum: No registered users and 9 guests