sorting and navigation

[staypuffinpc]

This should be another easy one.

I have four navigation buttons, FIRST, PREV, NEXT, and LAST. When i just have them do these simple steps, they work fine. However, I'd like to sort the records every time someone hits the button (so that new people added are sorted instantly). When I put the sort command on First and last work, but next always takes me to record 2 and prev always takes me to record 2, even if I add new people. what did I do (or not do?)?

[CobaltSky]

The approach you're considering may make navigation a little slow if/when the number of records gets very large.

Be aware also, that it may produce some curious results which are nonetheless quite consistent with the logic you've set out. For example, if you go back one record and then forward one record, you may not end up on the record you started out on (due to changes in the file since you first came to that record).

However, all that aside, to achieve the outcome you want, you will need:

1. A global number field called 'gFromRecord',

2. A calculating number field called 'RecordID' with the formula set to Status(CurrentRecordID),

3. An unstored calculating number field called 'RecordNumber' with the formula set to Status(CurrentRecordBNumber),

4. A self-join relationship called RecordFinder which matches 'gFromRecord' to 'RecordID',

5. An unstored calculating number field called 'BackRecordNumber' with the formula set to RecordFinder::RecordNumber - 1

5. An unstored calculating number field called 'FwdRecordNumber' with the formula set to RecordFinder::RecordNumber + 1

6. A script to go forward which runs along the lines of:

Set Field ["gFromRecord", "RecordID"]

Sort [Restore, No Dialog]

Go to Record/Request/Page ["FwdRecordNumber"]

7. A script to go backward which runs along the lines of:

Set Field ["gFromRecord", "RecordID"]

Sort [Restore, No Dialog]

Go to Record/Request/Page ["BackRecordNumber"]

When setting up the Go to Record/Request/Page [ ] script commands, you will be using the option "by field value" to determine the record to jump to in the new sort order, and that record will be the one which precedes or follows (in the new sort order) the record you were just on.

[staypuffinpc]

Your directions are very precise (thanks, that really helps), but alas, it still yields the same results. Interestingly, I kept all those fields you had me create showing and no matter what record I am on, BackRecordNumber and FwdRecordNumber are always 0,2 respectively, which means "forward" always takes me to record 2 and "back" always takes me to record one no matter what. Any clues?

-Peter

[staypuffinpc]

Cobalt Sky,

I actually started thinking about it and discovered what was and was not working. Here're the modifications:

I changed Set Field ["gFromRecord", "RecordID"] to

Set Field ["gFromRecord", "status(CurrentRecordNumber)"]

--This saves the relative number I am on in order, instead of the constant RecordID number--

I also changed 'BackRecordNumber' to 'RecordFinder::gFromRecord-1' and "FwdRecordNumber' to 'RecordFinder::gFromRecord+1'

thanks for the help. About how many records do you think it would take to really slow this process down? I'd kinda' like to do it on a larger database, but don't think it'd be a good idea.

Peter

[CobaltSky]

Hello Peter,

Many apologies - I wrote the previous post out entirely from memory - probably not a good idea that late at night...

The changes you've made, however, will not work correctly when records have been added (new records will cause records to be repeated or skipped in the navigation order).

Since writing out the preceding post, I've located a file that uses a related technique and which works correctly for all cases, and I've adapted it to match the methodology I was attempting to describe in my previous posting. It includes a step that I omitted to refer to in my previous description - oops!!

A password-free copy of the working model is attached, so you can download it and have a look if you wish.

Meanwhile, the speed of navigation will not be a concern while the file size is moderate - say under a thousand records, however you may find that if the file size increases to many thousands of records the navigation becomes unacceptably slow. Making sure your sort fields are all indexed will help (but not eliminate) the problem.

SortNavigation.zip