How XBROWSE data base on perticular fields value - SOLVED!

[dagiayunus]

Dear Sir

I have database Item master having field ICODE,INAME etc.
Second database is STOCK.dbf having field ICODE,WH_ID,STOCK, & set relation on ICODE

and data records of Stock.dbf as below

ICODE WH_ID QTY
ITEM1 W1 10
ITEM1 W2 5
ITEM1 W3 5

How to browse ARTICLE.DBF and show Qty of WH_ID="W1" .and. WH_ID="W2" only.

XBROWSE FIELD ARTICLE->INAME, "STK1" as qty from STOCK->QTY for WH_ID="W1" and "STK2" stock->QTY for WH_ID="2"

Regards
Yunus

[cnavarro]

Look

viewtopic.php?f=3&t=32063#p187645

[nageswaragunupudi]

Mr Dagia

I am suggesting two methods.
1. Using RDD
2. Using FWH ADO Pivot Table function.

Please test this sample program. You can build this in any folder you like.

Code: Select all  Expand view

#include "fivewin.ch"

REQUEST DBFCDX

FIELD ICODE

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

function Main()

   CreateArticleTable()
   CreateStockTable()

   DoBrowse()
   BrowseAsPivotTable()

return nil

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

static function DoBrowse()

   local oDlg, oFont, oBrw

   USE ARTICLE NEW SHARED VIA "DBFCDX"
   SET ORDER TO TAG ICODE
   GO TOP

   USE STOCK NEW SHARED VIA "DBFCDX"
   SET RELATION TO ICODE INTO ARTICLE
   GO TOP

   DEFINE FONT oFont NAME "TAHOMA" SIZE 0,-14
   DEFINE DIALOG oDlg SIZE 700,200 PIXEL FONT oFont ;
      TITLE "USING RDD"

   @ 10,10 XBROWSE oBrw SIZE -10,-10 PIXEL OF oDlg ;
      DATASOURCE "STOCK" ;
      COLUMNS "ARTICLE->INAME", ;
         "IF( WH_ID == 'W1', QTY, 0 )", ;
         "IF( WH_ID == 'W2', QTY, 0 )", ;
         "IF( WH_ID == 'W3', QTY, 0 )"  ;
      HEADERS "Article", "STK1", "STK2", "STK3" ;
      CELL LINES NOBORDER

   WITH OBJECT oBrw
      :lDisplayZeros    := .f.
      //
      :CreateFromCode()
   END

   ACTIVATE DIALOG oDlg CENTERED
   RELEASE FONT oFont

return nil

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

static function CreateArticleTable()

   local aCols := { { "ICODE", 'C', 2, 0 }, { "INAME", 'C', 20, 0 } }
   local aData := { { "01", "Computers" }, { "02", "Laptops" }, { "03", "Printers" }, { "04", "UPS" } }

   DBCREATE( "ARTICLE.DBF", aCols, "DBFCDX", .t., "MAS" )
   INDEX ON ICODE TAG ICODE

   FW_ArrayToDBF( aData )
   CLOSE MAS

return nil

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

static function CreateStockTable()

   local aCols := { { "ICODE", 'C', 2, 0 }, { "WH_ID", 'C', 2, 0 }, { "QTY", 'N', 5, 0 } }
   local aData := { { "01", "W1", 10 }, { "02", "W2", 5 }, { "03", "W3", 5 } }

   DBCREATE( "STOCK.DBF", aCols, "DBFCDX", .t., "STK" )
   INDEX ON ICODE TAG ICODE
   FW_ArrayToDbf( aData )
   CLOSE STK

return nil

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

static function BrowseAsPivotTable()

   local cPath    := cFilePath( ExeName() )  // file path of DBF tables
   local oCn, cSql, aPivot, oRs

   oCn   := FW_OpenAdoConnection( cPath )
   if oCn == nil
      ? "Ado Connect fail"
      return nil
   endif

   TEXT INTO cSql
   SELECT A.INAME AS ARTICLE, WH_ID AS WAREHOUSE, QTY
   FROM STOCK S
   LEFT OUTER JOIN ARTICLE A ON S.ICODE = A.ICODE
   ENDTEXT

   aPivot   := FW_AdoPivotArray( oCn, cSql, "Article", "WareHouse", "QTY", "SUM" )

   XBROWSER aPivot SETUP oBrw:lDisplayZeros := .f. ;
      TITLE "FWH ADO POWER"

   oCn:Close()

return nil

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

Using Normal RDD:


FWH ADO PivotTable

[dagiayunus]

Dear Sir,

Thanks for the solution.

We always learn something new from your post Sir.

Regards
Dagia Yunus.

[nageswaragunupudi]

Mr Dagia

I see you are using FWH 15.01. This version does not have pivot table functions. We introduced Pivot table functionality in 15.03 and later enhanced in 15.07.

If you are considering migrating from RDD to ADO (SQL databases) then you better consider upgrading FWH at your convenience.