Controlling Sort order on Layout using Trigger Script
I have researched this problem and found what I believe are the right components to make it work but as yet have not gotten the correct results.
I found that the trigger I should use in order to make this work while navigating through the records is the "OnRecordLoad" trigger within a layout.
I have two buttons defined (PREV and NEXT) that I want to use to move to the next or previous record in my table but I want to have the records sorted by two fields, the first in a related table and the second sort based on a field in my layout's main table.
The first sort field in the related table should be the related table's ID field and the second should be a numerical field in the main table for the layout field.
The main table will have 18 records for each record in the related table and will be identified by the numbers 1 to 18. The ID field in the related table is also numeric.
What I need is to navigate through each of the 18 records (in order 1 to 18) related to ID 1 of the related table before displaying the 18 records related to ID 2 of the related table.
I have created a script that is supposed to fire when a record is loaded that will keep the records together so the 18 records related to record A in the other table all show up in sequence before record 1 of then next group related to record B shows up.
When I try to navigate through these records using my PREV or NEXT button the sort does not seem to be working at all so I am wondering if there is a setting I haven't checked to make this happen.
My sort script is two lines using Ascending sort of the fields as follows;
"Sort Records by Field [Ascending; Course::Course Id]"
"Sort Records by Field [Ascending; CouseHole::Hole Number]"
I have also tried changing the first sort line to use the main table as follows;
"Sort Records by Field [Ascending; CourseHold::Course Id]' but it didn't seem to matter.
The way the navigation is acting right now is that the sort only seems to be using the second of these sort lines and sll the Hole Number ones are coming in order regardless of the relationship to the Course Id field in the first sort line.
Is this because these fields are in two different tables or is there another way to write this script to make it work?
Thanks for anyone who can guide me in the right direction.
The PREV button is coded to "Go to Record/Request/Page Previous"
The NEXT button is coded to "Go to Record/Request/Page Next"