A small index on problem? 1

[keepingbusy]

Hi all

I have two numeric fields in a table CSMORDER

CONSECNUM (n,6) and CONSECYER (n,4) (Example: 123456 and 2005)

I show a list of records from a table in a grid by using the following command:

muporder=SYS(3)  && temp file
SELECT * FROM CSMORDER WHERE COCOMPLT<>"Y" ;
  AND COCONFRM<>"Y" ; AND COACCTYN<>"Y" ;
  ORDER BY CONSECNUM DESCENDING INTO ;
  TABLE muporder+'.dbf'

If I index the table using:

INDEX ON CONSECYER+CONSECNUM DESCENDING TAG CONSECNUM

I get
10674 2005
10673 2005
10672 2005

then...

55 2006
54 2006
53 2006 etc

If I change the index on to:

INDEX ON CONSECYER+CONSECNUM TAG CONSECNUM

I get
1 2006
2 2006

then...
1 2005
2 2005 etc

If I change the index to:

INDEX ON STR(CONSECYER,4)+STR(CONSECNUM,6) ;
  DESCENDING TAG CONSECNUM

I get
25 2006
24 2006
23 2006

then...

10400 2005
10399 2005
10398 2005

(The last example is what I'm trying to achieve)

When I issue

USE CSMORDER ORDER CONSECNUM
BROWS FIELDS CONSECNUM, CONSECYER

in a command window it works fine. However when the records from the table are shown in a grid on a form the CONSECNUM shows
1 2006
2 2006
3 2006

and

1 2005
2 2005
3 2005

What am I missing here guys? I'm sure its staring me in the face!!

Version 6 for this app

Many thanks

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[GriffMG]

I would check the form for a data environment, perhaps the order is being overridden by some 'left overs' that you weren't expecting.

I'd look at any initially selected settings (table, order) and for anything in the init method.




Regards

Griff
Keep [Smile]ing

 

[keepingbusy]

Hi Griff

Checked that and there doesn't appear to be any 'left overs'.

Strange one this!

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[GriffMG]

Try dropping the 'order' part of the use... and do the index there (just for now) to ensure it's valid etc.

select 0
USE CSMORDER 
INDEX ON STR(CONSECYER,4)+STR(CONSECNUM,6) DESCENDING TAG CONSECNUM
set order to CONSECNUM
BROWS FIELDS CONSECNUM, CONSECYER

Regards

Griff
Keep [Smile]ing

 

[keepingbusy]

Hi Griff

That does work as you have shown, but when I run my code it reverts back to my original post e.g.

in a command window it works fine. However when the records from the table are shown in a grid on a form the CONSECNUM shows
1 2006
2 2006
3 2006

and

1 2005
2 2005
3 2005

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[GriffMG]

Lee,

Sorry, I only just spotted it was you!

I'm a bit busy tomorrow am, but if you want to make what you have available (upload/email) as much as you can... I'll have a good look see and post whatever I can by way of an answer.

Martin

Regards

Griff
Keep [Smile]ing

 

[keepingbusy]

Many thanks Griff (I'll post it tomorrow evening)

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[craigber]

The index is adding two numbers, not concatenating two fields, so it's doing 1+3 and getting 4 instead of concatinating two strings and getting 13. You haven't said what the reasoning is for the index. You can change the ORDER statement of the SELECT clause to
ORDER BY CONSECYER, CONSECNUM DESCENDING

Craig Berntson
MCSD, Visual FoxPro MVP,

http://www.craigberntson.com

Author, CrysDev: A Developer's Guide to Integrating Crystal Reports"

 

[keepingbusy]

Hi craigber

The reason for the index is to show the records in reverse order after they have been created. As mentioned the CONSECNUM is a rolling number and the CONSECYER is the current year.

I'm trying to show the latest (newest) order at the top of the list and the oldest at the bottom.

Therefore I should be ending up with a list something like:
25 2006
24 2006
23 2006

then...

10400 2005
10399 2005
10398 2005

Since posting back after Griff's post, I have been able to resolve the problem.

The table has another numeric field called SERIALNO (n,6) which adds the RECNO() to SERIALNO each time a record is appended to the table. An index has been created:

INDEX ON SERIALNO	TAG SERIALNO

When the command:

SELECT * FROM CSMORDER WHERE COCOMPLT<>"Y" ;
  AND COCONFRM<>"Y" AND COACCTYN<>"Y";
  ORDER BY SERIALNO DESCENDING INTO ;
  TABLE muporder+'.dbf'

this produces the required effect (at last!)

Thank you to those who posted on this thread (GriffMG / craigber), inspiration is a wonderful thing!

Cheers guys

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[keepingbusy]

Hi myearwood

Thank you for your post. The matter has been resolved but I will try out your suggestions for which I'm grateful.

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk

 

[PeterVD]

Hi guys,

I have seen that Lee got his problem solved. Because I read these forum's to learn from other peoples problems and the solutions you get presented here, I think it wouldn't be bad that to tell there is some danger in using his appoach with the RECNO(), used as kind of index number. Maybe in his application nobody is able to delete a record, but if it was and a PACK is issued to clean up, you are in trouble. I think is wise to use another approach for your indexnumbering., like a SELECT MAX(indexnumber) ....

Greetings,
Peter

 

[Olaf Doschke]

Hi Lee,

The most simple solution to the problem of showing a table in reverse physical order is to have an autoinc field or something alike as a primary key, then SET ORDER TO TAG primaryID DESCENDING.

Building an index with two fields should be done with BINTOC() and collation sequence machine: INDEX ON BINTOC(year)+BINTOC(no) TAG yearno. That is faster and shorter than STR(), even if you use STR(year,4).

Bye, Olaf.

 

[keepingbusy]

PeterVD
That's good information and I accept your comments. You are quite right in saying about the deletion and packing of records but in this case, the app is an invoice management system that does not allow the deletion of records. The reason for this is for VAT and TAX purposes (this relates to the UK if you are not aware of this). A complete list would be required by an Inspector on demand and there must not be any gaps between invoices even if they have been accepted or cancelled.

Even if records were deleted, there would still be an order in the table. For example, if the SERIALNO field was populated with the RECNO() in a sequence 567, 568, 569, 570 etc and 568 was deleted you would still have the desired effect in reverse (which was my task) such as 570, 569, 567 etc

Thank you for your valuable post which I'm sure will be useful to anyone who may be in a slightly different scenario to mine.

Olaf
My thanks to you also. I have to admit I had never hear of the BINTOC() command. I will of course, try this out even though my problem has been resolved (it's a case of the old "If it 'aint broke don't fix it) and if the need arises I am now aware of it.

Once again, thanks to all. Whatever would we do without this forum?

Kindest regards

Lee

Visual FoxPro Versions: 6 & 9
Operating System: Windows XP

http://www.customsoftware.co.uk