INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Indexing issue on two numeric fields

Indexing issue on two numeric fields

(OP)
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:

CODE -->

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:

CODE -->

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

CODE -->

SET ORDER TO JOBNUMBER DESC 

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

CODE -->

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

CODE -->

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

RE: Indexing issue on two numeric fields

2
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: JOBYEAR + JOBNUMB (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:

STR(JobYear, 4) + STR(JobNum, 4) DESC

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

RE: Indexing issue on two numeric fields

(OP)
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

RE: Indexing issue on two numeric fields

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

RE: Indexing issue on two numeric fields

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

CODE

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

CODE

INDEX ON BINTOC(JobYear,"4RS") + BINTOC(JobNum,"4RS") tag yrjbasc 
Defined normal (ASCENDING) and still working DESCENDING overall. You could also mix it

CODE

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.

RE: Indexing issue on two numeric fields

(OP)
Olaf and Stephan, appreciate the posts....

Thank you

Steve Williams

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close