Alpha Video Training
Results 1 to 12 of 12

Thread: Setting Index, Sort Order on Browse vs Report

  1. #1
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default Setting Index, Sort Order on Browse vs Report

    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
    Last edited by dik_coleman; 03-28-2006 at 08:00 AM.

  2. #2
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    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

  3. #3
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default

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

    thanx again
    D

  4. #4
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default

    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

  5. #5
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default 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

  6. #6
    "Certified" Alphaholic
    Real Name
    Ed Barley
    Join Date
    Mar 2002
    Location
    Southern California
    Posts
    1,056

    Default 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

  7. #7
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    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

  8. #8
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default 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:
    Code:
    invert(cdate(First_date))+invert(str(recno(),19,5))
    -- tom
    Last edited by Tom Cone Jr; 03-30-2006 at 12:17 PM.

  9. #9
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default

    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

  10. #10
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    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

  11. #11
    "Certified" Alphaholic
    Real Name
    Richard Coleman
    Join Date
    Oct 2000
    Location
    Franklin, TN
    Posts
    1,206

    Default

    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

  12. #12
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,310

    Default

    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
    Last edited by Tom Cone Jr; 04-01-2006 at 06:29 PM.

Similar Threads

  1. browse sort order
    By geoff m in forum Alpha Five Version 5
    Replies: 3
    Last Post: 09-23-2004, 08:59 AM
  2. Browse Sort Order
    By Jeffrey Wolfe in forum Alpha Five Version 5
    Replies: 4
    Last Post: 08-21-2004, 09:32 PM
  3. Browse Sort by Record Order
    By Jeff, Richards in forum Alpha Five Version 5
    Replies: 4
    Last Post: 05-29-2004, 08:14 AM
  4. Preset sort order in browse
    By René Stout in forum Alpha Five Version 5
    Replies: 5
    Last Post: 11-01-2003, 12:33 AM
  5. Report sort order
    By Edward F. Schulz in forum Alpha Five Version 4
    Replies: 6
    Last Post: 11-09-2002, 01:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •