Indexing issue on two numeric fields 2

[Steve-vfp9user]

Hello all

I have a table with two numeric fields, JOBNUMB N(6) and JOBYEAR N(4). The jobnumb field stores a rolling number for example 650, 651, 652 and the jobyear field stores the current year e.g. 2017. On the 1st of January, the jobnumb resets back to 1 and the year is added by one e.g. 2018.
In my grid there are two columns for the above and the index tag (already created) is as follows:

SET ORDER TO RECORDNO DESC  &&  This has been created as INDEX ON RECNO() TAG RECORDNO DESC

When the index is used it shows a list of job number/job year as:
655 2017
654 2017
653 2017
etc etc
854 2016
853 2016
852 2016

So the above is how we want it to show however, we have recently experienced some records out of sync in the list due to users creating the records at different times whereby the job number and job year is allocated.

I have tried the following:

INDEX ON JOBYEAR+JOBNUMB TAG JOBNUMBER
SET ORDER TO JOBNUMBER

This shows:
653 2017
654 2017
655 2017
etc etc
852 2016
853 2016
854 2016

SET ORDER TO JOBNUMBER DESC

This shows:
854 2016
853 2016
852 2016
etc etc
655 2017
654 2017
653 2017

INDEX ON JOBNUMB+JOBYEAR TAG JOBNUMBER
SET ORDER TO JOBNUMBER

This shows:
653 2017
654 2017
655 2017
etc etc
852 2016
853 2016
854 2016

SET ORDER TO JOBNUMBER DESC

This shows:
854 2016
853 2016
852 2016
etc etc
655 2017
654 2017
653 2017

So my question is, how do I create an index tag which will show my records with the highest job number and latest year first in descending order like this?

655 2017
654 2017
653 2017
etc etc
854 2016
853 2016
852 2016

Thank you

Steve Williams

 

[Mike Lewis]

Hi Steve,

As far as I can see, your first attempt was almost correct. Since you want to order by job number within year, you need to put the year first in the index expression: [tt]JOBYEAR + JOBNUMB[/tt] (which is what your originally did).

However, the above expression will add the two numbers together, creating an index on the sum. I would prefer to create the index on a string expression:

[tt]STR(JobYear, 4) + STR(JobNum, 4) DESC[/tt]

Note that the string is only being created for index purposes. It won't affect the actual values.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve-vfp9user]

Shame I can't give you two stars for your post Mike!

Yes, that's the cure and my sincere thanks to you.

Thank you

Steve Williams

 

[Mike Lewis]

Glad it worked, Steve.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Stephan_Neumann]

Little addition: since your jobnum is N(6) I would use str(jobnum,6) to prevent asterix problems in the future!

 

[Olaf Doschke]

To combine numbers into strings in an index the ideal way is to use bintoc(), especially for ints it is designed to keep numbers in order, so negative ints are sorted before positive in machine collation.
To get DESC order you can use BINTOC(x,"4rs").

As the range of N(6) is smaller than an integer and more important having no decimals, this should also work for this case.

Which means

INDEX ON BINTOC(JobYear) + BINTOC(JobNum) DESCENDING tag yrjbdesc

or

INDEX ON BINTOC(JobYear,"4RS") + BINTOC(JobNum,"4RS") tag yrjbasc

Defined normal (ASCENDING) and still working DESCENDING overall. You could also mix it

INDEX ON BINTOC(JobYear,"4RS") + BINTOC(JobNum) tag yrdjba

listing years descending but job numbers within each year ascending.

And another note: It doesn't matter whether you use ASCENDING or DESCENDING at index definition you can always use an index both ways simply SET ORDER TO indextagname ASCENDING or DESCENDING. Believe it or not, I only learned that after having used two index definitions. the mixed mode is only possible by using such trickery in bit inverting, with limited sets of character like alphabet you can also simply reverse letters a-z to z-a, because as Mike says the index expression is not seen, it only is used for lookup and sorting. Using indexes for sorting it won't matter your index nodes are not the original data values, you can also still use such indexes for seeking and query rushmore optimizations just using the exact same expressions as used when creating the index. Put that together with the ability to seek partial values just like matching partial strings, and you can make more efficient smaller indexes usable for versatile sorting and filtering.

Bye, Olaf.

 

[Steve-vfp9user]

Olaf and Stephan, appreciate the posts....

Thank you

Steve Williams