Setting Index, Sort Order on Browse vs Report

[dik_coleman]

Can anyone explain why I get a different sort order in an embedded browse and a report using the same index.

I have a table with two dates and several numeric fields.
Setting the index to :invert(date # 1) on the embedded browse results in the sort order of: rec # 59 to rec # 1

Setting the same index on a report results in a 'crazy' sort order.
The order is: rec # 45 to rec # 59, then rec # 30 to rec # 44, then rec # 16 to rec # 29 and finally rec # 1 to rec # 15

In each group, the date used as the index is the same. ie rec #1 to rec # 15 have the same date, 16 thru 29 have the same date, but different than the previous group; etc, etc.

Makes trying to 'debug' how a script is accessing the records just a bit difficult. Using the date in recs 1-15 as the basis for a 'fetch_find' results in going to record # 15. Looking at the report, it appears that the 'find' has gone to the last record in the sort order; when it has actually accessed the 15th record from the end.

Probably not an earth shattering problem, but I'm kinda curious why this happens.

D

PS
If you change the index to invert(recno()); you get exactly the same result as above

D

[Tom Cone Jr]

Good morning, Dick. It sounds as though your report is presenting the groups in descending order, but the details within each group in ascending order.

-- tom

[dik_coleman]

OK thanx for tip
Will look at the chm on report groups...

thanx again
D

[dik_coleman]

This is a follow up report on the topic.

First, the report has no 'formal' groups. The sort order is set at invert(date).
Second, anything else that I try (as far as sort order, setting the index, etc) only ends up by destroying the date sort order.
The report setup (apparently) will not react to 'invert(recno())'. This results in a sort order of 1, 10, 11, 12......2, 20, 21......etc. or just 1 thru 59....
I have no idea why????

So it looks like I will have to live with the date grouped together and the records for that date listed in ascending order.

This will work for me (as long as nothing changes) and I can test for the correct date using a 'fetch_next()'.

At this point, I don't see any additional problems

D

[dik_coleman]

zip attached

I know everyone has alot to do but if anyone can explain how the attached table can be printed out in reverse record numbers using the first_date as the sort order; it would be appreciated.

table has two dates in each record.
table has several records with the identical dates.
sort order is based on the first date in descending order

in larger tables with more records, the same thing happens each time the first date is changed, inside each date group the record order is ascending.

If there is no way to accomplish 'my task' - I just need to know that.

thanx
D

[Ed Barley]

use decending order

If I am understanding you correctly. You need to tell the report what to do. Also the report in your zip file has no grouping in it.



Open up your report in design mode.

Pick: Report/Properties/Detail Properties

Records tab: Order expression........pick what field or fields to sort on
select ascending/descending order


Regards Ed

[Tom Cone Jr]

Dick, the difficulty for me is understanding your goal. I've read the thread several times and remain confused.

Do you want the records sorted by record number, descending?
Do you want the records sorted by a date field, descending (as your first post says)?
Do you want the records sorted first by the date field (descending) and then by record number (ascending)?
Do you want the records sorted first by the date field (descending) and then by record number (descending)?

-- tom

[Tom Cone Jr]

Example

Dick,

If you want the details sorted first by the First_date field, descending, and then in cases where the date field value is the same you want to sort by record number, descending, here's an example.

I get the same sort sequence in your form if I embed the following ordering expression there:

invert(cdate(First_date))+invert(str(recno(),19,5))

-- tom

[dik_coleman]

Tom,
OK, I'm impressed. I spend 3 days trying to get the result you got.

I had no problem with the embedded browse, the order was exactly as I wanted - dates grouped together, descending and then descending record number order within the groups. That probably put me in a 'tunnel vision' frame of mind, since nothing but setting the index was required.

My problem stems from the fact that I did not consider each date (with multiple entries) as a group. I was only 'thinking' at the record level.

I did not think that a group break was required to achieve the necessary results.

It's been a long day, so I'll dig into your solution in the am. At this point, I'm not sure that I understand why the solution works, but that will probably come with a little study.

My sincere thanks for your patience and your help.

D

[Tom Cone Jr]

Dick, a group break is not required. It's just the approach that seemed easiest to me. If you do not want to use the group break just use the sort order:

invert(cdate(First_date))+invert(str(recno(),19,5))

and do not check the "descending" option, leave it "ascending".

-- tom

[dik_coleman]

OK, I'm good at copying..... so it works!!!!

Now, I still don't know why it works, and I seemed to have missed the part that says the variables have to be 'character' when being used in the expression builder....

and to seem even more 'dense', what are the numbers 19 and 5 for????

I've been going thru manuals for two days and cannot find anything that even remotely looks like the answer you gave me.

Point me towards the part of the manuals that cover what you did, please

D

[Tom Cone Jr]

I think the section called "Overview: Functions & Expressions" is essential. Especially the subsections dealing with "what is a filter expression", and "writing filter expressions", and "what is an order expression".

Here, your "First_date" field is a "date" data type, while the record numbers assigned by Alpha Five to each record are "numeric" data types. Your requirements specified that you want Alpha Five to sort the records in order first by "first_date" (descending), then by "record number" (descending). This told me that we need to create a compound sort key for Alpha Five to use in ordering the records. i.e. We need to write an expression which contains both "First_date" (descending) and "record number" (descending) components. The ONLY way to do this is convert them both to a character strings so they can be concatenated together to create a single sort sequence "key" that can be compared with other similar keys for each record being sorted.

Consider the simpler case:

"Cone" + "Tom"
"Cone" + "Ron"
"Cone" + "Joe"

If I use a Last + First order expression I get sort keys like this:

"ConeTom"
"ConeRon"
"ConeJoe"

Which Alpha can quickly sort:

"ConeJoe"
"ConeRon"
"ConeTom"

Piece of cake.

In your case your requirement mandates use of a compound sort key, just like Last + First, but you can't use the "+" operator to concatenate a date type field value with a numeric record number. Mixing data types is NOT allowed in an expression.

So I convert them to character strings so I CAN concatenate the values to create the desired compound sort key.

cdate(first_date) + str(recno())

(The 19 and 5 were supplied by Alpha Five automatically when I built the expression in the report editor. Numeric data types are limited to 19 digits, 5 of which can be decimal. These are max values. There can't be any record numbers longer / wider than this).

Now, in your case if we stopped with

cdate(first_date) + str(recno())

your records would be sorted first by First_date (ascending) and then by record number. The keys would look like this:

"20060308_________1"
"20060308_________2"
"20060308_________3"
"20060322_________4"

Why the gap between the date and record number? Well, the str() function returns the numeric as a string that's 10 wide by default. It pads the number with leading blanks to create the 10 wide character string equivalent for each number. Alpha's use of the optional 19,5 parameters in the STR() function assures that record numbers that up to 14 digits could be handled. Since the table can't have more than about 2 billion records, and 2 billion requires 10 digits, the optional 19 and 5 parameters are probably unnecessary.

In any event these compound sort keys, now expressed as a character string, can be easily sorted by Alpha Five.

But, you wanted them in descending order, so I supplied the invert() function to wrap both components of the compound key. And, voila, you have a working sort order expression:

invert(cdate(first_date)) + invert(str(recno()))

In many cases you will want to trim off the leading blanks when dealing with the STR() function.

"_________1"

is not nearly as handy as "1" in many situations. However, it's worth noting here that this would be a BIG mistake. You do not want to trim the leading blanks because that will destroy the desired sort sequence.

Consider the case of record numbers 1, 2, 10, 11, 12, and 21.

An order expression of Invert(STR(recno())) produces this sequence:

"________21"
"________12"
"________11"
"________10"
"_________2"
"_________1"

This is what you wanted in the present situation. However, if you trimmed the leading blanks like this:

Invert(Ltrim(str(recno())))

You'd get:

"21"
"2"
"12"
"11"
"10"
"1"

This latter unfortunate arrangement is all too familiar to many of us, perhaps you've bumped into it before?

Hope this helps.

-- tom