How to use process a dbf table in multiple orders

[sudip]

Hello All,

I am using DBFCDX. How can I process a table with multiple orders like SQL ORDER BY ... statement with a DBF table, where all sorting keys will be INDEXES of the table (CDX).

Thanks.

[sudip]

One thing more:

This multiple orders will be ONE WITH IN ANOTHER like SQL ORDER BY .... statement

[jucar_es]

yo uso

ordsetfocus("indice")

[sudip]

Can you please post a smal PSEUDO CODE for this.

Thanks in advnce

[esgici]

Hello All,

I am using DBFCDX. How can I process a table with multiple orders like SQL ORDER BY ... statement with a DBF table, where all sorting keys will be INDEXES of the table (CDX).

Thanks.

Hi Sudip, how are you ?

By the way you are walking at inshore of a river or a sea ?

I don't know what is SQL ORDER BY ... statement; but did you inspected <hmg_root>\SAMPLES\EDIT.EXTENDED\demo.prg ?

IMHO all about CDX usages is in this .prg.

Regards

--

Esgici

[sudip]

Hi Brother Esgici,

How are you, long days back I am reading your message.
Yes, it's sea, Bay of Bengal Hope, I am not looking angry in this picture
My friend took the photograph when I was checking my mobile, yes it's a bad habbit, but I am expecting to run one QT based HMG app on this (yes, it's Nokia)

Ok, regarding my query, I am working on a project with DBFCDX where user can check anytype of sort order (yes, it will be multiple and one with in another) for viewing or printing.

Eg., Table table has 4 fields: BillNo, BillDt, CustCode, NetAmt

User may want to view in the order:

Code: Select all

CustCode
   BillDt
      BillNo
Here first it is sorted with CustCode. Within CustCode - BillDt and within BillDt, BillNo

This type of sorting can be done by storing the records in an array and then using ASROT() within a FOR ... NEXT loop. But, I want to use Index keys (CDX). I am not sure, how to do this.

Hope I am able to express what I am thinking

Thank you very much

[esgici]

Ok, regarding my query, I am working on a project with DBFCDX where user can check anytype of sort order (yes, it will be multiple and one with in another) for viewing or printing.

Well, you haven't time for reading whole example; so this is a little pseudo code for your convenience.

Code: Select all

REQUEST DBFCDX

* ( say name of you .dbf is [b]CustBill[/b] )

use CustBill

* >>> rutine for FIRST indexing :

* assuming "CustBill.CDX" dosen't exist

OrdCreate( "CustBill.CDX",                      ;  // name of .cdx is same of table (a good habit)
           "Customer",                                ;  // index "tag" 
           "CustCode",;                               ;  // key field (character) 
           {|| CustCode } ) )                          // key code block (for speed)

  
OrdCreate( "CustBill.CDX",                       ;  // using an existing .cdx name means "add this index too same index file" 
           "BillDate",                                    ;  // index "tag" 
           "BillDt",;                                      ;  // key field (character) 
           {|| BillDt } ) )                                  // key code block (for speed)


OrdCreate( "CustBill.CDX",                       ;  // third index (tag) in the same same index file 
           "BillNumb",                                  ;  // index "tag" 
           "BillNo",;                                     ;  // key field (character) 
           {|| BillNot } ) )                               // key code block (for speed)


*   using with index (.cdx):

use CustBill index CustBill 

OR : if AUTOPEN is ON:

use CustBill 


* >>> Selecting/Changing an index order (tag)

* ( as jucar_es mentioned: )

OrdSetFocus("Customer")                // first tag: Customer

...

OrdSetFocus("BillDate")                // 2.nd tag: BillDate

...

OrdSetFocus("BillNumb")                // 3.th tag: BillNumb

* ...

** that is all !

* ................

Caution :

Add

ERASE CustBill.CDX

line at top of FIRST indexing rutine for REINDEXING; otherwise every time your .cdx file will grown.

In addition, you may need building a little "combined" index keys for "key within key" situaitons, such as:

CustCode + DTOS( BillDt) + BillNo // if Billno is character

OR

CustCode + DTOS( BillDt) + STR( BillNo ) // if Billno is numeric

OR

CustCode + DTOS( BillDt) + STR( BillNo, 5 ) // depending field width of Billno

OR

CustCode + DTOS( BillDt) + STRZERO( BillNo, 5 ) // depending field width of Billno

Hope will give idea

Regards

--

Esgici

[sudip]

Hello Esgici,

Thank you.

I am very sorry. I again, couldn't explain what I wanted to express.
In SQL, it will be like:

Code: Select all

SELECT CustCode, BillDt, BillNo, NetAmt FROM Bill ORDER BY CustCode, BillDt, BillNo

List will be like:

Code: Select all

CustCode   BillDt     BillNo     NetAmt
--------   --------   ------  ---------
xxxxxxx    01/04/11   1234    100000.00
xxxxxxx    01/04/11   1235    345355.00
xxxxxxx    02/04/11   1111     43324.00
xxxxxxx    02/04/11   1212    434324.00
yyyyyyy    01/04/11   1000    100000.00
yyyyyyy    02/04/11   1245    999999.00
(dates in dd/mm/yy format)

I am very sorry, that I cannot write code from the pseudo code you gave to me.
It's due to my lack of knowledge.

Here indexes are on CUSTCODE, BILLDT, BILLNO. And I want to do it without creating new indexes
It will be helpful to me, if you please explain how to get above listing. Moreover it will be generic.

I can do this by storing all the records to an array. Then using ASORT() first I shall sort on BillNo then BillDt and finally on CustCode (reverse order of sorting hiererchy). But, I want to do this using index keys. And adding more "spices", user can select ASCENDING or DESCENDING order (All things can be done with array method, but I can't find how to use it PURE XBASE INDEXES)
Thank you very much

[esgici]

Code: Select all

REQUEST DBFCDX
IF FILE( "Bill.CDX" )
    ERASE Bill.CDX
ENDIF
USE BILL VIA DBFCDX
OrdCreate( "Bill.CDX", "Customer", "CustCode + DTOS( BillDt  ) + STR( BillNo, 5 )",{|| CustCode + DTOS( BillDt  ) + STR( BillNo, 5 ) } ) 
GO TOP
cCustList := ''CustCode   BillDt     BillNo     NetAmt" + CRLF + "--------      --------   ------     ---------"
DO WHILE .NOT. EOF()
    cCustList +=  CustCode + " " + DTOC(  BillDt ) + STR(  BillNo,8 )  + TRANSFORM(  NetAmt, "999,999,999.99") + CRLF
    SKIP
ENDDO

The cCustList variable will contain your list.

If you want working without INDEXs you haven't need DBFCDX.

In this case you have two opportunities:

- sorting your file into a intermediate (and probably temporary) file and getting list from that file.

OR

-reading entire file into an array, sorting array and listing.

But the best way is indexing.

Regards

--

Esgici

[mrduck]

You may achieve what you want to do using separate harbour indexes without reading all the records in a array and sorting in memory, with 3 nested loops...

Code: Select all

select a
use bill as custcode index custcode
select b
use bill as billdt index billdt
select c
use bill as billno index billno

SELECT A
do while ! eof()
  SELECT B
  set filter to CUSTCODE = A->CUSTCODE
  go top
  do while ! eof()
     select C
     SET FILTER to CUSTCODE = A->CUSTCODE .and. BILLDT = B->BILLDT
     do while ! eof()
        ? CustCode, BillDt, BillNo, NetAmt
        skip
     enddo
     select B
     skip
  enddo
  select custcode
  skip
enddo