How XBROWSE data base on perticular fields value - SOLVED!

Post Reply
dagiayunus
Posts: 69
Joined: Wed Nov 19, 2014 1:04 pm
Contact:

How XBROWSE data base on perticular fields value - SOLVED!

Post by 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
Last edited by dagiayunus on Thu Mar 17, 2016 12:45 pm, edited 1 time in total.
Dagia Yunus.
Rajkot, India

FWH 17.04
User avatar
cnavarro
Posts: 5792
Joined: Wed Feb 15, 2012 8:25 pm
Location: España

Re: How XBROWSE data base on perticular fields value

Post by cnavarro »

C. Navarro
Hay dos tipos de personas: las que te hacen perder el tiempo y las que te hacen perder la noción del tiempo
Si alguien te dice que algo no se puede hacer, recuerda que esta hablando de sus limitaciones, no de las tuyas.
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: How XBROWSE data base on perticular fields value

Post by 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

#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:
Image

FWH ADO PivotTable
Image
Regards

G. N. Rao.
Hyderabad, India
dagiayunus
Posts: 69
Joined: Wed Nov 19, 2014 1:04 pm
Contact:

Re: How XBROWSE data base on perticular fields value (SOLVED)

Post by dagiayunus »

Dear Sir,

Thanks for the solution.

We always learn something new from your post Sir.

Regards
Dagia Yunus.
Dagia Yunus.
Rajkot, India

FWH 17.04
User avatar
nageswaragunupudi
Posts: 8017
Joined: Sun Nov 19, 2006 5:22 am
Location: India
Contact:

Re: How XBROWSE data base on perticular fields value

Post by 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.
Regards

G. N. Rao.
Hyderabad, India
Post Reply