3 Replies Latest reply on Dec 12, 2012 3:52 PM by philmodjunk

    Controlling Sort order on Layout using Trigger Script



      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"

        • 1. Re: Controlling Sort order on Layout using Trigger Script

               You seem to be confusing record navigation--moving from record to record on your layout with record sorting--arranging the records in a specified order.

               It seems like you have this relationship:


               Course::CourseID = CourseHole::CourseID

               See: Common Forum Relationship and Field Notations Explained if that notation is unfamiliar.

               And it would appear that your layout refers to CourseHole in Layout Setup | Show Records From.

               Is that correct so far?

               If so, what is your found set? I would guess that maybe you are trying to limit your found set to 18 course hole records all linked to the same course record. Would that be the case?

               Then, when you click the next or prev buttons do you want to pull up the next or previous set of 18 course hole records?

               That can be done, but keep in mind that if you based your layout on Course, an 18 row portal could be used to list the CourseHole records and you would not need any scripting at all.

          • 2. Re: Controlling Sort order on Layout using Trigger Script

                 You are correct in that I am trying to tie the 18 hole records to a particular course. The original purpose of this layout was to enter the information for each hole tied to a particular course record but also to be able to move through the records one at a time. So if I have the course record established the next step would be to change to this "Course Hole" layout  to enter information particular to each hole. I was hoping to use this same entry layout to also move through the 18 holes related to the same course record as a matter of review and edit. I know that a portal could show all 18 "Course Hole" records at once and will probably create a layout for that as well but was hoping the entry layout would allow the movement through each "Course Hole" record one at a time.

                 Thanks for your help.

            • 3. Re: Controlling Sort order on Layout using Trigger Script

                   You can do what you want with a list view layout. I just didn't want to spend the time describing that approach if the much simpler portal based layout can do the job.

                   For a list view based layout, I suggest working with two layouts--one based on Course and one, your list view, based on CourseHole.

                   Keep the records on your Course layout showing all records and sorted in the desired order that you want.

                   On your List view layout, set up a "Prev" button to go to the previous course to perform this script:

                   Freeze Window
                   Set Variable [$$TriggersOff ; True ]  --> this step is optional, see note at end of this post
                   Go to Layout [Course]
                   Go To Record/Request/Page [Previous]
                   Go To Related Record [Show only related records; From table: CourseHole; Using layout: "CourseHole" (CourseHole)]
                   Sort Records [Restore ; No Dialog] ---> Sort records by Hole Number in ascending order
                   Go To record/Request/Page [First]
                   Set Variable [$$TriggersOff ; False ]  --> this step is optional, see note at end of this post

                   Your script for the Next button would be exactly the same as the above except tha the go to record step should go to the next record instead of the previous record.

                   Note: Scripts like these that change layouts and change focus to different current records can trip a number of script triggers such as, but not limited to: OnLayoutExit, OnLayoutEnter, OnRecordLoad, OnObjectEnter. These trigger controlled scripts can slow things down and also can interfere with getting the desired results. If you have such triggers specified for either the Course or CourseHole layout, put them insde an If step like this:

                   If [ Not $$TriggersOff ]
                      #Put your script here
                   End IF

                   That way, you can use the $$TriggersOff variable to "disable" your trigger controlled scripts so that they do not interfere.

                   Enhancements to this that you can use if you want them:

                   If you set up your Course layout as a list view layout, you can put a button in the layout body that a use can click to switch to the CourseHole layout to see the related CourseHole records. That button would perform a very similar script to what I have already posted here. Also, if you perform a find on the Course Layout, you can set this up to work only with a sub set of all the courses in your database so this may have some future application in your database as well.